how to modify Inner join sql query to linq to entities.

lakshmi sowmya

13y1.6kLINQ

Need to modify the below sql query to linq to entities.

SELECT a.*, c.*
FROM bp_user a
INNER JOIN bp_audit_logintime c
ON a.UserName = c.User_Name
INNER JOIN
(
SELECT User_Name, MAX(Login_Time) maxDate
FROM bp_audit_logintime
GROUP BY User_Name
) b ON c.User_Name = b.User_Name AND
c.Login_Time = b.maxDate

we got struck near Inner Join with select sub query......Please help

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi13y ago
Accepted answer
Hi,
LINQ query some thing like....
var k = from u in context.bp_user
        join c in context.bp_audit_logintime on u.UserName equals c.UserName
join other in (from a in bp_audit_logintime
   group a by a.User_Name into g
   select new {
    UserName = User_Name, MaxTime = g.Max(x=>x.Login_Time)
   }) on new { un = c.User_Name , lt = c.Login_Time} equlas new { un = other.UserName , lt = other.MaxTime}
select new { u, c}
try it might have some syntax error
hope this will help you.
0
lakshmi sowmya13y ago
Hi, It worked for us when we have changed the User_Name to "Key". var k = (from u in objbidportalEntity.bp_user join c in objbidportalEntity.bp_audit_logintime on u.UserName equals c.User_Name join other in (from a in objbidportalEntity.bp_audit_logintime group a by a.User_Name into g select new { Username = g.Key, MaxTime = g.Max(x => x.Login_Time) }) on new { un = c.User_Name, lt = c.Login_Time } equals new { un = other.Username, lt = other.MaxTime } select new { u, c });
0