Join in LINQ

Question

Hi,

 

How can i do Left Outer Join in LINQ also

How can i add two on conditions for join billow is the SQL query, which i need to use LINQ instead for.

 

Select * from Customer AS C LEFT OUTER JOIN Order AS O ON C.CustomerId = O.CustomerId INNER JOIN

Product AS P ON C.CustomerId = P.CustomerId AND O.OrderId = P.OrderId

 

Thanks

 

Arvind

 

 

Answer 1

If I remember well, LINQ To Entities doesn't support the DefaultIfEmpty method.

So in your case, you should do something like this (not tested)

 

Code Snippet

from c in context.Customers

select new

{

Customer = c,

Orders = c.Orders,

Products = from o in c.Orders

 join p in context.Products on

new { p.CustomerId, p.OrderId} equals new {c.CustomerId, o.OrderId}

};

 

(I suppose that you have a relationship between Customer and Order).

Answer 2

Hi,

 

Thanks for the reply. sorry In my case i dont have a relationShip between Customer and Order. The query i have posted is a simplified one, my actual query is litle diffrent and uses lot more tables.

Answer 3

Ok so you should do something like this:

 

Code Snippet

from c in context.Customers

let orders = context.Orders.Where (o => o.CustomerId == c.CustomerId)

select new

{

Customer = c,

Orders = orders,

Products = from o in orders

           join p in context.Products on new { p.CustomerId, p.OrderId} equals new {c.CustomerId, o.OrderId}

};

 

 

Answer 4

Hi,

 

I am getting an error as "The type of one of the expressions in the join clause is incorrect. Type interface failed in the call to join" in the Products join. is there some thing i am missing.

 

Thanks

 

Arvind

 

 

Answer 5

The (compiler error I assume?) indicates a type mismatch. Make sure the properties in the key have precisely the same types. In particular, check for nullable types.

 

I'll also offer some alternate query patterns for left outer joins and three-way joins in LINQ...

 

Just for reference, I'll repeat your SQL query here:

 

Select *

from 

    Customer AS C LEFT OUTER JOIN

    Order AS O ON C.CustomerId = O.CustomerId INNER JOIN

    Product AS P ON C.CustomerId = P.CustomerId AND O.OrderId = P.OrderId

 

The left outer join alone can be written as:

 

Code Snippet

from c in context.Customers

select new

{

    Customer = c,

    Orders = from o in context.Orders where o.CustomerId = c.OrderId select o

};

 

 

In LINQ queries, it is often desirable to handle left outer joins with nested results. The result include the outer element (the customer) and between 0 and n matching inner element (the orders).

 

The entire query can be written as:

 

Code Snippet

from c in context.Customers

from o in context.Orders

from p in context.Products

where c.CustomerId = o.CustomerId && c,CustomerId = p.CustomerId && o.OrderId = p.OrderId

select new { Customer = c, Order = o, Product = p };

 

 

Since the inner join clause in your original query includes a comparison between o.OrderId and p.OrderId, you can actually get away with an inner join since null values for o.OrderId will not match in any case.

 

Thanks,

-Colin

 

 

 

Answer 6

Imagine this query:

Select *

from 

    Customer AS C LEFT OUTER JOIN

    Order AS O ON C.CustomerId = O.CustomerId LEFT OUTER

    Product AS P ON C.CustomerId = P.CustomerId AND O.OrderId = P.OrderId

 

In this case, the DefaultIfEmpty should be good.

 

Answer 7

Matthieu,

As you mentioned earlier, the pattern involving DefaultIfEmpty to write LEFT OUTER JOINs is not supported in LINQ to Entities v1. Projecting the collection is the equivalent pattern that is supported. Does this answer your last question?

Diego

Answer 8

It was not a question

I just said that it's a shame that DefaultIfEmpty isn't supported.

Answer 9

Yikes, another unsupported extension method (I just posted on Single and SingleOrDefault not being supported).  This is not going to be fun converting my LINQ to SQL project since I use DefaultIfEmpty quite a bit as well. 

 

I think I'm swimming uphill trying to convert this project, especially trying to get EF to work with my home-grown multi-tier framework (see my other posts) which was written around LINQ to SQL to begin with.

 

