How to convert store procedure to linq in nopCommerce c#

Question

My store procedure query

SELECT p.Id
FROM Product p WITH (NOLOCK)
LEFT JOIN Discount_AppliedToProducts dap WITH (NOLOCK)
    ON p.Id = dap.Product_Id
LEFT JOIN Product_Category_Mapping pcm WITH (NOLOCK)
    ON p.Id = pcm.ProductId
LEFT JOIN Discount_AppliedToCategories dac WITH (NOLOCK)
    ON pcm.CategoryId = dac.Category_Id
        AND dac.Category_Id IN (1 ,2 ,3 ,4 ,5 ,6)
LEFT JOIN Product_Manufacturer_Mapping pmm WITH (NOLOCK)
    ON p.Id = pmm.ProductId
LEFT JOIN Discount_AppliedToManufacturers dam WITH (NOLOCK)
    ON pmm.ManufacturerId = dam.Manufacturer_Id
WHERE dap.Discount_Id IN (3)
    OR dac.Discount_Id IN (3)
    OR dam.Discount_Id IN (3)

My linq query

var productlist = (from q in _productRepository.Table
                                       select q).ToList();

var discount_AppliedToProductIds = (from dp in _discountRepository.Table
                                    from p in dp.AppliedToProducts
                                    select p).ToList().DistinctBy(d => d.Id).ToList();

var discount_AppliedToCategorieIds = (from dp in _discountRepository.Table
                                      from c in dp.AppliedToCategories
                                      select c).ToList().DistinctBy(d => d.Id).ToList();

var discount_AppliedToManufacturerIds = (from dp in _discountRepository.Table
                                         from m in dp.AppliedToManufacturers
                                         select m).ToList().DistinctBy(d => d.Id).ToList();

var product_Manufacturer_Mapping = (from dp in productlist
                                    from pm in dp.ProductManufacturers
                                    select pm).ToList().DistinctBy(d => d.Id).ToList();

var product_Category_Mapping = (from dp in productlist
                                from pc in dp.ProductCategories
                                select pc).ToList().DistinctBy(d => d.Id).ToList();

var ss = (from p in productlist
      join dap in discount_AppliedToProductIds on p.Id equals dap.Id
      join pcm in product_Category_Mapping on p.Id equals pcm.ProductId
      //join dac in discount_AppliedToCategorieIds on pcm.CategoryId equals dac.Id
      from dac in discount_AppliedToCategorieIds
      join pmm in product_Manufacturer_Mapping on p.Id equals pmm.ProductId
      join dam in discount_AppliedToManufacturerIds on pmm.ManufacturerId equals dam.Id
      from dapd in dap.AppliedDiscounts
      from pacd in dac.AppliedDiscounts
      from damd in dam.AppliedDiscounts
      where discountIds.Any(d => dapd.Id == d || d == pacd.Id || d == damd.Id)
      // innner join condition
      where categoryIds.Any(d => d == dac.Id) && dac.Id == pcm.CategoryId    
    select p).ToList();

I have write this code into c# but this code not provide proper result. Now I don't know what is problem into this code. If I run this code into sql server, then I get proper result, but in c# code I don't get proper result.

score 0 · Answer 1 · answered Dec 22 '17 at 09:54

It's been a long time since I wrote a query in LINQ, but I seem to recall that if you wish to model a LEFT JOIN, you have to use DefaultIfEmpty(), otherwise you end up with an INNER JOIN.

See this, it's answer shows where to apply DefaultIfEmpty:

Linq join iquery, how to use defaultifempty

Obviously if you don't correctly model a LEFT JOIN expression, you'll end up with results only when all 3 inputs produce values.

I would also suggest not using .ToList() on each of your source queries, because that's going to manifest data into memory and use LINQ to Objects for your final query. If you remove the .ToList(), LINQ will construct a single database query for the entire process.

Mark

How to convert store procedure to linq in nopCommerce c#

1 Answers1