Linq - Equivalent to BETWEEN inside a Left Join

Question

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

Answer 1

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