Can you use projections on Entity Framework navigation properties?

Question

This is a question I can not answer. I currently have the following simple code snippet.

return _context.UserProfiles
                .Single(p => p.ID == userID)
                    .Organisations
                    .Select(p => new { ID = p.ID })
                    .First()
                    .ID;

This simply gets the first user profile (which pulls as SELECT * FROM UserProfiles WHERE...) which is fine. The problem is the Organisations navigation property.

I am using code first with migrations so there is a junction table between UesrProfiles and Organisations (many to many) that is created automatically by EF. I ended up with a userprofile navigation property of Organisations and an organisation navigation property of Members. So, easy.

My problem with the organisations nav property is that it's not projecting. On the select and first I am getting the following SQL sent to the DB server.

SELECT 
    [Extent2].[ID] AS [ID], 
    [Extent2].[Description] AS [Description], 
    [Extent2].[ShortDescription] AS [ShortDescription], 
    [Extent2].[Name] AS [Name], 
    [Extent2].[Address] AS [Address], 
    [Extent2].[City] AS [City], 
    [Extent2].[State] AS [State], 
    [Extent2].[PostCode] AS [PostCode], 
    [Extent2].[Country] AS [Country], 
    [Extent2].[Created] AS [Created], 
    [Extent2].[CreatedBy] AS [CreatedBy]
    FROM  [dbo].[OrganisationMembers] AS [Extent1]
    INNER JOIN [dbo].[Organisations] AS [Extent2] ON [Extent1].[Organisation_ID] = [Extent2].[ID]
    WHERE [Extent1].[UserProfile_ID] = @EntityKeyValue1

Since I am projecting only the ID should I not get the following?

SELECT 
    [Extent2].[ID] AS [ID]
    FROM  [dbo].[OrganisationMembers] AS [Extent1]
    INNER JOIN [dbo].[Organisations] AS [Extent2] ON [Extent1].[Organisation_ID] = [Extent2].[ID]
    WHERE [Extent1].[UserProfile_ID] = @EntityKeyValue1

The joining is correct as I expect but not the columns. I guess I could create another object for the junction table and then write the code joining and selecting manually but this is stuff that I'd expect EF would do for me.

UPDATE

I am just not playing around with ordering and projections of the original user profile brought back from First(). If I change the code to this:

return _context.UserProfiles
   .Select(p => new { ID = p.ID, Organisations = p.Organisations.Select(q => new { ID = q.ID }) })
   .Single(p => p.ID == userID)
   .Organisations
   .First()
   .ID;

I end up with this monstrosity:

SELECT 
    [Project1].[ID] AS [ID], 
    [Project1].[C1] AS [C1], 
    [Project1].[Organisation_ID] AS [Organisation_ID]
    FROM ( SELECT 
        [Limit1].[ID] AS [ID], 
        [Extent2].[Organisation_ID] AS [Organisation_ID], 
        CASE WHEN ([Extent2].[Organisation_ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM   (SELECT TOP (2) [Extent1].[ID] AS [ID]
            FROM [dbo].[UserProfiles] AS [Extent1]
            WHERE [Extent1].[ID] = @p__linq__0 ) AS [Limit1]
        LEFT OUTER JOIN [dbo].[OrganisationMembers] AS [Extent2] ON [Limit1].[ID] = [Extent2].[UserProfile_ID]
    )  AS [Project1]
    ORDER BY [Project1].[ID] ASC, [Project1].[C1] ASC

But, I guess it's projecting my ids as I want.

UPDATE 2

With the answer from Gongdo, I updated to have the following:

    return _context.UserProfiles
                    .Where(p => p.ID == userID)
                    .Take(1)
                    .SelectMany(p => p.Organisations)
                    .Select(p => new { ID = p.ID })
                    .First().ID;

Which outputs:

SELECT 
    [Limit2].[Organisation_ID] AS [Organisation_ID]
    FROM ( SELECT TOP (1) 
        [Extent2].[Organisation_ID] AS [Organisation_ID]
        FROM   (SELECT TOP (1) [Extent1].[ID] AS [ID]
            FROM [dbo].[UserProfiles] AS [Extent1]
            WHERE [Extent1].[ID] = @p__linq__0 ) AS [Limit1]
        INNER JOIN [dbo].[OrganisationMembers] AS [Extent2] ON [Limit1].[ID] = [Extent2].[UserProfile_ID]
    )  AS [Limit2]

I am much closer to the output that I want. Thanks for that.

Answer 1

Single, SingleOrDefault, First, and FirstOrDefault are projection methods itself. So you need to keep IQueryable until you actually need to project it. Follow would work as you want.

return _context.UserProfiles
                .Where(p => p.ID == userID)
                .SelectMany(p => p.Organisations)
                .Select(p => new { ID = p.ID })
                .First()
                .ID;