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.
Accepted Answer
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.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
