Join table on multiple conditions with LINQ, always show all results of primary key and coalesce nulls

user_10430070 0
Viewed 154

Essentially I'm trying to replicate this query, which works beautifully as a direct SQL query:

SELECT *
FROM Products p
LEFT OUTER
  JOIN EventProducts ep ON ep.Product_index = p.[index]
  AND COALESCE(ep.Event_index,'3') = '3'

I have two relevant tables:

  • Products: has index and other information
  • EventProducts: has index, Product.index, and Event.index and other information including sold, allocated, etc.

In my application view, the Event is already selected and its index will have a constant value. I want to select ALL products, and join the EventProduct data if it has an entry in the database then populate that information into a data grid.

If a Product has no associated EventProduct, it should return an object like:

{
    index = 1,
    name = productName,
    sold = 0,
    allocated = 0
    ...
}

but if there is an associated EventProduct entry, return the object

{
    index = 2,
    name = product2Name
    sold = 10
    allocated = 15
    ...
}

This is my LINQ query right now:

    var eventProducts = dbContext.Products
        .Join(dbContext.EventProducts,
            product => new { productIndex = product.index, eventIndex = currentEvent.index },
            eventProduct => new { productIndex = eventProduct.Product.index, eventIndex = eventProduct.Event.index },
            (product, eventProduct) => new
            {
                Product = product,
                EventProduct = eventProduct
            });

This query always returns 0 objects, where it should return 8 (for each of my products) even though my EventProducts table is currently empty.

entity-framework-6 entity-framework-core join linq
1 Answers

Join performs inner join, to perform left join you can either switch to query syntax looking something like this:

 var query = from product in dbContext.Products
     join eventProduct in dbContext.EventProducts.Where(ep => ep.Event_index == currentEvent.index) 
          on product.index equals eventProduct.Product_index into gj
     from sub in gj.DefaultIfEmpty()
     select new { Product,  EventProduct = sub };

Or use GroupJoin as in this answer.

Or, if Product entity has correctly set up navigation you can try to use Include with Where clause looking like this(not sure it will work though, can't check ATM):

 var productWithEventProducts = dbContext.Products
    .Include(p => p.EventProducts)
    .Where(p => p.EventProducts.Any(ep => ep.Event_index == currentEvent.index)
         || !p.EventProducts.Any())
    .ToList()
user_25012790
Related