LINQ Code - InvoiceDtl

aarong · March 1, 2021, 11:52am

Hello,

Anyone able to help me with my LINQ code? I’m very new to this.

var FreightTotal = (from invcDtl in Db.InvcDtl
                   
                   where InvcDtl.InvoiceNum == InvoceNum
                   select invcDtl.InvoiceNum, invcDtl.DocTotalMiscChrg, InvoiceLine
                   ).ToList().Sum();

using (var txScope = IceContext.CreateDefaultTransactionScope())
{

foreach (var invcDtl in (from invcDtlRow in Db.invcDtl
where invcDtlRow.Company == Session.CompanyID 
&& invcDtlRow.InvoiceNum == InvoiceNum
select invcDtlRow))
{
FreightTotal = Convert.ToDecimal(FreightTotal);
invcDtl.TotalFreight_c =  Convert.ToDecimal(FreightTotal); 
}


 Db.Validate();
 txScope.Complete();
}

I want to convert this SQL Query into LINQ so I can populate a field on InvoiceDtl with Total Freight - Fieldname: TotalFreight_c from a BPM on ARInvoice.Update/Post-Processing

SQL:

SELECT InvoiceNum, SUM(InvoiceLine), SUM(DocTotalMiscChrg) AS [Total Freight] FROM Erp.InvcDtl 
WHERE InvoiceNum = 134786
GROUP BY InvoiceNum

but I’m getting some errors

Any help would be greatly appreciated.

Aaron

dhewi · March 1, 2021, 12:00pm

Most of those errors are because of case sensitivity. You might want to check those first and then it’ll look a lot more manageable.

I know I prefer to find my own answers where I can for learning purposes, so I won’t jump in further straight away!

aarong · March 1, 2021, 12:12pm

Thank you Daryl,

That was a lot of my problem.

var FreightTotal = (from invcDtl in Db.InvcDtl
                   where invcDtl.InvoiceNum == InvoiceNum
                   select invcDtl.InvoiceNum
                   ).ToList().Sum();
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
  foreach (var invcDtl in (from InvcDtlRow in Db.InvcDtl
    where InvcDtlRow.Company == Session.CompanyID && InvcDtlRow.InvoiceNum == InvoiceNum
      select InvcDtlRow))
      {
        FreightTotal = Convert.ToInt32(FreightTotal);
        InvcDtl.TotalFreight_c =  Convert.ToDecimal(FreightTotal); 
      }
 Db.Validate();
 txScope.Complete();
}

How would I include a SUM like in my SQL query and a group by? This where I am lost and do require help haha

dhewi · March 1, 2021, 1:24pm

The easier and more readable way to do that is to query for each row inside your foreach. As long as you query per invoice and return only the field you want, you can sum the entire query.

If this is a big loop then you could sum outside it, within your select clause, and return all the values at once, something like

group invcDtl by InvoiceNum into allInvcDtl
select new {InvoiceNum = allInvcDtl.Key, miscTotal = allInvcDtl.Sum(c => c.DocTotalMiscCharge}

I usually end up playing around with LINQ in LINQpad before committing anything to a BPM. I find it’s very difficult to be fluent in both SQL and LINQ.

hkeric.wci · March 1, 2021, 1:53pm

Just a related example to InvcDtl, showing also some joins.

// There could be multiple Invoice Lines
var invDetailRows = (from ih in Db.InvcHead
                     join id in Db.InvcDtl on new { ih.Company, ih.InvoiceNum } equals new { id.Company, id.InvoiceNum }
                     join sd in Db.ShipDtl on new { id.Company, id.PackNum, id.PackLine } equals new { sd.Company, sd.PackNum, sd.PackLine }
                     join jh in Db.JobHead on new { sd.Company, sd.JobNum } equals new { jh.Company, jh.JobNum }
                     where ih.Company == "EPIC03"
                            // && ih.GroupID == "HASOTEST"
                             && ih.InvoiceType == "SHP"
                             && ih.InvoiceNum == Erp.ErpEFFunctions.ConvertToInt(invoiceNum)
                             && id.OurShipQty > 0
                             && id.ConsolidateLines == false
                        select new {
                            id.Company,
                            id.InvoiceNum,
                            id.InvoiceLine,
                            id.PackNum,
                            id.PackLine,
                            jh.JobNum,
                            jh.PartNum,
                            jh.JobClosed,
							jh.ProdQty,
							id.MtlUnitCost,
							id.LbrUnitCost,
							id.BurUnitCost,
							id.SubUnitCost,
							id.MtlBurUnitCost,
							id.OrderNum, id.OrderLine, id.OrderRelNum,
                            JobProdQty = (jh.ProdQty <= 0) ? 1 : jh.ProdQty,
                            PartTranShippedQty = Db.PartTran.Where(w => w.Company == id.Company && w.JobNum == jh.JobNum && w.PartNum == jh.PartNum && w.TranType == "MFG-CUS")
                                            .Select(s => s.TranQty).DefaultIfEmpty(0).Sum()
                        }
                    ).ToList();

aarong · March 1, 2021, 1:56pm

I can’t seem to figure this error.

var FreightTotal = (from invcDtl in Db.InvcDtl
                   where invcDtl.InvoiceNum == InvoiceNum
                   select invcDtl.InvoiceNum
                   ).ToList().Sum();
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
  foreach (var invcDtl in (from InvcDtlRow in Db.InvcDtl
    where InvcDtlRow.Company == Session.CompanyID && InvcDtlRow.InvoiceNum == InvoiceNum
      select InvcDtlRow))
      {
        FreightTotal = Convert.ToInt32(FreightTotal);
        InvcDtl.TotalFreight_c =  Convert.ToDecimal(FreightTotal); 
      }
 Db.Validate();
 txScope.Complete();
}

