How to write Entity Framework query to be able to join to the ICollection?-CodePudding

I'm trying to work out the Entity Framework syntax to return the User.Name, User.Email for the given Profile.email.

1 profile can have N categories. 1 category can have 1 User.

Profile - ID, email, Name, CreatedDate
Category - ID, ProfileId, Name, UserID
User - ID, Name, Email

In SQL I would write:

SELECT U.NAME, U.EMAIL
FROM PROFILE P
JOIN CATEGORY C ON P.ID = C.PROFILEID
JOIN USER U ON C.USERID = U.ID
WHERE P.EMAIL = '[email protected]'

Here is what I tried:

var data = await _context.Profiles
                         .AsNoTracking()
                         .Where(p => p.Categories.Users.email == '[email protected]')
                         .Select(u => new 
                                      {
                                          UName = u.Name,
                                          UEmail = u.Email
                                      }).ToListAsync();

The problem is that p.Categories is an ICollection, so I don't know how to proceed because p.Categories doesn't give me access to the .Users. I can write p.Categories.Where.... but I'm not sure how to proceed.

Instead of starting with _context.Profiles. should I be starting with _context.Users.?

Can someone help me on how to think about the approach when writing the Entity Framework query?

CodePudding user response：

If I understood your model correctly, this should work:

var data = await _context.Categories.AsNoTracking()
                     .Where(c=>c.Profile.email == "[email protected]")
                     .Select(c=>new {
                         UName=c.User.Name,
                         UEmail=c.User.Email
                     }).ToListAsync();

Ofcourse this requires your model to have navigation properties set.

CodePudding user response：

So just start your query the Categories in LINQ form:

from c in _context.Categories
where c.Profile.Email == someEmail
select new { c.User.Name, c.User.Email }

or in Lambda form:

   _context.Categories
           .Where( c => c.Profile.Email == someEmail )
           .Select( c => new {c.User.Name, c.User.Email}

or start from Profiles and use SelectMany, whose LINQ form looks like

from p in _context.Profiles
from c in p.Categories
where p.Email == someEmail
select new {c.User.Name, c.User.Email}

or in Lambda form:

_context.Profiles
        .Where(p => p.Email == someEmail)
        .SelectMany(p => p.Categories)
        .Select( c => new {c.User.Name, c.User.Email} )