Some help with LINQ and anonymous class

Kinetic ERP
, ,
1

I am putting a code wall of the complete code at the bottom of the post, but I will attempt to highlight what I think is important.

I am trying to add some columns to the New PO Suggestions landing page via Erp.BO.POSugg.GetRowsPlant
I have been able to add a few columns and so I began to code for the information I wanted

PartVendorDtls is a LINQ query that pulls in Company Part Plant data and some additional information building

var PartVendorDtls = ( from in join ...
select new
{
  sugPoDtl.Company,
  sugPoDtl.Plant,
  sugPoDtl.PartNum,

  SuggestedVendor {},
  PrimaryVendor {},
  AlternateVendor {},
}).Distinct().ToList();

/* This seems to work as intended */

// var poSummaryData = new List<object>(); // Create a list to store the PO summary data
List<object> poSummaryData = new List<object>();

foreach (var Part in PartVendorDtls)
{
    var partnum = Part.PartNum;
    var plant = Part.Plant;
    var company = Part.Company;
/* redacted */
    var partPOData = (
                         /* perform some PO aggregate calculations */
                          }).ToList();

    poSummaryData.AddRange(partPOData);
}

/* This seems to complete with data but does not appear to properly set properties*/

var ttAltVendorDtl = (from sugPoDtl in result.SugPoDtl
                         join poSugDtl in Db.SugPoDtl on sugPoDtl.SugNum equals poSugDtl.SugNum
                         join partdtl in poSummaryData on new { Company = sugPoDtl.Company, Plant = poSugDtl.Plant, PartNum = poSugDtl.PartNum } 
                           equals new { Company = (string)((dynamic)partdtl).Company, Plant = (string)((dynamic)partdtl).Plant, PartNum = (string)((dynamic)partdtl).PartNum }
                         select new
                         {
                             sugPoDtl,
                             partdtl
                         }).ToList();

/* I can force a join here by explicitly declaring type*/

foreach (var item in ttAltVendorDtl)
{
    var sugPoDtlRow = item.sugPoDtl;
    var partPlantRow = item.partPlant;
    var primaryVendorRow = item.primaryVendor;

    sugPoDtlRow["PrimaryVendorNum"] = primaryVendorRow.VendorNum;
};

This code generates the following error

<anonymous type: SugPoDtlRow sugPoDtl, object partdtl>’ does not contain a definition for ‘primaryVendor’ and no accessible extension method ‘primaryVendor’ accepting a first argument of type ‘<anonymous type: SugPoDtlRow sugPoDtl, object partdtl>’ could be found (are you missing a using directive or an assembly reference?)

I am trying to avoid creating a public class, but I will if I have to make this work.

If anyone has suggestions I would appreciate them.

Thanks,

and here is the complete code:

/* Alternate Vendor
**********************************
**********************************

Author: Kevin Barrow
Last Update: 04/02/2023

**********************************
**********************************
*/



