Reputation: 3998
When querying my entity framework DB, I am trying to match a condition based on the values from 2 rows.
My current query looks like this:
var query = context.table.where(x => x.row > 2);
This all works fine.
What I now want to achieve is to query the table based on the value in current and next row eg:
var query = context.table.where(x => x.row > 2 && x.row[next row up in DB] < 2);
Can this be done.
I know I can achieve this in code, but can it be done in a single query using LINQ and entity?
Here is an example of how I would do this with SQL:
SELECT *
FROM t_Table p
INNER JOIN t_Table f
ON (p.id + 1) = f.id
WHERE p.column = whatever
AND f.column = whatever2
Upvotes: 0
Views: 410
Reputation: 26936
Translating your sample SQL into LINQ to SQL:
var ans = from p in t_table
from f in t_table
where (p.id+1) == f.id && p.column == whatever && f.column == whatever2
select new { p, f };
This does not appear to generate an inner join in SQL but rather a cross-join, but I assume the SQL engine will handle it appropriately. Note that LINQ can only do equi-joins.
I didn't realize you can do (some) expressions in LINQ joins as long as equal is the primary operator, and this generates a sub-select and an inner join, which seems quite a bit faster:
var ans = from p in t_table
where p.column == whatever
let pidplus1 = p.id+1
join f in t_table on pidplus1 equals f.id
where f.column == whatever2
select new { p, f };
Upvotes: 1