Linq to Entity with multiple left outer joins

Question

Hi,

I am trying to understand left outer joins in LINQ to Entity. For example I have the following 3 tables:

Company, CompanyProduct, Product

The CompanyProduct is linked to its two parent tables, Company and Product.

I want to return all of the Company records and the associated CompanyProduct whether the CompanyProduct exists or not for a given product. In Transact SQL I would go from the Company table using left outer joins as follows:

SELECT * FROM Company AS C
LEFT OUTER JOIN  CompanyProduct AS CP ON C.CompanyID=CP.CompanyID
LEFT OUTER JOIN  Product AS P ON CP.ProductID=P.ProductID 
WHERE      P.ProductID = 14 OR P.ProductID IS NULL

My database has 3 companies, and 2 CompanyProduct records assocaited with the ProductID of 14. So the results from the SQL query are the expected 3 rows, 2 of which are connected to a CompanyProduct and Product and 1 which simply has the Company table and nulls in the CompanyProduct and Product tables.

So how do you write the same kind of join in LINQ to Entity to acheive a similiar result?

I have tried a few different things but can't get the syntax correct.

Thanks.

Answer 1

Please try something like this:

from s in db.Employees
join e in db.Employees on s.ReportsTo equals e.EmployeeId
join er in EmployeeRoles on s.EmployeeId equals er.EmployeeId
join r in Roles on er.RoleId equals r.RoleId
where e.EmployeeId == employeeId &&
er.Status == (int)DocumentStatus.Draft
select s;

Cheers!

Answer 2

Thanks for the reply. Close but something is still wrong... The following code which I thought might be the equivalent of the SQL above brings back 2 of the 3 company records but the 3rd company (which is returned as a seperate row when executing normal left outer joins in SQL above) is missing.

var result = from c in Database.Company

join cp in Database.CompanyProduct on c.CompanyID equals cp.Company.CompanyID
join p in Database.Products on cp.Product.ProductID equals p.ProductID
where p.ProductID == productId || p.ProductID == null
select new { c, cp, p };

Any other thoughts? It seems like the ProductID == null in the where clause isn't working. It also takes about 4-5 seconds to execute where as the SQL is instant, so I've got to be doing something wrong!

Thanks for any help...

Answer 3

var result = from c in db.Company join cp on c.CompanyID equals cp.Company.CompanID into companyLeftJoin from resultObj in companyLeftJoin.DefaultIfEmpty() select new {RCompany = c, RCompanyProduct = resultobj};

OR

var result = from c in db.Company join cp on c.CompanyID equals cp.Company.CompanID into companyLeftJoin from resultObj in companyLeftJoin.DefaultIfEmpty() WHERE resultObj.Product.ProductID = 14 select new {RCompany = c, RCompanyProduct = resultobj};

Answer 4

Thanks for the reply.

Unfortunately, DefaultifEmpty is not supported in LINQ to ENTITIES

(see http://msdn.microsoft.com/en-us/library/bb738638.aspx)

so it spacks out and any help is still appreciated! :o)

Answer 5

What about this one (you do have a many-to-many relationship between Company and Product in your Entity Designer, don't you?):

from s in db.Employees
where s.Product == null || s.Product.ProductID == 14
select s;

Entity Framework should be able to figure out the type of join to use.

Answer 6

You'll want to use the Entity Framework to set up a many-to-many mapping from Company to Product. This will use the CompanyProduct table, but will make it unnecessary to have a CompanyProduct entity set in your entity model. Once you've done that, the query will be very simple, and it will depend on personal preference and how you want to represent the data. For example, if you just want all the companies who have a given product, you could say:

var query = from p in Database.ProductSet
            where p.ProductId == 14
            from c in p.Companies
            select c;

or

var query = Database.CompanySet
            .Where(c => c.Products.Any(p => p.ProductId == 14));

Your SQL query returns the product information along with the companies. If that's what you're going for, you might try:

var query = from p in Database.ProductSet
            where p.ProductId == 14
            select new
            {
                Product = p,
                Companies = p.Companies
            };

Please use the "Add Comment" button if you would like to provide more information, rather than creating another answer.

Answer 7

The normal group join represent a left outer join. Try that:

var list = from a in _datasource.table1
           join b in _datasource.table2
           on a.id equals b.table1.id
           into ab
           where ab.Count()==0
           select new { table1 = a, 
                        table2Count = ab.Count() };

That example gives you all records from table1 which haven't got a reference to table2. If you omit the where senetence, you get all records of table1.

Answer 8

LEFT OUTER JOINs are done by using the GroupJoin in Entity Framework:

http://msdn.microsoft.com/en-us/library/bb896266.aspx