none
Help with LINQ to SQL Query RRS feed

  • Question

  • Hi folks,

     

    I have the following SQL query I'd like to transform into a LINQ query:

     

    select userid,update_date,cluster_name,store_group,store_name from History as T
    where exists
    (select * from History where userid = T.userid
    and update_date <> T.update_date)
    order by userid,update_date desc

     

    I can't figure out the C# LINQ syntax being new to LINQ, any help?

     

    I have done the following and it works but was looking for a more elegant solution:

     

    string sql = @"select user_uuid,userid,update_date,cluster_name,store_group,store_name from History as T" +

    " where exists" +

    " (select * from History where userid = T.userid" +

    " and update_date <> T.update_date)" +

    " order by userid";

    var query = db.ExecuteQuery<History>(sql);

     

     

     

    v/r

    --Richard

    Wednesday, August 6, 2008 5:52 PM

Answers

  • I figured it out!

     

    var fubar = (from a in db.Histories

    from b in db.Histories

    where a.userid == b.userid && a.update_date != b.update_date

    orderby a.userid

    select a);

     

    Wednesday, August 6, 2008 7:13 PM

All replies

  • I figured it out!

     

    var fubar = (from a in db.Histories

    from b in db.Histories

    where a.userid == b.userid && a.update_date != b.update_date

    orderby a.userid

    select a);

     

    Wednesday, August 6, 2008 7:13 PM
  • First, you should probably change your query to have a better where clause.  I assume you're trying to select the records that represent multiple updates from the same user and I can think of more concise ways to write that.  Your best solution to use linq on this problem is to go to the linq to sql class and drag a stored procedure that contains the query onto the table that represents the objects you're returning.

    Using Linq to SQL syntax in your own code is meant for simple queries that you want to directly include .NET operators and logic in, not operations that are easily expressed in pure SQL and will perform better as a cached stored procedure anyway.

    Good pickup on the multiple from clauses thouhg.
    Wednesday, August 6, 2008 7:40 PM