Convert SQL to Linq - how to?

Question

Is it possible to convert this SQL to LINQ?

SqlCommand cmd = new SqlCommand(@"
WITH new AS (
   SELECT [UserSessionSequenceID],
          [SessionGuid],
          SiteID,
          IP,
          UrlTitle,
          Url,
          Referer,
          BrowserWidth,
          BrowserHeight,
          [Timestamp],
          ROW_NUMBER() OVER (PARTITION BY [SessionGuid]  
                                 ORDER BY UserSessionSequenceID DESC) AS sort  
     FROM [tblSequence] 
    WHERE SiteID = @siteID
      AND [Timestamp] > DATEADD(mi, -@minutes, GETDATE()))
  SELECT TOP(@resultCount) 
         n.* 
    FROM new n
   WHERE n.sort = 1 
     AND NOT EXISTS (SELECT NULL
                     FROM tblSequence s
                    WHERE s.siteid = n.siteid
                      AND s.sessionguid = n.sessionguid
                      AND [TimeStamp] <= DATEADD(mi, -@minutes, GETDATE()))
ORDER BY n.usersessionsequenceid DESC
            ");
            cmd.Parameters.Add("@resultCount", SqlDbType.Int).Value = resultCount;
            cmd.Parameters.Add("@minutes", SqlDbType.Int).Value = minutes;
            cmd.Parameters.Add("@siteID", SqlDbType.Int).Value = siteID;

I have a class containing all the fields selected from the SQL called "SimpleSession". Thanks in advance for any help.

what did you try and where did you get stuck? – jeroenh May 05 '11 at 12:09 — jeroenh, May 05 '11 at 12:09

score 4 · Answer 1 · answered May 04 '11 at 14:41

4

To be honest, I would rather create a View for this statement and use that simple view in Linq2Sql...

answered May 04 '11 at 14:41

Daniel Hilgarth

171,043
40
335
443

I have my data inMemory now and I simply don't query the DB any longer - so I need to do something else. LINQ :-) – mch_dk May 04 '11 at 14:56

score 2 · Answer 2 · answered May 04 '11 at 14:52

In fact, many times there isn't no reason to convert complex SQL to some LINQ equivalent (even in case of compatible statements).

LinqToSql needs to dynamically create the SQL commands when running any LINQ command. So, do why we would want to overhead if we already know which is the best SQL query to solve?

We can easily create LINQ methods that call some View or Stored Procedure and returns it as typed objects. Your program will continue to be all fully object oriented but without unecessary overheads.

I have my data inMemory now and I simply don't query the DB any longer - so I need to do something else. LINQ :-) — mch_dk, May 04 '11 at 14:57

score 0 · Answer 3 · edited May 23 '17 at 12:12

for first query u can use this

from seq in tblSequence.AsEnumerable() where seq.Field("SiteID") == siteID && seq.Field("Timestamp") > DateTime.Now.AddMinutes(minutes) select new { seq.UserSessionSequenceID, seq.SessionGuid, seq.SiteID, seq.IP, seq.UrlTitle, seq.Url, seq.Referer, seq.BrowserWidth, seq.BrowserHeight, seq.Timestamp };

for row_number refer this How To Project a Line Number Into Linq Query Results

Convert SQL to Linq - how to?

3 Answers3