I didn't know about these shortcomings (I don't use LINQ much). Could you give e...

Mouse47 · on Sept 5, 2017

Here's a left outer join (taken from https://stackoverflow.com/questions/3404975/left-outer-join-...)

    from c in categories
    join p in products on c.Category equals p.Category into ps
    from p in ps.DefaultIfEmpty()
    select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName };

You're doing something called a 'group join' (second line) 'into' a new identifier (ps) above. I'm guessing it stuffs each matching result in 'p' into a list, which sorta makes sense. The second part mystifies me though - somehow 'from p in ps.DefaultifEmpty()' flattens the list back out into a denormalized result set while simultaneously preserving entries in 'c' with no matches.

What does 'p' point to now? Why is it seemingly created twice? Is 'ps' still available? For a simple left join this is way, WAY too convoluted IMO.

The second thing I'm talking about you can read about by finding out when EF throws a 'NotSupportedException' at runtime. (e.g. https://stackoverflow.com/questions/25337974/entity-framewor...). Basically not everything you can do in LINQ can be translated to SQL. But it won't tell you at compile time. You have to wait till run time, which is garbage IMO. The example I linked is trivial but it's easy to miss this kind of thing when you're optimizing ORM use (and trying to move as much computation to the DB as possible).