var PartVendorDtls = (  from sugPoDtl in result.SugPoDtl
                        join PartPlant in Db.PartPlant on new { sugPoDtl.Company, sugPoDtl.Plant, sugPoDtl.PartNum } equals new { PartPlant.Company, PartPlant.Plant, PartPlant.PartNum }
                        join SuggVendor in Db.Vendor on new { sugPoDtl.Company, sugPoDtl.VendorNum } equals new { SuggVendor.Company, SuggVendor.VendorNum }
                        // Perform left join with PrimVendor
                        join PrimVendor in Db.Vendor on new { sugPoDtl.Company, VendorNum = PartPlant.VendorNum } equals new { PrimVendor.Company, PrimVendor.VendorNum } into PrimaryVendorGroup
                        from PrimaryVendor in PrimaryVendorGroup.DefaultIfEmpty() // Perform the left join and retrieve the results into PrimVendor
                        // Perform left join with AlternateVendor
                        join AlternateVendor in Db.Vendor on new { Company = Session.CompanyID, VendorID = PartPlant.SecVendorID_c } equals new { AlternateVendor.Company, AlternateVendor.VendorID } into AltVendorGroup
                        from AltVendor in AltVendorGroup.DefaultIfEmpty()
                                select new
                                {
                                    sugPoDtl.Company,
                                    sugPoDtl.Plant,
                                    sugPoDtl.PartNum,
                                    SuggestedVendor = new
                                    {
                                        VendorNum = SuggVendor.VendorNum,
                                        VendorID = SuggVendor.VendorID,
                                        VendorName = SuggVendor.Name,
                                    },
                                    PrimaryVendor = new
                                    {
                                        VendorNum = PrimaryVendor?.VendorNum,
                                        VendorID = PrimaryVendor?.VendorID,
                                        VendorName = PrimaryVendor?.Name,
                                        LeadTime = PartPlant?.LeadTime,
                                        MinOrderQty = PartPlant?.MinOrderQty
                                    },
                                    AlternateVendor = new
                                    {
                                        VendorNum = AltVendor?.VendorNum,
                                        VendorID = AltVendor?.VendorID,
                                        VendorName = AltVendor?.Name,
                                        Freq = PartPlant?.SVFreq_c,
                                        LeadTime = PartPlant?.SVLeadTime_c,
                                        Quota = PartPlant?.SVQuota_c,
                                        MinOrderQty = PartPlant?.SVMOQ_c
                                    }
                                }).Distinct().ToList();

// var poSummaryData = new List<object>(); // Create a list to store the PO summary data
List<object> poSummaryData = new List<object>();

foreach (var Part in PartVendorDtls)
{
    var partnum = Part.PartNum;
    var plant = Part.Plant;
    var company = Part.Company;
    var firstDayOfYear = new DateTime(DateTime.Now.Year, 1, 1);
    var lastNonPrimaryPODate  = new DateTime();
    var PrimaryVendorRow = Part.PrimaryVendor;
    var PrimaryVendorNum = PrimaryVendorRow.VendorNum;
    var AlternateVendorRow = Part.AlternateVendor;
    var AlternateVendorNum = AlternateVendorRow.VendorNum;
    var SuggestedVendorRow = Part.SuggestedVendor;
    var SuggVendorNum = SuggestedVendorRow.VendorNum;

    var partPOData = (from POHeader in Db.POHeader
                      join PODetail in Db.PODetail on new { Company = Session.CompanyID, PONum = POHeader.PONum } equals new { Company = PODetail.Company, PONum = PODetail.PONUM }
                      join PORel in Db.PORel on new { Company = PODetail.Company, PONum = PODetail.PONUM, POLine = PODetail.POLine } equals new { Company = PORel.Company, PONum = PORel.PONum, POLine = PORel.POLine }
                      where POHeader.Approve == true
                            && POHeader.Company == company
                            && PORel.Plant == plant
                            && PODetail.PartNum == partnum
                      group new { POHeader, PODetail, PORel } by new { POHeader.Company, PORel.Plant, PODetail.PartNum } into g
                      select new
                      {
                          Company = g.Key.Company,
                          Plant = g.Key.Plant,
                          PartNum = g.Key.PartNum,
                          SuggestedVendor = SuggestedVendorRow,
                          PrimaryVendor = PrimaryVendorRow,
                          AlternateVendor = AlternateVendorRow,
                          
                          LastPODate = g.Max(x => x.POHeader.OrderDate),
                          Total_PO_YTD = g.Where(x => x.POHeader.OrderDate >= firstDayOfYear )
                                     .Select(x => x.POHeader.PONum)
                                     .Distinct()
                                     .Count(),  
                          Primary_PO_YTD = g.Where(x => x.POHeader.OrderDate >= firstDayOfYear && (x.POHeader.VendorNum == PrimaryVendorNum))
                                     .Select(x => x.POHeader.PONum)
                                     .Distinct()
                                     .Count(),  
                          Alternate_PO_YTD = g.Where(x => x.POHeader.OrderDate >= firstDayOfYear && (x.POHeader.VendorNum == AlternateVendorNum))
                                     .Select(x => x.POHeader.PONum)
                                     .Distinct()
                                     .Count(),
                          OtherVendor_PO_YTD = g.Where(x => x.POHeader.OrderDate >= firstDayOfYear && (x.POHeader.VendorNum != PrimaryVendorNum) && (x.POHeader.VendorNum != AlternateVendorNum))
                                     .Select(x => x.POHeader.PONum)
                                     .Distinct()
                                     .Count(),
                          
                          LastNonPrimaryPODate = g.Where(x => x.POHeader.OrderDate >= firstDayOfYear && (x.POHeader.VendorNum != PrimaryVendorNum))
                                                .OrderByDescending(x => x.POHeader.OrderDate)
                                                .Select(x => x.POHeader.OrderDate)
                                                .FirstOrDefault(),
                          ConsecutivePrimaryPO = g.Select(x => new
                          {
                              Date = x.POHeader.OrderDate,
                              VendorNum = x.POHeader.VendorNum,
                              PrimaryVendor = PrimaryVendorNum
                          })
                          .Where(x => x.Date >= firstDayOfYear && x.Date >= lastNonPrimaryPODate)
                          .OrderBy(x => x.Date)
                          .ToList()
                          
                          }).ToList();

    poSummaryData.AddRange(partPOData);
}