The column is apart of InvcDtl_UD…

System.Drawing.Bitmap	CS0120	An object reference is required for the non-static field, method, or property 'InvcDtl.TotalFreight_c'

dhewi · March 1, 2021, 2:02pm

Depending on the type of BPM, you may have to refer to UD fields in either of the forms

InvcDtl["TotalFreight_c"] = FreightTotal
InvcDtl.SetUDField<System.Decimal>("TotalFreight_c",FreightTotal)

hkeric.wci · March 1, 2021, 2:03pm

Also make sure its lower case

invcDtl and not InvcDtl

aarong · March 1, 2021, 2:26pm

I can’t for the life of me work this GroupBy and sum fields for my two fields in my original sql query.

var FreightTotal = (from invcDtl in Db.InvcDtl
                   where invcDtl.InvoiceNum == InvoiceNum
                   select invcDtl.InvoiceNum
                   ).ToList().Sum();
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
  foreach (var invcDtl in (from InvcDtlRow in Db.InvcDtl
    where InvcDtlRow.Company == Session.CompanyID && InvcDtlRow.InvoiceNum == InvoiceNum
      select InvcDtlRow))
      {
        invcDtl["TotalFreight_c"] = FreightTotal;
        invcDtl.SetUDField<System.Decimal>("TotalFreight_c",FreightTotal);
      }
 Db.Validate();
 txScope.Complete();
}

Anyone able to slap it in there or an example of where to put it… Daryl did say within the foreach section but I keep getting errors in there!.

dhewi · March 1, 2021, 2:42pm

If you put it within the foreach, you’ll need a different variable for the row, so the two don’t clash, and you’ll need to sum the charge field not the invoice number.

It looks like you’re only doing this for a single invoice each time? In which case you only have a single value to write to all the lines? If that is the case then it won’t matter anyway and you can do it outside the foreach.

And you can choose one or the other of the two ways to set the field, you don’t need both.

dhewi · March 1, 2021, 2:47pm

Just copying, pasting, and tweaking, does this work better?

var FreightTotal = (from invcDtl in Db.InvcDtl
                   where invcDtl.InvoiceNum == InvoiceNum
                   select invcDtl.DocTotalMiscCharge
                   ).Sum();
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
  foreach (var invcDtl in (from InvcDtlRow in Db.InvcDtl
    where InvcDtlRow.Company == Session.CompanyID && InvcDtlRow.InvoiceNum == InvoiceNum
      select InvcDtlRow))
      {
        invcDtl["TotalFreight_c"] = FreightTotal;
      }
 Db.Validate();
 txScope.Complete();
}

aarong · March 1, 2021, 2:50pm

It grabs the invoice number and totals all of the lines ending in one single Freight Charge calculating all of the lines Misc Charges.

Does your code above do that?

dhewi · March 1, 2021, 2:53pm

Should do, assuming you’ve already sorted the InvoiceNum variable before this. It’s your code, still! I haven’t done much to it.

aarong · March 1, 2021, 3:14pm

Unfortunately, it’s not updating the InvcDtl.TotalFreight_c

I put this back into SQL with a dummy invoice number which has £100 on Line 1 - £50 on Line 2, - £20 on Line 3 and SQL pulls back £170 but the TotalFreight_c is blank

SELECT   Erp.InvcDtl.InvoiceNum, Erp.InvcDtl_UD.TotalFreight_c, SUM(DocTotalMiscChrg)
FROM         Erp.InvcDtl INNER JOIN
                         Erp.InvcDtl_UD ON Erp.InvcDtl.SysRowID = Erp.InvcDtl_UD.ForeignSysRowID
WHERE     (Erp.InvcDtl.InvoiceNum = 134824)
GROUP BY InvoiceNum, TotalFreight_c

aarong · March 1, 2021, 3:16pm

My MD

dhewi · March 1, 2021, 3:16pm

Sorry, I forgot you need

invcDtl.RowMod = "U";

in there too.

But invoices can be finicky - it depends where you are in the process whether you can update them or not.

hkeric.wci · March 1, 2021, 3:20pm

Where are you setting this, it may just be blank or 0

aarong · March 1, 2021, 3:26pm

I’m setting it in a ABC under a variable InvoiceNum

dhewi · March 1, 2021, 3:27pm

Over to @aarong on that. It’s just a slightly altered version of his code, and I assumed there was more of it and InvoiceNum was already a set variable.

hkeric.wci · March 1, 2021, 3:32pm

Few changes I would do:

I would use UpdateMaster and then you will have access to a built in pre-populated variable ipInvoiceNum, ipInvoiceLine, cTableName, cGroupID

The reason yours does not work probably is that you must be updating InvcDtl for the ttInvcDtlRow to have something populated, if you update a Misc Charge the BPM would only have ttInvcChrg or so.