CODEWITHSUNDEEP
c#entity-frameworklinq
Alex
Alex

Reputation: 3998

Linq entity look ahead to value in next row

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

Answers (1)

NetMage
NetMage

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

Related Questions