Entity Framework INNER JOIN with "BETWEEN" date range

Question

Entity Framework INNER JOIN with "BETWEEN" date range

c# entity-framework linq

Question

Is there a way with entity framework to do a "INNER JOIN" with a "BETWEEN"?

I have a table with a list of date of the first day of each month, and I want to associate all my record that are in the month so I can group by month after.

Anyway what I want to reproduce is something like that:

SELECT a.* 
FROM Assignments as a
INNER JOIN monthList as m ON ( m.Date BETWEEN a.StartDate AND a.EndDate)

Here is what I tried but doesn't work...

var query = (from a in Context.Assignments
             join m in monthList on (m >= a.StartDate && m <= a.EndDate);

One other thing to note is that montList is not part of my context.

Hemadeus

View more on Stack Overflow

nomad · Accepted Answer · 5/23/2017 11:50:38 AM

Look at this answer: LINQ Join On Between Clause

In a LINQ to Entities query two from in a row also produce INNER JOIN in SQL statement.

In your case you would have the following.

var query = from a in Context.Assignments
            from m in monthList
            where m >= a.StartDate && m <= a.EndDate
            select new { a.SomeProperty, a.AnotherProperty };

In your case, since monthList is not part of your DbContext object, Context.Assignments will be queried first to pull it into local memory and then this result will be inner joined with monthList to produce query object.

Entity Framework INNER JOIN with "BETWEEN" date range

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related