CODEWITHSUNDEEP
c#sqlsql-serverlinq
gh9
gh9

Reputation: 10703

Conversion Of left Outer join with a where clause to Linq-to-sql

Below I have two queries. I have a Sql Query and a linq query I have been tinkering with. I have not be able to get the linq query to be logically equivalent to the sql query. My current issue exists on the line with the following comment //right here.

I am getting a compile error trying to access u.isDeleted:

The name 'u' does not exist in the current context

Can someone tell me why and help me fix the query to compile please. If this isn't an optimal way of doing it, I am open to suggestions. Thank you very much!!!!

Linq (which doesn't work)

var ret = (from m IN db.Foo
           join u in db.Bar on m.Id equals u.m_Id
           into FooBars
           from vm IN db.Temp.DefaultIfEmpty()
           where  vm == null && u.Id = 32 // right here
           select m).ToList();

Sql Query, which I would like to convert to linq 

SELECT m.*
FROM Foo AS m
INNER JOIN Bar AS u ON m.Id = u.m_Id
LEFT JOIN Temp AS vm on u.M_Id = temp.m_Id
WHERE vm.id IS NULL AND u.Id = 32

Upvotes: 0

Views: 864

Answers (1)

Gilad Green
Gilad Green

Reputation: 37299

I think you got the left join syntax wrong. This is how:

var ret = (from m IN db.Foo
           join u in db.Bar on m.Id equals u.m_Id
           join vm in db.Temp on u.M_Id = temp.m_Id into vmg
           from vm im vmg.DefaultIfEmpty()
           where vm == null && u.IsDeleted
           select m).ToLIST();
  • The into X syntax is GroupJoin method - so you are using it wrong when joining to db.Bar - there you want a normal join
  • When performing a left join you first use a GroupJoin and then you select from the group adding DefaultIfEmpty.

See documentation of Left Join - it shows both in query syntax like above and also in method syntax (and uses GroupJoin)

As for your compile error:

The name 'u' does not exist in the current context

Your query:

var ret = (from m IN db.Foo
    join u in db.Bar on m.Id equals u.m_Id
    into FooBars
    from vm IN db.Temp.DefaultIfEmpty()
    where  vm == null && u.Id = 32 // right here
    select m ).ToList();

Reason being is that you grouped all the results of the join to FooBars - so after that line there is no such thing as u in your query

Upvotes: 4

Related Questions