Linq - Equivalent to BETWEEN inside a Left Join

I've seen this topic BETWEEN 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 inside a "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
                开发者_StackOverflow中文版        }
                     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)
                   };

WorkAround:

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 a workaround Linq translate to 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 where before "select new", but I have to implement by this way, with "between" inside the left join, How can I do this ?

---

Edit - Added DefaultOrEmpty() in order for it to be a Left Join

Modify your query like so, this will force the where clause into the join on clause. It wont give you the Between clause in the Join, but at least there wont be a where clause

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

---

SQL Server should consider your original query and the example produced by LINQ to be identical, because WHERE model.Start <= Tcar.year and model.End >= Tcar.year and ON Tcar.year between model.Start and model.End both specify join conditions.

It's generally preferred to use ON because it keeps your join conditions separate from your other search criteria, but that's for readability rather than performance. Testing similar queries on a couple of tables I had lying around produces identical query plans, and I'd be surprised if you see different plans for your tables.