SQL -> LINQ - Multiple left joins with null values

Question

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()

LINQ isn't SQL. It's the query language of ORMs like Entity Framework. It's the ORM's job to generate JOINs from the relations between entities (not tables, entities). Your Course entity should have a Category or Categories property. If you load a Course object then EF itself will generate the JOIN that also loads the related Category. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 5, 2024 at 9:25
No matter what, you should post the entities and even tables. Using ISNULL is quite simply a bug that prevents the use of indexes. NULLs aren't 0s in any database. Either use OR (t.offlinecategory_id is null AND offlinecategory_id is null) or store a default value in that field. If you care about performance the SQL query itself needs to change — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 5, 2024 at 9:27
@PanagiotisKanavos Just out of curiosity, what if we set the filtered index on nullable columns instead of having default values? — sa-es-ir
– sa-es-ir, Commented Nov 5, 2024 at 12:27
The problem is the function ISNULL not NULL. Indexes are generated based on the stored values and can't be used to speed up filtering on function results. You can create a computed column with the function result and index it but SQL Server won't use it automatically to accelerate functions. In PostgreSQL you can create indexes on expressions, which can be used — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 5, 2024 at 12:33
The question is incomplete: 1. How are the results not the same? 2. Which ORM do you use? Probably Entity Framework core. If so, please add the [entity-framework-core] tag and a tag for the version, e.g. [ef-core-8.0]. I already removed some irrelevant tags to make that possible. — Gert Arnold
– Gert Arnold, Commented Nov 5, 2024 at 12:45

Svyatoslav Danyliv · Accepted Answer · 2024-11-05 10:49:39Z

0

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 
     {
          ....
     };

answered Nov 5, 2024 at 10:49

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL -> LINQ - Multiple left joins with null values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related