1

I have an SQL query which i am converting to Linq and i want to use Left Join instead of Inner Join.

I have tried DefaultIfEmpty() method but i haven't had any luck.

The Sql query:

SELECT t0.*, t1.* FROM entity AS t0  
LEFT JOIN migration_logs AS t1 ON (CAST(t0.id AS CHAR) = t1.ObjectId and  'SASParty' = t1.ObjectType) 
where t1.status is null || t1.Status <> '1' ORDER BY t0.id LIMIT 0, 10;

The Linq Query:

Entities
.Join(Migration_logs, 
e => new { id = e.Id.ToString(), ObjectType = "SASParty" }, 
mlog => new { id = mlog.ObjectId, mlog.ObjectType }, 
(e, mlog) => new {e,mlog})
.Where(result => result.mlog.Status == null || result.mlog.Status !=  "1").DefaultIfEmpty().ToList()

I am using linqpad and when i execute the linq query it generates the following sql query:

SELECT t0.*
FROM entity AS t0
INNER JOIN migration_logs AS t1
ON ((CAST(t0.id AS CHAR) = t1.ObjectId) AND (@p0 = t1.ObjectType))
WHERE ((t1.Status IS NULL) OR (t1.Status <> @p1))

Some minor differences in the original query and generated sql query are there but i hope the problem statement is clear.

Any help would be appreciated.

Talal Tahir Malik
  • 165
  • 4
  • 16
  • See msdn left outer join sample : https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b – jdweng Jun 17 '19 at 16:05

2 Answers2

0

I was able to find a solution with the linq to sql query and using into clause. 

(from e in Entities
join mlog in Migration_logs
on new { id = e.Id.ToString(), ObjectType = "SASParty" }
equals new { id = mlog.ObjectId, mlog.ObjectType }
into results
 from r in results.DefaultIfEmpty()
 where r.Status == null || r.Status != "1"
 select new
 {
  e
 })
Talal Tahir Malik
  • 165
  • 4
  • 16
0

you want to perform the .DefaultIfEmpty() method on the quantity that you want to perform a left join onto. maybe this code snippet helps you

from e in Entities
join ml in Migration_lpgs on new { id=e.Id.ToString(), ObjectType="SASParty" } equals new { id=ml.Id.ToString(), mlog.ObjectType } into j
from e in j.DefaultIfEmpty()
where ml.Status == null || ml.Status !=  "1"
select e
Manuel Hess
  • 140
  • 9