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
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
See more on this question at Stackoverflow