CODEWITHSUNDEEP
c#linqlinq-to-sqlc#-4.0
DooDoo
DooDoo

Reputation: 13437

how to check next item in linq 2 sql query

I have such this table:

position             title
---------------------------
1                     "t1"
2                     "t1"
3                     "t2"
4                     "t1"
5                     "t2"

I want to filter "t1" title but for positions that current position and next position have 1 difference. According this if I want first "t1" the result should be [1,2].

How I can write this query using linq 2 sql?

Upvotes: 1

Views: 257

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1499770

Sounds like you want a self-join:

var query = from item1 in db.Items.Where(x => x.title == "t1")
            from item2 in db.Items.Where(x => x.title == "t1")
            where item1.position + 1 == item2.position
            select item1; // Adjust however you want, e.g. new { item1, item2 }

Now I don't know whether that will actually work in SQL... but logically it's what you want.

Upvotes: 2

Related Questions