CODEWITHSUNDEEP
sqllinqleft-join
Lakshan
Lakshan

Reputation: 11

SQL -> LINQ - Multiple left joins with null values

I want to convert this sql query to LINQ

left join @categories oc   
     on OC.course_id = t.course_id  
     and isnull( t.offlinecategory_id,-1) = isnull(oc.offlinecategory_id,-1)

I tried in this way but results are not same in sql and LINQ

join drv in categories
on new { a1 = t?.CourseID ?? 0, a2 = (t?.OfflineCategoryID ?? -1) }
     equals new { a1 = drv.CourseId, a2 = drv.OfflineCategoryId ?? -1 } into cgroup
from oc in cgroup.DefaultIfEmpty()

Upvotes: 0

Views: 71

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27436

Consider using other syntax which represents LEFT JOIn, look at documentation: Collection selector references outer in a non-where case

var query =
     from t in sometable
     from oc in categories
          .Where(oc => oc.course_id = t.course_id && t.offlinecategory_id ?? -1 == oc.offlinecategory_id ?? -1)
          .DefaultIfEmpty()
     select new 
     {
          ....
     };

Upvotes: 0

Related Questions