var ttAltVendorDtl = (from sugPoDtl in result.SugPoDtl
                         join poSugDtl in Db.SugPoDtl on sugPoDtl.SugNum equals poSugDtl.SugNum
                         join partdtl in poSummaryData on new { Company = sugPoDtl.Company, Plant = poSugDtl.Plant, PartNum = poSugDtl.PartNum } 
                           equals new { Company = (string)((dynamic)partdtl).Company, Plant = (string)((dynamic)partdtl).Plant, PartNum = (string)((dynamic)partdtl).PartNum }
                         /* join partdtl in poSummaryData on new { Company = Session.CompanyID, poSugDtl.Plant, poSugDtl.PartNum } equals new { Company = partdtl.Company, partdtl.Plant, partdtl.PartNum }*/
                         select new
                         {
                             sugPoDtl,
                             partdtl
                         }).ToList();
                         

foreach (var item in ttAltVendorDtl)
{
    var sugPoDtlRow = item.sugPoDtl;
    var PrimaryVendorRow = item.partdtl.PrimaryVendor;
    var AltVendorRow = item.partdtl.AlternateVendor;
/*
    // Access fields directly from item
    sugPoDtlRow["PrimaryVendorNum"] = primaryVendorRow.VendorNum;
    sugPoDtlRow["PrimaryVendorID"] = primaryVendorRow.VendorID;
    sugPoDtlRow["PrimaryVendorName"] = primaryVendorRow.Name;

    if (altVendorRow != null)
    {
        sugPoDtlRow["AltVendorNum"] = altVendorRow.VendorNum;
        sugPoDtlRow["AltVendorID"] = altVendorRow.VendorID;
        sugPoDtlRow["AltVendorName"] = altVendorRow.Name;
        sugPoDtlRow["AltVendFrequency"] = partPlantRow.SVFreq_c;
        sugPoDtlRow["AltVendLeadTime"] = partPlantRow.SVLeadTime_c;
        sugPoDtlRow["AltVendQuota"] = partPlantRow.SVQuota_c;
        sugPoDtlRow["AltVendMOQ"] = partPlantRow.SVMOQ_c;
    }
/* */
};
2

I’m on a phone so it’s hard to look closely, but I can say the copious use of distinct scares me a little bit.

Try to narrow your criteria, you’ll thank yourself later.

3

From what I can see here, it looks like

Is creating a new List with items that contain a SugPoDtlRow and an object partdtl.

When you set your sugPoDtlRow, your ttAltVendorDtl item already contains that row as a variable. But your item doesn’t contain a partPlant, just the partdtl which is a row from the poSummaryData. I think you would need to access the items in that row, although I’m not positive of the syntax. Something like:

foreach (var item in ttAltVendorDtl)
{
    var sugPoDtlRow = item.sugPoDtl;
    var partPlantRow = item.partdtl.partPlant;
    var primaryVendorRow = item.partdtl.primaryVendor;

    sugPoDtlRow["PrimaryVendorNum"] = primaryVendorRow.VendorNum;
};
4

I could be wrong but
image

image
image771×203 5.92 KB

