Thursday, January 22, 2015

LINQ Joins

Use Inner Join when you know you've got a match:

var innerJoinQuery =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID
    select new { ProductName = prod.Name, Category = category.Name }; //produces flat sequence


Use Group Join when you want to see associated records in another set, for example:

var innerGroupJoinQuery =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    select new { CategoryName = category.Name, Products = prodGroup }; 

You can then perform a sub-query using that group if you want to, e.g.

var innerGroupJoinQuery2 =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    from prod2 in prodGroup
    where prod2.UnitPrice > 2.50M
    select prod2;

When you Left-Outer Join all the elemtns in the left source sequence are returned even if no matches are found in the right sequence. We use 'DefaultIfEmpty' to provide a NULL or a default value for the right-side element to produce if a left-side element has no matches. You can use null as the default or you can specify your own, e.g.

var leftOuterJoinQuery =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    from item in prodGroup.DefaultIfEmpty(new Product { Name = String.Empty, CategoryID = 0 })
    select new { CatName = category.Name, ProdName = item.Name };

See here for more info: https://msdn.microsoft.com/en-us/library/bb311040.aspx

Fixes to common .NET problems, as well as information on .NET features and solutions to common problems that are not language-specific.

Fixes to common .NET problems, as well as information on .NET features and solutions to common problems that are not language-specific.

Z