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.

1
2
10/28/2013 8:08:37 PM

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.

4
5/23/2017 11:50:38 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow