Entity Framework Join Two Tables If The Foreign Key Is A Nullable Column

Hello,

When retrieving records by joining two tables wherein the foreign key of the referenced table is a nullable column, and you want to return all records from the primary table, with or without the matching rows in the right table, the query would be using left join rather than inner join. So in LINQ expression, rather than inner join, revise the query to left join as presented below.

Show products with matching categories, disregarding other products without categories
MVC View:

Code:

1 2 3 4 5 6 7 8 9 10 11

model.Products .AddRange( (from item in context.Products .Where(item => item.Name.Trim().StartsWith(selectedLetter)) join category in context.ProductSubcategories on item.ProductSubcategoryID equals category.ProductSubcategoryID select new ProductModel() { ProductName = item.Name, ProductID = item.ProductID, ProductNumber = item.ProductNumber, Color = (string.IsNullOrEmpty(item.Color)) ? "NA" : item.Color, StandardCost = item.StandardCost, ProductCategory = (string.IsNullOrEmpty(category.Name)) ? "NA" : category.Name }).ToList());

Show products with or without Categories (Join Two Tables If The Foreign Key Is A Nullable Column)
Sql Server query using left join

MVC View

Code:

1 2 3 4 5 6 7 8 9 10 11

model.Products .AddRange( (from item in context.Products .Where(item => item.Name.Trim().StartsWith(selectedLetter)) join category in context.ProductSubcategories on item.ProductSubcategoryID equals category.ProductSubcategoryID into ProductCategory from category in ProductCategory.DefaultIfEmpty() select new ProductModel() { ProductName = item.Name, ProductID = item.ProductID, ProductNumber = item.ProductNumber, Color = (string.IsNullOrEmpty(item.Color)) ? "NA" : item.Color, StandardCost = item.StandardCost, ProductCategory = (string.IsNullOrEmpty(category.Name)) ? "NA" : category.Name }).ToList());