I had big hopes for EF, especially that it would solve the multi-tier problem (which it really doesn't).  At this point, I may just shift into wait-and-see mode with EF for v2.  Or maybe I'll relent and convert my app to 2-tier mode.  Database connection for everybody.  Ugh.

 

-Larry

 

Answer 10

Matthieu and Lawrence,

We agree that the pattern that uses DefaultIfEmpty to define LEFT OUTER JOINs is useful and we would like to add support for it in future versions. Your feedback is much appreciated and will help us in our decision process.

In the meanwhile, I think Colin’s answer addresses the question on how to achieve similar results using LINQ to Entities v1.

Thanks,
Diego

Answer 11

Thanks for the response and thank you for listening. 

 

-Larry

 

 

Answer 12

I'm a bit confused by Colin's workaround for the LEFT OUTER JOIN.  Say my SQL looks like this:

 

Code Snippet

SELECT c.CustomerID, CompanyName, o.OrderID

FROM Customers AS c

LEFT OUTER JOIN Orders AS o

  ON o.CustomerId = c.CustomerId

WHERE (c.CustomerID = 'ALFKI') OR (c.CustomerID = 'PARIS')

 

 

When I run it I get the following:

 

ALFKI  Alfreds Futterkiste 10643
ALFKI  Alfreds Futterkiste 10692
ALFKI  Alfreds Futterkiste 10702
ALFKI  Alfreds Futterkiste 10835
ALFKI  Alfreds Futterkiste 10952
ALFKI  Alfreds Futterkiste 11011
PARIS  Paris spécialités   NULL

 

How do I write a LINQ to Entities query that will give me the *exact* same result set (i.e. flat and not shaped)?  In LINQ to SQL I would do it like this:

 

Code Snippet

from c in db.Customers
join o in db.Orders
  on c.CustomerID equals o.CustomerID
  into z1
from o in z1.DefaultIfEmpty()
where (c.CustomerID == "ALFKI") || (c.CustomerID == "PARIS")
select new
{
  c.CustomerID,
  c.CompanyName,
  OrderID = (Int32?)o.OrderID
}

 

Answer 13

You can't!

You can just do this:

Code Snippet

from c in db.Customers

where c.CustomerID == "ALFKI" || c.CustomerID == "PARIS"

select new

{

  c.CustomerID,

  c.CompanyName,

  Orders = c.Orders.Select(o => o.OrderID)

};

 

 

 

Answer 14

of course, you can what you want with LINQ To Object :

 

Code Snippet

var q =

from c in db.Customers

where c.CustomerID == "ALFKI" || c.CustomerID == "PARIS"

select new

{

  c.CustomerID,

  c.CompanyName,

  Orders = c.Orders.Select(o => o.OrderID)

};

 

var whatYouWant =

from c in q.AsEnumerable()

from o in c.Orders.Select(o => (int?) o).DefaultIfEmpty()

select new

{

  c.CustomerID,

  c.CompanyName,

  OrderID = o

};

 

 

 

Answer 15

OK, I fully understand now.  I was fearing that LINQ to Objects was the only workaround.  Yikes, that is a bit of a limitation.  Not fun if you have a ton of LINQ to SQL code that works great.  It's not as simple as refactoring by simply hanging your LINQ code off an Object Context instead of a Data Context.  It should be that simple (that's what LINQ as a standard is after all) but LINQ to Entities is only a partial implementation of LINQ; i.e. it doesn't fully support all LINQ operators.  Buyer beware.

 

 

Answer 16

I am not agree with you Lawrence. Indeed, LINQ To SQL also doesn't support the Aggregate method for example.

Answer 17

OK, I should have qualified my statements a bit.  There are of course differences between LINQ to Objects, LINQ to XML, LINQ to SQL.  Some operators just don't really apply (or aren't feasible) so they aren't supported for that specific provider.

 

But between LINQ to SQL and LINQ to Entities, they both ultimately target the same thing; i.e. a SQL database.  So I was hoping that the same set of operators would be supported.  The lack of support in LINQ to Entities for Single and DefaultIfEmpty are big ones, IMO.

 

I'm actually reading up on Entity SQL right now as a workaround to the LEFT OUTER JOIN problem; i.e. I won't be using LINQ to Entities in some places because of that.

 

-Larry

Answer 18

Lawrence Parker wrote:

But between LINQ to SQL and LINQ to Entities, they both ultimately target the same thing; i.e. a SQL database.  So I was hoping that the same set of operators would be supported.  The lack of support in LINQ to Entities for Single and DefaultIfEmpty are big ones, IMO.

 

I am agree and I am not. I will take an example. With LINQ To SQL, we use only SQL Server so we can imagine to have some recursive query with CTE. With LINQ To Entities, it's more difficult because we are not dependent of one DB provider. In my opinion we should be able to do this and if the DB provider doesn't support it, we should have an exception but I don't think it's the opinion of the EF team.

 

However I absolutely agree with you that DefaultIfEmpty and Single should be implemented even if I think these two operators aren't very important. Indeed, you can do this with another way.

Answer 19