image
image749×130 6.34 KB

The different capitalizations could be causing you some errors.

5

Thank you for the advice, I appreciate it, although I don’t know how to do so and achieve the desired results.

In each case, using distinct feels appropriate, as I only want unique values without grouping those values on the unique field; when counting how many Total_PO_YTD for instance, if POHeader.PONum was in the group by then my result would always be 1. The same I believe is true for all the other values of partPOData.

I’m not a big fan of ‘distinct’ either, but my C# is not strong.

Thanks, Todd; I did notice that and corrected that error after posting this. Unfortunately, however, that did not resolve the issue.

I think my troubles begin in poSummaryData.AddRange(partPOData);

If instead of:

var ttAltVendorDtl = (from sugPoDtl in result.SugPoDtl
                         join poSugDtl in Db.SugPoDtl on sugPoDtl.SugNum equals poSugDtl.SugNum
                         join partdtl in poSummaryData on new { Company = sugPoDtl.Company, Plant = poSugDtl.Plant, PartNum = poSugDtl.PartNum } 
                           equals new { Company = (string)((dynamic)partdtl).Company, Plant = (string)((dynamic)partdtl).Plant, PartNum = (string)((dynamic)partdtl).PartNum }
                         /* join partdtl in poSummaryData on new { Company = Session.CompanyID, poSugDtl.Plant, poSugDtl.PartNum } equals new { Company = partdtl.Company, partdtl.Plant, partdtl.PartNum }*/
                         select new
                         {
                             sugPoDtl,
                             partdtl /* This line should bring in partdtl list object*/
                         }).ToList();

I change it to :

                         select new
                         {
                             sugPoDtl,
                             partdtl.Company
                         }).ToList();

I get the error:

‘object’ does not contain a definition for ‘Company’ and no accessible extension method ‘Company’ accepting a first argument of type ‘object’ could be found (are you missing a using directive or an assembly reference?)

This makes me think that poSummaryData is not getting properties assigned, preventing using them in subsequent queries or code.

Thanks again,
Kevin

7

I don’t think you need the add range, just the ToList.

after all of the lists are built I would have a section like below. This is a piece from my GetRowsPlant routine.

Where I an querying Part and PartPlant you will want to grab the data from your list you built above. I would probably throw the SugPoDtl SysRowID in the data to make the match solid.

foreach (var ttSugPoDtlRow in result.SugPoDtl.Where(ttSugPoDtl_Row=> Session.CompanyID == ttSugPoDtl_Row.Company &&
Session.PlantID == ttSugPoDtl_Row.Plant && ttSugPoDtl_Row["Date05"] == null && whereClauseSugPoDtl.Contains(ttSugPoDtl_Row.BuyerID) &&
ttSugPoDtl_Row.SugType == "M").OrderBy(ttSugPoDtl_Row => ttSugPoDtl_Row.PartNum).ThenBy(ttSugPoDtl_Row=> ttSugPoDtl_Row.OrderByDate))

{
  

  Ice.Diagnostics.Log.WriteEntry(" DEBUG In GetrowsPlant " + ttSugPoDtlRow.SugNum.ToString() + " pn " + ttSugPoDtlRow.PartNum);
  
  ttSugPoDtlRow["Date05"] = DateTime.Now;
  ttSugPoDtlRow.OverridePriceList = true;
  Part = Db.Part.Where(Part_Row => Part_Row.Company == ttSugPoDtlRow.Company  && Part_Row.PartNum == ttSugPoDtlRow.PartNum).FirstOrDefault();

  if (Part != null)
  {
    PartPlant = Db.PartPlant.Where(PartPlant_Row => PartPlant_Row.Company == ttSugPoDtlRow.Company && PartPlant_Row.PartNum == ttSugPoDtlRow.PartNum && PartPlant_Row.Plant == ttSugPoDtlRow.Plant).FirstOrDefault();
    if (PartPlant != null)
    {
      Part_MinOrderQty = PartPlant.MinimumQty;
      Part_MinOnHandQty = PartPlant.MinimumQty + PartPlant.SafetyQty;
      PartPlant_PrimWhse = PartPlant.PrimWhse;
    }