CODEWITHSUNDEEP
c#linq
NULL
NULL

Reputation: 1589

multiple join on conditions sql to linq

how can i change the sql below to a linq. 

select distinct * from dbo.TbleA a
left outer join dbo.TbleB b on a.schid = b.schid
left outer join dbo.TbleC c on b.addrid=c.addrid
and c.userid=a.userid
where b.addrid=1

here is my linq version which is causing error:

from a in db.TbleA
join b in db.TbleB on a.schid equals b.schid
join c in db.TbleC on new { w = b.addrid, z = a.userid } equals new { w=(int?)c.addrid, z=c.userid}
where (b.addrid == 1)

i am getting error around here:

join c in db.TbleC on new { w = b.addrid, z = a.userid } equals new { w=(int?)c.addrid, z=c.userid}

i do understand where the problem is i am comparing to two tables in my join. thanks and the error is:

"the type of one of the expressions in the join clause is incorrect""Type inference failed in the call to join"

b.addrid - int, 
a.userid - string, 
c.addrid - int?
c.userid - string

Upvotes: 0

Views: 134

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1503290

We can't really tell what's wrong with your current query in terms of compilation without knowing the types involved, but it wouldn't be equivalent to your original SQL anyway, as you want left outer joins. I suspect you want something more like:

from a in db.TbleA
join b in db.TbleB on a.schid equals b.schid into bs
from b in bs.DefaultIfEmpty()
join c in db.TbleC on new { w = (int?)b?.addrid, z = a.userid }
               equals new { w = c.addrid, z = c.userid } into cs
from c in cs.DefaultIfEmpty()
where (b.addrid == 1)

That's if you can use C# 6 with the null conditional operator, of course. If not, you would at least logically need:

from a in db.TbleA
join b in db.TbleB on a.schid equals b.schid into bs
from b in bs.DefaultIfEmpty()
join c in db.TbleC on new { w = (b == null ? default(int?) : (int?)b.addrid), z = a.userid }
               equals new { w = c.addrid, z = c.userid } into cs
from c in cs.DefaultIfEmpty()
where b == null || b.addrid == 1

Upvotes: 2

Toxantron
Toxantron

Reputation: 2398

I suspect your error is caused by trying to compare anonymous classes within a LinQ statement which are not of the same type. I suggest you change the join to the following:

from a in db.TbleA
join b in db.TbleB on a.schid equals b.schid
join c in db.TbleC on new { w = (int?)b.addrid, z = a.userid } equals new { w=(int?)c.addrid, z=c.userid}
where (b.addrid == 1)

Upvotes: 0

Related Questions