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.

Answer 1

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

Answer 2

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.

Answer 3

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