none
how to use join+in in lambda expression in Entity Framework RRS feed

  • Question

  • Hi there i have one sql query as below.


    select * 
    from Customers as C
    where
    c
    .CompanyID in (
     
    select CompanyID
     
    from Projects
     
    where Projects.ID in (
       
    select ProjectAssignment.ProjectID
       
    from ProjectAssignment
       
    where ProjectAssignment.EmployeeID = 7
     
    ) or Projects.ID in (
       
    select T.ProjectID
       
    from Tasks as T
       
    where t.AssignedUserID = 7
     
    )
    )

    How can i write it with lamda expression?

    Thanks in Advance
    Tuesday, June 14, 2011 12:39 PM

Answers

  • Hi,

    Without knowing your model and associations, something like this should give you almost the exactly the same sql:

    context.Customers.Where(row => context.Projects.Where(row2 => context.ProjectAssignment.Where(row3 => row3.EmployeeId == 7)
                                       .Select(row3 => row3.ProjectId).Contains(row2.Id) || 
                                   context.Tasks.Where(row3 => row3.AssignedUserId == 7)
                                       .Select(row3 => row3.ProjectId).Contains(row2.Id))
                            .Select(row2 => row2.CompanyID).Contains(row.CompanyID));
    

    This isn't that readable, but since you in your SQL doesn't use any joins, it's not easy to say how these tables are related to eachother.

    But if your project entity has a Customer navigation property, a ProjectAssignments navigation property and a Tasks navigation property, you could write it something like this:

    context.Projects.Where(row => row.ProjectAssignments.Where(row2 => row2.EmployeeId == 7).Any() || row.Tasks.Where(row2 => row2.Employee == 7).Any()).Select(row => row.Customer);
    

    This will probably give you the same result, but it's a lot more easier to read.

    Hope this helps!

     


    --Rune
    Tuesday, June 14, 2011 8:26 PM

All replies

  • Hi,

    Without knowing your model and associations, something like this should give you almost the exactly the same sql:

    context.Customers.Where(row => context.Projects.Where(row2 => context.ProjectAssignment.Where(row3 => row3.EmployeeId == 7)
                                       .Select(row3 => row3.ProjectId).Contains(row2.Id) || 
                                   context.Tasks.Where(row3 => row3.AssignedUserId == 7)
                                       .Select(row3 => row3.ProjectId).Contains(row2.Id))
                            .Select(row2 => row2.CompanyID).Contains(row.CompanyID));
    

    This isn't that readable, but since you in your SQL doesn't use any joins, it's not easy to say how these tables are related to eachother.

    But if your project entity has a Customer navigation property, a ProjectAssignments navigation property and a Tasks navigation property, you could write it something like this:

    context.Projects.Where(row => row.ProjectAssignments.Where(row2 => row2.EmployeeId == 7).Any() || row.Tasks.Where(row2 => row2.Employee == 7).Any()).Select(row => row.Customer);
    

    This will probably give you the same result, but it's a lot more easier to read.

    Hope this helps!

     


    --Rune
    Tuesday, June 14, 2011 8:26 PM
  • Any update? Would you mind letting us know how it goes?

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, June 16, 2011 8:01 AM