How to extract max(property) from left join in LINQ with lambda expression

Question

User-604164819 posted

I have fourtables:

• CL_ProductType
• CL_InsuranceProduct
• PR_Product
• PR_ProductInsuranceProduct (aggregation table for PR_Product and CL_InsuranceProduct)

I need left join for PR_ProductInsuranceProduct and I've done it with SelectMany() selector. The problem is that this query has groupBy method, and I need to extract the max(ID_ProductInsuranceProduct).

My question is: How to extract in .SelectMany() the highist value of ID_ProductInsuranceProduct?

SQL that works:

select p.ID_Product,p.Name, p.Code, p.InProduction, MAX(pip.ID_ProductInsuranceProduct)
from PR_Product p
join CL_ProductType pt  ON p.ID_ProductType = pt.ID_ProductType
left join PR_ProductInsuranceProduct pip ON p.ID_Product = pip.ID_Product
join CL_InsuranceProduct ip ON pip.ID_InsuranceProduct = ip.ID_InsuranceProduct
GROUP BY p.ID_Product,p.Name, p.Code, p.InProduction

My code in C# and LINQ Lambda:

 var query = DBContext.PR_Product
            .Where(m => m.Active)
            .Where(nameFilter)
            .Where(activationDateFilter)
            .Where(closureDateFilter)
            .Where(productTypeFilter)
            .Where(subgroupFilter)
            .Where(inproductionFilter)
            .Where(answerFilter)
            .Where(insuranceProductFilter)
            .Where(excludePidsFilter)
            .Join(DBContext.CL_ProductType, p => p.ID_ProductType, pt => pt.ID_ProductType,
                (p, pt) => new { p, pt = pt.Name })
            .GroupJoin(DBContext.PR_ProductInsuranceProduct,
                p => p.p.ID_Product,
                pip => pip.ID_Product,
                (p1, pip) => new { Products = p1, ProductInsuranceProduct = pip })
                .SelectMany
                (
                    x => x.ProductInsuranceProduct.DefaultIfEmpty(), 
                    (x, y) => new
                    {
                        x.Products.p.ID_Product,
                        x.Products.p.Name,
                        x.Products.p.Code,
                        x.Products.p.ActivationDate,
                        x.Products.p.ClosureDate,
                        x.Products.pt,
                        x.Products.p.InProduction,
                        //Here I want to fill in to my custom property max for ID_ProductInsuranceProduct, MaxId is a custom property in a model
                        x.Products.p.MaxId = x.ProductInsuranceProduct.Max(pip => pip.ID_ProductInsuranceProduct)
                    })
                .GroupBy(x =>
                  new
                  {
                      x.ID_Product,
                      x.Name,
                      x.Code,
                      x.ActivationDate,
                      x.ClosureDate,
                      x.pt,
                      x.InProduction,
                  });

I assume, beacause it's a SelectMany, that my code returns "flatten" data into one single table, therefore, my method Max, its input is bad, because its not a collection?

Can I do left join in linq with just .Select()?

My continuation of the code, when I execute the query:

 count = query.Count();

        var list = query
            .OrderBy(x => x.FirstOrDefault().Code)
            .DoPaging(pageSize, pageIndex)
            .ToList();

        List<PR_Product> products =
            (from m in list
             select new PR_Product
             {
                 ID_Product = m.Key.ID_Product,
                 Name = m.Key.Name,
                 Code = m.Key.Code,
                 ActivationDate = m.Key.ActivationDate,
                 ClosureDate = m.Key.ClosureDate,
                 ActivationDateString = m.Key.ActivationDate.ToString("d", new CultureInfo(DALParams.LCID, false)),
                 ClosureDateString = m.Key.ClosureDate.ToString("d", new CultureInfo(DALParams.LCID, false)),
                 ProductType = m.Key.pt,
                 InProduction = m.Key.InProduction
                 //MaxId = implemention...
             }).ToList();

Answer 1

User1520731567 posted

Hi nicolas999,

Firstly,I find there are something wrong with:

   .Where(m => m.Active)
            .Where(nameFilter)
            .Where(activationDateFilter)
            .Where(closureDateFilter)
            .Where(productTypeFilter)
            .Where(subgroupFilter)
            .Where(inproductionFilter)
            .Where(answerFilter)
            .Where(insuranceProductFilter)
            .Where(excludePidsFilter)

Is this part of code right?

Finally,

(x, y) => new
                    {
                        x.Products.p.ID_Product,
                        x.Products.p.Name,
                        x.Products.p.Code,
                        x.Products.p.ActivationDate,
                        x.Products.p.ClosureDate,
                        x.Products.pt,
                        x.Products.p.InProduction,
                        //Here I want to fill in to my custom property max for ID_ProductInsuranceProduct, MaxId is a custom property in a model
                        x.Products.p.MaxId = x.ProductInsuranceProduct.Max(pip => pip.ID_ProductInsuranceProduct)
                    })

This highlight code will cause a compilation error.

Like the picture:

I suggest you could modify the code like:

                  (x, y) => new
                   {
                       ID_Product = x.Products.p.ID_Product,
                       Name= x.Products.p.Name,
                       Code= x.Products.p.Code,
                       ActivationDate= x.Products.p.ActivationDate,
                       ...
                        //Here I want to fill in to my custom property max for ID_ProductInsuranceProduct, MaxId is a custom property in a model
                      MaxId = x.ProductInsuranceProduct.Max(pip => pip.ID_ProductInsuranceProduct)
                   })
                  .GroupBy(x =>
                  new
                  {
                      x.ID_Product,
                      x.Name,
                      x.Code,
                      x.ActivationDate,
                      ...
                      x.MaxId 
                  });

Best Regards.

Yuki Tao