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.
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.