Related data in Entity Framework projection query not being tied together

Question

My Database contains three tables: PermissionCategories, Permissions, and Users. There is a many to many relationship between Permissions and Users which is resolved by a UserPermissions table.

Using Entity Framework projection I'm trying to get all the PermissionCategories and include (eager load) the permissions filtered by userId.

My query looks like this:

var data = from pc in ctx.PermissionCategories
            select new
            {
                PermissionCategory = pc,
                Permissions = (
                from p in pc.Permissions
                where p.Users.Any(u => u.UserId == userId)
                select p
                )
            };

A sample iteration to output the results:

foreach (var item in data) 
{
    // category name
    System.Console.Writelin(item.PermissionCategory.Name);

    // DOES NOT CONTAIN THE RESULTS I EXPECT
    System.Console.Writelin(item.PermissionCategory.Permissions);

    // spacer
    System.Console.Writelin("");

    // category name
    System.Console.Writelin(item.PermissionCategory.Name);

    // DOES CONTAIN THE RESULTS I EXPECT
    System.Console.Writelin(item.Permissions);
}

It appears as though the list Permissions included inside PermissionCategory, are re-ordered, putting the expected values first. That same list then also includes all the values which should not appear in the list (Permissions not coming from the userID).

It was my understanding that the EF, using projection, should allow all of the entities to be joined in graphs as they are retrieved. In other words, assign Permissions as seen in "item.Permissions" to the associated entity "item.PermissionCategory.Permissions".

What am I doing wrong?

Examples:

I have three Permissions. Names = "A", "B", "C".

I have two PermissionCategories. Names = "TestCategory 1", "TestCategory 2"

I have two users, userID's 1 and 2.

In my model, users have permissions (a mapped Many to Many). I want my query to get all the PermissionCategories, and include all the Permissions filtered by a specific userID.

Example 1: userId 1 has permissions A and B.

The result for userID 1 should be:

   TestCategory 1
        A
        B
    TestCategory 2

The "permissions" list inside TestCategory 2 would be empty.

Example 2: userId 2 has permissions B and C

The result for userID 2 should be:

   TestCategory 1
        B
    TestCategory 2
        C

Currently, my query produces this:

For userID 1:

   TestCategory 1
        A
        B
    TestCategory 2
        C

For userID 2:

   TestCategory 1
        B
        A
    TestCategory 2
        C

Note the re-ordering of permissions between userID 1 and 2. I don't know why that happens, or why I'm not getting the expected results.

Answer 1

Your bug is here:-

 System.Console.Writelin(item.PermissionCategory.Permissions);

You are outputting ALL the persmissions for the permission category. It should be:-

 System.Console.Writelin(item.Permissions);

Which uses the projection you created with just the permissions relating to that user.