How to access related data created by EF

Question

Like a simple UserProfiles to Roles membership in a UserInRoles table

I created a relationship between UserProfiles and Clients in the UserInClients table using 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 public virtual ICollection<dbClient> Clients { get; set; } public virtual ICollection<dbClient> Clients { get; set; } public virtual ICollection<dbClient> Clients { get; set; } , and my Clients has public virtual ICollection<UserProfiles> UserProfiles { get; set; } public virtual ICollection<UserProfiles> UserProfiles { get; set; }

• If you need to see how the models tell me, I can publish them

In the model and representation I would like

• Display all clients (Distinct) and show how many users have access to this client
• Create a view that displays only clients who are allowed to view the current user.

I thought it was as simple as accessing the properties of my Clients.ClientID models, I tried things like Clients.ClientID.select (u => u.UserId == clientid), but I knew better and I know that it does not and will not work.

My other thoughts were to create a model with CliendID and UserID in it (for example, in the created table), so I can use the connection in my controller to find the correct values

---

In the end, I'm trying to include this line in 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 created by Entity Framework?

EDIT:

SOLUTION OF THE PROBLEM. Gather both answers together.

  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:

 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 converts the above into SQL calls, I had to use string concatenation, as if you tried to use a good String.Format("{0} ({1})", c.Client, c.UserProfiles.Count()) , he will complain about the inability to translate this into SQL.

Other parameters are two-pass query execution, data materialization before additional 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 allow you to pull out all the user files along with the Clients, which will allow you to use this table for filtering, counting records, etc ... that the .Where clause may need some work actually, but it should be lasting start.