Linq - BETWEEN equivalent inside left join

Question

I saw this topic BETWEEN AN EQUIVALENT in LINQ

My original query in SQL:

SELECT ISNULL(Tcar.name, '') FROM dbo.models model LEFT JOIN cars Tcar on Tcar.model = model.id AND Tcar.year between model.Start and model.End 

I need to implement between the "left join", I tried this:

My classes:

 public class car { public string name { get; set; } public int model { get; set; } public DateTime year { get; set; } } public class model { public int id { get; set; } public DateTime Start { get; set; } public DateTime End { get; set; } } 

My implementation:

 var theModel = from model in models join Tcar in cars on new { ID = (int)model.id, DateStart = (DateTime)model.Start, DateEnd = (DateTime)model.End } equals new { ID = (int)Tcar.model, DateStart = (DateTime)Tcar.year, DateEnd = (DateTime)Tcar.year } into tempCar from finalCar in tempCar select new { CAR = (finalCar == null ? String.Empty : finalCar.name) }; 

Decision:

 var theModel = from model in models join Tcar in cars on model.id equals Tcar.model where model.Start <= Tcar.year && model.End >= Tcar.year select new { CAR = Tcar.name }; 

If I use the Linq workaround, translate this query:

 SELECT Tcar.name FROM dbo.models model LEFT JOIN cars Tcar on Tcar.model == model.id WHERE model.Start <= Tcar.year and model.End >= Tcar.year 

I can put a simple one where in front of “select new”, but I have to implement this way, “between” inside the left connection, how to do it?

Answer 1

Edit - Added DefaultOrEmpty() to make it a left join

Modify your request so that it forces the where clause to join join on clause. This will not give you an Inter clause in Join, but at least there will not be a clause where

 var theModel = from model in models from Tcar in cars.Where(x => model.id == x.model) .Where(x => model.Start <= x.year && model.End >= x.year) .DefaultOrEmpty() select new { CAR = Tcar.name }; 

Answer 2

SQL Server should consider your original query, and the LINQ example will be identical because WHERE model.Start <= Tcar.year and model.End >= Tcar.year and ON Tcar.year between model.Start and model.End both indicate connection conditions.

It is generally recommended to use ON because it keeps the join conditions separate from other search criteria, but for readability, not performance. Testing such queries on several tables that I was laying down creates identical query plans, and I would be surprised if you see different plans for your tables.