CODEWITHSUNDEEP
c#sql-serverentity-frameworklinqlinq-to-sql
Tatranskymedved
Tatranskymedved

Reputation: 4371

Join by LINQ to Entities (EF) same Table based on Time

I have table containing information about time and some data. Expected result is to have the interval (in seconds) between two rows with data shown.

MyTable (origin):

------------------------------
|        Time        |  Data |
------------------------------
| 23.8.2018 14:00:00 |  abc  |
| 23.8.2018 14:00:02 |  def  |
| 23.8.2018 14:00:05 |  ghi  |
| 23.8.2018 14:00:06 |  jkl  |
------------------------------

Expected result:

-------------------
| interval| result|
-------------------
|    2    |  abc  |
|    3    |  def  |
|    1    |  ghi  |
|   NULL  |  jkl  | --optional result / might not exist
-------------------

Possible solutions I think of:

I conclude the result can be obtained by JOIN or by ZIP in the LINQ/SQL. As found, ZIP is not support by Linq to Entities, but should work in local variables.

Something like:

var lTmp = lData.Zip(lData.Skip(1), (a, b) => new
           {
               interval = (b.Time - a.Time).Seconds,
               result = a.Value
           }).ToList();

Thinking about JOIN, I came to "solution" where I can join the same table, by the next row, but I don't know how it should be written in LINQ. Considering IDs, I could join to the ID which is (+1) bigger the the previous one, but some rows might be missing and the rows must be sorted by Time (not ID).

And last, but not least, directly in SQL I can write something like this:

SELECT
    DATEDIFF(MILLISECOND,
             [Time],
             LEAD([Time], 1, null) OVER (ORDER BY [Time])) AS interval,
    result
FROM
    Data

The question:

Is is possible to write such query in LINQ (Linq to Entities) and how? Or do I need to put this in stored procedure/plain SQL query?

Upvotes: 2

Views: 79

Answers (1)

Richard
Richard

Reputation: 108975

LINQ (in all its forms) only supports equi-joins so join is out.

But you can do a subquery yourself that gets the next row by time:

from left in table
let right = (from x in table
             where x.Time > left.Time
             orderby x.Time
             select x).FirstOrDefault)
where right != null
select new {
    left.Data,
    interval = right.Time - left.Time
}

(You might need to use one of the SQL functions to perform the time difference.)

Often the database can optimise such a correlated subquery, so performance might not be too bad. Otherwise you'll likely need to send your own SQL.

Upvotes: 2

Related Questions