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

  • 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();
    Thursday, August 9, 2018 11:45 AM

All replies

  • 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

    Friday, August 10, 2018 7:09 AM