Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Question Unanswered: Linq joining a table on itself

    I am trying to convert the following sql to linq

    SELECT R.UserID,R.Cntrl_nbr FROM User R
    WHERE (
    R.REG_ID=
    (SELECT MAX(B.REG_ID)
    FROM User B
    WHERE R.UserID = B.UserID )

    The linq I am using is

    var query = (from n2 in q1
    where
    n2.RegServiceTs == (q1.Where(c1 => c1.UserID == n2.UserID).Max(c2 => c2.REG_ID))
    select n2).SingleOrDefault();

    This is slow and is there a better way to do this? Help appreciated

  2. #2
    Join Date
    Jan 2004
    Posts
    49
    Hm...

    var q = TUsers.Join(
    TUsers.GroupBy(u => u.UserID).Select(grp => new {UserID = grp.Key, REG_ID = grp.Max(t => t.REG_ID)})
    ,e => new {e.UserID, e.REG_ID}
    ,o => new {o.UserID, o.REG_ID}
    ,(e, o) => new {e.UserID, e.REG_ID, e.Cntrl_nbr}
    ).SingleOrDefault();

    /*
    SELECT [t0].[UserID], [t0].[REG_ID], [t0].[Cntrl_nbr]
    FROM [TUser] AS [t0]
    INNER JOIN (
    SELECT MAX([t1].[REG_ID]) AS [value], [t1].[UserID]
    FROM [TUser] AS [t1]
    GROUP BY [t1].[UserID]
    ) AS [t2] ON ([t0].[UserID] = [t2].[UserID]) AND ([t0].[REG_ID] = [t2].[value])
    */

    P.S.: try to Download LINQPad for debug your linq queries

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •