How to access Related Data built By the EF

Question

Similar to the simple Membership UserProfiles to Roles in the Table UserInRoles

I have created a Relationship between UserProfiles to Clients in the table UserInClients with this code

modelBuilder.Entity<UserProfiles>()
            .HasMany<dbClient>(r => r.Clients)
            .WithMany(u => u.UserProfiles)
            .Map(m =>
            {
                m.ToTable("webpages_UsersInClients");
                m.MapLeftKey("ClientId");
                m.MapRightKey("UserId");
            });

My UserProfiles has an public virtual ICollection<dbClient> Clients { get; set; } and my Clients has an public virtual ICollection<UserProfiles> UserProfiles { get; set; }

• If you need to see the Models let me know i can post them

In the model and view i would like to

1. Display all Clients ( Distinct ) and show how many users have access to that client
2. Create a View that only shows clients a Currently logged in user is allowed to view.

I thought it was as easy as accessing the Properties of my models Clients.ClientID, i have been trying things like Clients.ClientID.select(u=>u.UserId == clientid) but i knew better and know it does not and will not work.

My other thoughts were creating a model with CliendID and UserID in it ( like the table it created ) so i can use a join in my controller to find the right values??

---

In the end what i'm trying to accomlish is to populate a KendoUI CascadingDropDownList with this line in my GetCascadeClients JsonResult

return Json(db.Clients.Select(c => new { ClientID = c.ClientID, ClientName = c.Client }), JsonRequestBehavior.AllowGet);

My question is, when I'm in my Controller, how do I access this table built by Entity Framework?

EDIT:

SOLUTION QUERY Pieced together by both answers

  return Json(db.Clients
              .Include(c => c.UserProfiles)
              .Where(c => c.UserProfiles.`Any(up => up.UserName == User.Identity.Name))`
              .Select(c => new
              {
                  ClientID = c.ClientID,
                  ClientName = c.Client,
                  UserCount = c.UserProfiles.Count()
              }),
              JsonRequestBehavior.AllowGet);

Answer 1

This is more of a LINQ question really:

db.Clients
    .Where(c => c.UserProfiles.Any(up => up.UserId == loggedInUserId))
    .Select(c => new {
        ClientId = c.ClientID,
        ClientName = c.Client + " (" + c.UserProfiles.Count() + ")"
    })

Due to the fact that it will convert the above into SQL calls, I had to use string concatenation, as if you try to use a nice String.Format("{0} ({1})", c.Client, c.UserProfiles.Count()) it will complain about being unable to translate that to SQL.

The other options is to do a 2-pass query, materializing the data before doing extra formatting:

db.Clients
    .Where(c => c.UserProfiles.Any(up => up.UserId == loggedInUserId))
    .Select(c => new {
        ClientId = c.ClientID,
        ClientName = c.Client,
        ProfileCount = c.UserProfiles.Count()
    })
    // this forces SQL to execute
    .ToList()
    // now we're working on an in-memory list
    .Select(anon => new {
        anon.ClientId,
        ClientName = String.Format("{0} ({1})", anon.ClientName, anon.ProfileCount)
    })

Answer 2

try something like:

return JSON (db.Clients
              .Include(c => c.UserProfiles)
              .Where(c => c.UserProfiles.UserId == loggedInUserId)
              .Select( c => new {
                            ClientId = c.ClientID,
                            ClientName = c.Client,
                            UserCount = c.UserProfiles.Count()}),
              JsonRequestBehavior.AllowGet);

the .Include() extension will make sure that you pull all the UserProfiles along with the Clients, allowing you to use that table for filtering, record counts, etc... that .Where clause might need some work actually, but this should be a solid start.