CODEWITHSUNDEEP
c#linqentity-frameworkouter-join
mcalex
mcalex

Reputation: 6798

Left Outer Join ObjectSet<T> to IQueryable

I'm trying to do a left outer join and inner joins on multiple DbContext.Sets, but the inner joins stop the outer join working properly. My tables:

Vendors:

ID, Name
V1, Alan
V2, Brad
V3, Cath

Regions:

ID, Name
R1, Ohio
R2, Utah
R3, Iowa

Orders:

ID, VendorID, RegionID
O1,       V1,       R1
O2,       V1,       R2
O3,       V2,       R1
O4,       V2,       R3
O5,       V3,       R2
O6,       V3,       R3

I want to run a query that returns all vendors showing the orders by region if they have any and blank values if they have none. So, if I wanted to run the query using Iowa as the region, the result would be: 

Vendor, OrderID
Alan,   
Brad,  O4
Cath,  O5

In sql, I would either use where value = 'bar' or value is null syntax or create a temp table with the inner joins, and then outer join the temp table and the outer join table. How do I do this in linq? I can't seem to get the syntax using '... or is null' and I can't outer join a Set and an IQueryable (which is what i get when I create the 'temp table' from a query.

This is my current attempt based on multiple googe searches and looking through SO:

var orders = from o in context.Orders
            where o.RegionID == region.ID  // region is an object from the EF model 'Region' class
            select o;

var vendorOrders = from v in context.Vendors
                   where vendorNameList.Contains(v.Name) // vendorNameList is a string []
                   join orders on v equals orders.Vendor into list
                   from vo in list.DefaultIfEmpty()
                   select new { Vendor = v, Order = orders == null ? "" : orders.ID };

This doesn't compile with 'orders is a variable, but is used like a type' on the "join orders on v equals orders.Vendor into list" line.

How do I get all vendors in to my result?

Upvotes: 0

Views: 1782

Answers (2)

kulotskie
kulotskie

Reputation: 331

try this code sir

var vendorOrders = from v in context.Vendors
                   where vendorNameList.Contains(v.Name) // vendorNameList is a string []
                   join v1 in orders.toList() on v equals v1.Vendor into list
                   from vo in list.DefaultIfEmpty()
                   select new { Vendor = v, Order = orders == null ? "" : orders.ID };

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Try this query

var vendorOrders = from v in context.Vendors.where(x=>vendorNameList.Contains(x.Name)) // vendorNameList is a string []
from o in context.Orders.where(x=>x.RegionID == region.ID && x. VendorID==v. ID).DefaultIfEmpty()
                   select new { Vendor = v, Order = orders == null ? "" : orders.ID };

Upvotes: 0

Related Questions