CODEWITHSUNDEEP
entity-framework-core
d00d
d00d

Reputation: 763

How to use navigation property to retrieve data over two entities with a mapping table?

I have two entities, "Users" and "Systems" and a mapping table "MapUsersSystems" with UserID and SystemID. A user can have access to multiple systems, a system can be accessed by multiple users. I'm new to EF Core and not sure if my approach is correct. How do I retrieve all the systems a certain user has access to? 

Users CurrentUser = context.Users.SingleOrDefault(b => b.PkId == System.Convert.ToInt32(UserID));

List<MapUsersSystems> systems = context.MapUsersSystems.Where(b => b.FkUser == CurrentUser.PkId).ToList();

At this point, I have a list of MapUsersSystems objects that I could loop over to retrieve the Systems with the Foreign Key of MapUserSystems. But I'm not sure that is the intended way of doing this. Is there a better way to get the systems a user has access to?

Upvotes: 1

Views: 26

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109118

You were close. Just select the System property from the MapUsersSystems:

List<System> systems = 
    context.MapUsersSystems
       .Where(b => b.FkUser == CurrentUser.PkId)
       .Select(us => us.System)
       .ToList();

Side note: do yourself a favor and use singular words for class names and reference properties. Only collection properties should be plural. Makes your code far more self-explanatory.

Upvotes: 1

Related Questions