locked
SQL to LINQ Lambda RRS feed

  • Question

  • User-34860367 posted

    Hello all,

    I am new to LINQ. I have T-SQL but I like to convert it to LINQ lambda expression as follows:

    SELECT DISTINCT p.Id, p.Date, p.LastName, p.FirstName, p.Gender, e.EventLocationId
    FROM [dbo].[Vw_Personal] AS p
    left join EventReservations r on (p.Id = r.Id)
    left join Events e on (r.EventId = e.EventId)

    I really appreciate your help. Thanks in advance.

    Tuesday, January 8, 2019 8:47 PM

Answers

  • User1724605321 posted

    Hi avt2k7,

    You can refer to below linq query , modify that to meet you requirement :

    var query = 
        from um in db.UserMasters
        join ct in db.CustTests on um.UserId equals ct.UserID
        into ctGroup from ct in ctGroup.DefaultIfEmpty() // left outer join
        join ts in db.TestInvitaions on ct.TestId equals ts.TestID
        into tsGroup from ts in tsGroup.DefaultIfEmpty() // left outer join
        where um.CustomerId == UserSession.CustomerId
           && um.RoleId == 4
           && um.Status == 1
        group ts by new { um.UserId, um.Email, um.FirstName, um.CreatedOn } into g
        select new UserModel
        {
            Name =  g.Key.FirstName,
            CreatedOn = g.Key.CreatedOn,
            EmailId = g.Key.Email,
            UserId = g.Key.UserId,
            NoOfTestTaken = g.Where(ts => ts != null).Select(ts => ts.TestID).Distinct().Count(),
            NoOfInvitationsSent = g.Count(ts => ts != null)
         };
    

    You can also refer to below threads for more details :

    https://stackoverflow.com/questions/788984/getting-distinct-rows-from-a-left-outer-join/15065307 

    https://stackoverflow.com/questions/10914720/linq-to-objects-left-outer-join-distinct-object-property-values-to-an-aggregat 

    Best Regards,

    Nan Yu

    Best Regards,

    Nan Yu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 9, 2019 8:38 AM