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

Question

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.

Possible duplicate of [LEFT OUTER JOIN in LINQ](https://stackoverflow.com/q/3404975/3404097) — philipxy, Jun 03 '20 at 04:36

Guru Stron · Accepted Answer · 2020-06-03T00:01:26.573

1

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()

edited Jun 03 '20 at 00:01

answered Jun 02 '20 at 23:35

Guru Stron

102,774
10
95
132

Thank you very much, that's very helpful. However when I use that query syntax, my EventProduct object is still null. https://imgur.com/a/VWq7i74 – rook218 Jun 03 '20 at 12:20
@rook218 do you have any `EventProducts` with `Event_index == currentEvent.index` ? – Guru Stron Jun 03 '20 at 12:22
I don't have any EventProducts at all. This view is where the user will add information to create EventProducts, and all data should coalesce to 0 if there is no product in the DB. The idea is that EventProducts will be a foreign key relationship between Event and Product, but with some extra information like sold, allocated, sampled, etc so they can track sales and stock for each product for each event. – rook218 Jun 03 '20 at 14:14
@rook218 Then it is normal behaviour. – Guru Stron Jun 03 '20 at 14:17
Thank you for all your help and I understand this is normal behavior, but it is not the desired behavior. – rook218 Jun 03 '20 at 14:35
@rook218 what behavior do you want? – Guru Stron Jun 03 '20 at 14:42
I want it to return all my products, and join to an EventProducts object which by default has an Event_id key of the current event, a Product_id key with one of my 8 (so far) products, and all my properties (allocated, sold, sampled, etc) should either return with the EventProduct that it finds in the DB, or if there is no entry in the DB they should be 0. – rook218 Jun 03 '20 at 15:36
@rook218 but wan't your SQL query return you NULL's? – Guru Stron Jun 03 '20 at 18:52
1

Yes, you're right, it does... That wasn't as big of an issue before because it was all in one table, and i could coalesce all null values easily in that table. Since the results of this are in a new object, I can't have my associated EventProduct object be null. Thank you for all your help, I've learned a ton about linq queries from working through this with you – rook218 Jun 03 '20 at 19:46
@rook218 was glad to help! – Guru Stron Jun 03 '20 at 19:49

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

1 Answers1