SQL to LINQ for SubQuery Entity Framework

Question

i want to convert this SQL to LINQ but i cant compile it. This is my sql to convert

SELECT  u.UserID ,
        A.Username ,
        A.Password ,
        A.CreatedOn
FROM    dbo.tbl_User U
        INNER JOIN dbo.tbl_UserAuthDetail A ON A.UserID = U.UserID
                                               AND A.CreatedOn IN (
                                               SELECT TOP 1
                                                        CreatedOn
                                               FROM     dbo.tbl_UserAuthDetail U2
                                               WHERE    U2.UserID = U.UserID
                                               ORDER BY CreatedOn DESC )

and this is my attempt so far

var q = from u in context.Users
                        join au in context.UserAuthDetails on u.UserID equals au.UserID && 
                        (from au2 in context.UserAuthDetails where au2.UserID == u.UserID orderby au2.CreatedOn descending select au2.CreatedOn).ToList().Contains(au.CreatedOn)

Any help would be appreciated.

TIA

John Woo · Accepted Answer · 2014-08-03 04:50:34Z

2

Note: This code is untested but I think you want to get the latest credential of each user.

var query = context.Users    
            .GroupJoin(context.UserAuthDetails, 
                    u => u.UserID ,     
                    d => d.UserID,   
                  (u, d) => new 
                  { 
                    User = u, 
                    Details = d.OrderByDescending(x => x.CreatedOn).Take(1)
                  });

You can remove .Take(1) to get all the records of the user and still the result is sorted in descending order.

answered Aug 3, 2014 at 4:50

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

1 Comment

John Woo Over a year ago

by the way you can also .FirstOrDefault() instead of .Take(1)so it will return an entity - not IEnumerable<Entity>.

AD.Net · Accepted Answer · 2014-08-03 04:42:27Z

0

from u in context.Users
join au in context.UserAuthDetails on u.UserID equals au.UserID && 
                        context.UserAuthDetails.Where(au2 => au2.UserID == u.UserID)
                                        .OrderByDescending(au2 => au2.CreatedOn)
                                        .Select(au => au2.CreatedOn)
                                        //.Take(1) //you have this in SQL
                                        .Any(auc=>auc == au.CreatedOn)

answered Aug 3, 2014 at 4:42

AD.Net

13.4k2 gold badges30 silver badges47 bronze badges

Collectives™ on Stack Overflow

SQL to LINQ for SubQuery Entity Framework

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related