locked
how convert lambda expression to sql RRS feed

  • Question

  • Hi All.

    How to convert this lambda expression to SQL select

    List<int> empIds = context.EmpDeptartments.Where(ed => ed.GroupId == groupid).Select(ed => ed.EmpID).ToList();
    
    var query = context.Employee.
        Join(context.EmpDeptartments, e => e.ID, ed => ed.EmpID,
        (e, ed) => new { e.ID, e.Name, e.Description, ed.EmpID, ed.GroupId, ed.CreatedDate, ed.CreatedBy, ed.UpdatedDate, ed.UpdatedBy }).
        Where(e => !empIds.Contains(e.ID))

    where groupid is variable.

    Thanks.

    • Edited by zleug Wednesday, September 23, 2020 10:31 PM
    Wednesday, September 23, 2020 10:11 PM

Answers

  • Hi zleug, 
    You can join two tables via "join on" statement and use "not in " statement in sql to exclude some data that meet the conditions. (Equal to your "Where(e => !empIds.Contains(e.ID))" statement)
    The following is the complete sql statement you can refer to.

    select e.ID, e.Name, e.Description, ed.EmpID, ed.GroupId, ed.CreatedDate, ed.CreatedBy, ed.UpdatedDate, ed.UpdatedBy from Employee e join EmpDeptartments ed on e.ID=ed.EmpID where e.ID not in (select EmpID from EmpDeptartments where GroupId = groupid)

    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 24, 2020 7:28 AM

All replies

  • Hi zleug, 
    You can join two tables via "join on" statement and use "not in " statement in sql to exclude some data that meet the conditions. (Equal to your "Where(e => !empIds.Contains(e.ID))" statement)
    The following is the complete sql statement you can refer to.

    select e.ID, e.Name, e.Description, ed.EmpID, ed.GroupId, ed.CreatedDate, ed.CreatedBy, ed.UpdatedDate, ed.UpdatedBy from Employee e join EmpDeptartments ed on e.ID=ed.EmpID where e.ID not in (select EmpID from EmpDeptartments where GroupId = groupid)

    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 24, 2020 7:28 AM
  • Try this variation too:

    select e.ID, e.Name, e.Description, ed.EmpID, ed.GroupId, ed.CreatedDate, ed.CreatedBy, ed.UpdatedDate, ed.UpdatedBy
    from Employee as e
    inner join EmpDeptartments as ed on ed.EmpID = e.ID
    where ed.GroupID <> @groupId
    

    ‘@groupId’ is parameter that corresponds to your groupid variable.

    Thursday, September 24, 2020 8:46 AM
  • Hi zleug,
    Has your problem been solved? If it is resolved, please mark it as answer. So it can help other people who have the same problem find a solution quickly.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 28, 2020 8:42 AM
  • Hi Daniel_Zhang-MSFT.

    Thanks.

    Thursday, October 1, 2020 1:25 AM