MVC 4 explicitly load a many to many EF lookup and filter by both keys

Question

I have the following in an MVC4 app. I want to filter the load by both keys, there could be many to many but both the keys together represent a unique row. The goal to to explicitly load these collections only when needed and filter by both sides of the relationship.

I have the following Entity.DBContext, it works but only for the UserId key.

context.UserProfiles.Include(o => o.CoachOfOrginizations).Where(p => p.UserId == UserId).Single()

Also this loads all of them, but of course doesnt filter at all, just showing you so you know that I set up my fluent code properly.

    context.Entry(this).Collection(t => t.AdministratorOfOrginizations).Load();

modelBuilder.Entity<UserProfile>()
                .HasMany(p => p.CoachOfOrginizations)
                .WithMany(t => t.Coaches)
                .Map(mc =>
                {
                    mc.ToTable("OrganizationCoaches");
                    mc.MapLeftKey("UserProfileID");
                    mc.MapRightKey("OrganizationID");
                });

CoachOfOrginizations is the following property on UserProfile.

public ICollection<Organization> CoachOfOrginizations { get; set; }

What i would like is to filter my original .Include above based on Organization.ID (the other key) and the currently used key p.UserId. I've tried the following and it doesn't work, any advice? "this" is the UserProfile object.

context.Entry(this)
                .Collection(b => b.CoachOfOrginizations)
                .Query()
                .Where(p => p.ID == orgID)
                .Load();

score 1 · Accepted Answer · answered Sep 22 '12 at 01:36

1

You can do both filtering in a single query. See conditional includes.

var query = context.UserProfiles
             .Where(p => p.UserId == UserId)
             .Select(p => new { p, Orgs = p.CoachOfOrginizations.Where(o => o.ID == orgID) });

var profiles = query.AsEnumerable().Select(a => a.p);

answered Sep 22 '12 at 01:36

Eranga

32,181
5
97
96

Great, that works, now I just need to move this over to the Organization object because really what I need is a collection of those objects, but the code works either way! Thanks. – Marc Sep 22 '12 at 04:34
I a gave you the answer to quickly, I had to do the following in order to get it to work...I need the end result to be an ICollection...which seems inefficient with all the .ToList(). var coaches = queryCoaches.AsEnumerable().Select(a => a.Orgs).ToList(); CoachOfOrginizations = coaches[0].ToList(); – Marc Sep 24 '12 at 18:02

MVC 4 explicitly load a many to many EF lookup and filter by both keys

1 Answers1