none
How to write subquery RRS feed

  • Question

  • Hi,

     

    I try to move our tools to OData and we are trying to find a solution for nested query for quite long time.

     

    We need to get absences in given project.

    Our database schema + entities schema is:

    - Absences - absences of ALL user in ALL projects. 

     

    - Project - list of projects

    - TeamMember -  team members tale with relation to Project table. UserID here should be the same as in Absence in case of absence record.

     

    SQL QUERY: 

    SELECT [Id], [UserId], [Date] FROM [Absence] WHERE UserId IN (SELECT UserId FROM TeamMember WHERE ProjectId={0})
    


     

    The problem for us is to write similar LINQ to Entities query 

     


    Sunday, July 10, 2011 6:04 PM

Answers

  • Hi Dusan;

    This should be what you are looking for.

    var query = from a in ObjectContext.Absence
          where (from u in ObjectContext.TeamMember 
              where u.ProjectId == 0
              select u.UserId).Contains(a.UserId)
          select new
          {
            Id = a.Id,
            UserId = a.UserId,
            Date = a.Date	
          };
    
    


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, July 11, 2011 4:42 AM

All replies

  • Hi Dusan;

    This should be what you are looking for.

    var query = from a in ObjectContext.Absence
          where (from u in ObjectContext.TeamMember 
              where u.ProjectId == 0
              select u.UserId).Contains(a.UserId)
          select new
          {
            Id = a.Id,
            UserId = a.UserId,
            Date = a.Date	
          };
    
    


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, July 11, 2011 4:42 AM
  • Hi Dusan,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[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.

    Monday, July 18, 2011 3:54 AM
    Moderator