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
– jeroenh

2011-05-05 12:09:02 +00:00
Commented May 5, 2011 at 12:09 — jeroenh
– jeroenh, Commented May 5, 2011 at 12:09

Daniel Hilgarth · Accepted Answer · 2011-05-04 14:41:17Z

4

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

answered May 4, 2011 at 14:41

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mch_dk Over a year ago

I have my data inMemory now and I simply don't query the DB any longer - so I need to do something else. LINQ :-)

Erick Petrucelli · Accepted Answer · 2011-05-04 14:52:02Z

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.

answered May 4, 2011 at 14:52

Erick Petrucelli

15.1k9 gold badges68 silver badges89 bronze badges

1 Comment

mch_dk Over a year ago

I have my data inMemory now and I simply don't query the DB any longer - so I need to do something else. LINQ :-)

Community · Accepted Answer · 2017-05-23 12:12:02Z

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

Collectives™ on Stack Overflow

Convert SQL to Linq - how to?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related