Need help to convert T-Sql into Linq

Question

I have never used before Linq, and really have lack of time to study. My little knowledge is not enough to do this and I need your help. Here is a code which I need to convert into Linq. (I am using EF6 and context)

WITH messages AS (
    SELECT s.siteId,s.originator,s.sentTime,s.mode,s.mainsFrequency,s.gensetFrequency,
            s.dgBattery,s.runHours,s.fuel,s.messageID,s.messageText,
           ROW_NUMBER() OVER(PARTITION BY s.originator 
                                 ORDER BY s.sentTime DESC) AS rk
      FROM smsParseds s)
SELECT m.*
FROM messages m
WHERE m.rk = 1
order by m.sentTime DESC

So if you don't have the time to figure this out yourself, what makes you think anyone here should take the time to do your work for you? — MattD, Jan 13 '14 at 19:37
Thank you. I checked both ways given by dagarrison & Aducci. working. P.S. in Linq "DESC" must be as "descending". — ADO_kg, Jan 14 '14 at 09:28

score 0 · Answer 1 · edited May 23 '17 at 11:57

see Row_number over (Partition by xxx) in Linq? for doing the partition.

The rest is fairly basic linq syntax.

I haven't tested this or tried to compile it, so it may need a little modification. I split up the query similar to the question just for clarity, but these can easily be combined into one line.

var messages = smsParseds
   .OrderBy(o => o.sentTime).GroupBy(g => g.originator)
   .Select(s => new {s, rk = s.Count()})
   .SelectMany(sm => sm.s.Select(b => b)
      .Zip(Enumerable.Range(1,sm.rk), (j,i) => new {j.siteId, j.originator, j.sentTime, j.mode, j.mainsFrequency, j.gensetFrequency, j.dgBattery, j.runHours, j.fuel, j.messageID, j.messageText, rk = i}));

var result = messages
   .Where(w => w.rk = 1)
   .OrderByDescending(o => o.sentTime)

score -1 · Accepted Answer · answered Jan 13 '14 at 20:00

-1

This is not a direct translation (Entity Framework cannot use Row Number), but the results should be the same

var query = from m in context.Messages
            where (from x in context.Messages
                   where m.Originator == x.Originator
                   where x.SentTime > m.SentTime
                   select x).Any() == false
            orderby m.SentTime desc
            select new
            {
              m.siteId,
              m.originator,
              m.sentTime,
              m.mode,
              m.mainsFrequency,
              m.gensetFrequency,
              m.dgBattery,
              m.runHours,
              m.fuel,
              m.messageID,
              m.messageText,
            };

answered Jan 13 '14 at 20:00

Aducci

26,101
8
63
67

Since the original query filters based on the partition, this query will return many more rows than are desired. – David Garrison Jan 13 '14 at 20:04
@dagarrison, How so? The where clause is only including the latest records from each Originator. – Aducci Jan 13 '14 at 20:07
Oh I see what you are doing. You are correct, my mistake. It always takes me a few moments to process exactly what the `x.SentTime > m.SentTime` is doing. – David Garrison Jan 13 '14 at 23:41

Need help to convert T-Sql into Linq

2 Answers2