MatthieuMEZIL wrote:

Imagine this query: Select * from      Customer AS C LEFT OUTER JOIN     Order AS O ON C.CustomerId = O.CustomerId LEFT OUTER     Product AS P ON C.CustomerId = P.CustomerId AND O.OrderId = P.OrderId   In this case, the DefaultIfEmpty should be good.

 

Hi,

 

This query is what im trying to do the last two days, but really i dont know how. I need to do a query with 2 or more LEFT OUTER JOIN.

 

Please can any help me?, thanks.

Answer 20

 

I think i found a solution but not sure if is the good one

 

var varQ= from t in Tipologia

select new {

t,

c=(from c in dc.Categoria where t.Categoria.Categoria_ID== c.Categoria_ID select c).FirstOrDefault(),

f=(from f in dc.Fase where t.Fase.Fase_ID==f.Fase_ID select f).FirstOrDefault()

};

 

SQL Query:

 

SELECT

1 AS [C1],

1 AS [C2],

[Extent1].[Tipologia_ID] AS [Tipologia_ID],

[Extent1].[Codigo] AS [Codigo],

[Extent1].[Nombre] AS [Nombre],

[Extent1].[Descripcion] AS [Descripcion],

[Extent1].[Tiempo] AS [Tiempo],

[Extent1].[Categoria_ID] AS [Categoria_ID],

[Extent1].[Fase_ID] AS [Fase_ID],

[Limit1].[Categoria_ID] AS [Categoria_ID1],

[Limit1].[Nombre] AS [Nombre1],

[Limit2].[Fase_ID] AS [Fase_ID1],

[Limit2].[Nombre] AS [Nombre2]

FROM [dbo].[Tipologia] AS [Extent1]

OUTER APPLY (SELECT TOP (1) [Extent2].[Categoria_ID] AS [Categoria_ID], [Extent2].[Nombre] AS [Nombre]

FROM [dbo].[Categoria] AS [Extent2]

WHERE [Extent1].[Categoria_ID] = [Extent2].[Categoria_ID] ) AS [Limit1]

OUTER APPLY (SELECT TOP (1) [Extent3].[Fase_ID] AS [Fase_ID], [Extent3].[Nombre] AS [Nombre]

FROM [dbo].[Fase] AS [Extent3]

WHERE [Extent1].[Fase_ID] = [Extent3].[Fase_ID] ) AS [Limit2]

Answer 21

any help?, thanks.

Answer 22

Wirlo,

Thanks for your example.  I've been searching the internet all day trying to find a LINQ example on how to do an outer join using the Entity Framework.  I can't believe such a common requirement is so difficult in the Entity Framework.  I have such a love-hate attitude towards the Entity Framework and the EF team.  They've made so many things good, but so many simple things unbelievably difficult.  Anyway, your solution is the only one I found that works.  Although it generates super ugly SQL using the OUTER APPLY, I guess it will have to do for now.  Thank You!!!

Answer 23

Wirlo, et.al.

I agree with your comments.  I have still not absorbed the code above but I have two L2E projects going that are killing me.  I believe that Entities are the future,  however I feel we are not there yet.  I expect 4.0 will be better but still not complete (jmho). 

But I still like Entities and am on the edge of bailing and going back to the old way.

Eric

Answer 24

plzzzzz help...! 

i am in big trouble.. 

i want to make this query using linq..  

SELECT [t1].[User_id] AS [User_id], [t0].[Resourse_name], [t1].[Status] AS [s]

FROM [dbo].[LoginResourses] AS [t0] 

LEFT OUTER JOIN [dbo].[LoginResourseMapping] AS [t1] 

ON ([t0].[Resourse_id]) = [t1].[Resourse_id] 

and [t1].[User_id] = 'E004'  

but m not able get this query with linq, what query i made i am sending you it includes "where" clause but i want "and" condition instead of "where"...

var q =from lr in db.LoginResourses

join lrm in db.LoginResourseMappings

on lr.Resourse_id equals lrm.Resourse_id

into tempid

from id in tempid.DefaultIfEmpty()

where

id.User_id == DDUser.SelectedValue

select new

{

id.User_id,

lr.Resourse_name,

lr.Status,

s = id.Status

};

result query is

SELECT [t1].[User_id] AS [User_id], [t0].[Resourse_name], [t0].[Status], [t1].[Status] AS [s] FROM [dbo].[LoginResourses] AS [t0] LEFT OUTER JOIN [dbo].[LoginResourseMapping] AS [t1] ON ([t0].[Resourse_id]) = [t1].[Resourse_id] "where" [t1].[User_id] = 'E004'

but i want and not where

thax in advance:-)