0

I've been trying to perform a Left Join kind of expression in LINQ to Entities, however the DefaultIfEmpty method works differently to what I expected - it returns an empty row for each CounterNo that doesn't have a match in the Readings table.

var leftjoin = from counter in database.Counters
               join reading in database.Readings
               on counter.CounterNo equals reading.CounterNo into gj
               from x in gj.DefaultIfEmpty()
               select x;

This way I don't know which rows from the Counters table don't have a corresponding row the Readings table.

How do I make this work?

thespeedkills
  • 3
  • 3

1 Answers1

0

Sounds like you simply don't want to add in the from x in gj.DefaultIfEmpty(), and you instead want to have each item in the left table paired with a group of items in the right table (that group may have zero elements, which is how you know when there are no matching items) which is exactly the behavior you get when you remove that line.

Servy
  • 202,030
  • 26
  • 332
  • 449
  • Are you suggesting that I should group the records by the ID in the Counters table? That doesn't achieve what I want - the query won't return the rows from the left hand side table that don't have a corresponding value in the left hand side table. – thespeedkills May 08 '14 at 19:04
  • @thespeedkills As I said, all you need to do is remove that one line that I quoted. After you do that, `gj` will be a collection of all of the rows in the right table that match the current row in the left table. That collection will be of size zero if there are no matching items. It will be of size one if there is one matching item. It will be of size 10 if there are 10 matching items. You can add `where !gj.Any()` if you want all rows in the left table that match nothing in the right, for example. – Servy May 08 '14 at 19:06
  • 1
    Oh, I'm a bit new to Linq and didn't know that structure was viable. But let's say that I want to know the maximum value of the corresponing rows in the right hand side table (e.g. reading.Value). I'm afraid this is impossible to achieve with the solution you suggest. – thespeedkills May 08 '14 at 19:33
  • @thespeedkills How is it impossible? You have a sequence of rows, `gj`. If you want to find the maximum value of a sequence, there are LINQ operators for that, like, say, `Max`. – Servy May 08 '14 at 19:39
  • Okay, this is not what I initially wanted but it actually achieves my goal in an easier and probably more optimal way than what I was trying to do (I wanted to group up the result from a left outer join that uses DefaultIfEmpty method). Thanks a lot. – thespeedkills May 09 '14 at 12:30