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

Display all Clients ( Distinct ) and show how many users have access to that client
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);

score 2 · Answer 1 · answered Aug 17 '13 at 03:10

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.

answered Aug 17 '13 at 03:10

Claies

22,124
4
53
77

Include is only needed if you return an entity and want to avoid lazy-load of properties (e.g. return `Client = c` and want to later access the UserProfiles property). If you do a projection as you have, then that already forces UserProfiles to be part of the query. – Timothy Walters Aug 19 '13 at 00:51
Great, minus 1 thing, could you explain the logic to me of how it knows to compare the 2 table with the UserInClients Table? - also still working on the Linq still looks like its not quite liking the .UserID on UserProfiles. – Don Thomas Boyle Aug 19 '13 at 12:32
Its not Liking .UserID because in my Model for Clients it only has the `public virtual ICollection UserProfiles { get; set; }` for UserProfiles wich as far as I have been aware doesnt actually let me go into the UserProfiles table with another `.` The furthest I have been able to go "With Intellisence" is `Clients.UserProfiles` ( as a collection i guess ) it won't let me use `.UserID` ontop of that. – Don Thomas Boyle Aug 19 '13 at 12:34
UserProfiles is populated by joining your UserInClients and UserProfiles tables, and joining the result to Client. See my answer for how you query this type of connection. – Timothy Walters Aug 20 '13 at 05:28
Ok, unsure why and would still ike to know how to ( Insert records ) but i with both your codes I have created a query that works thanks so much to the both of you! – Don Thomas Boyle Aug 20 '13 at 14:47

score 1 · Accepted Answer · answered Aug 19 '13 at 00:49

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)
    })

answered Aug 19 '13 at 00:49

Timothy Walters

16,866
2
41
49

You realize the table I'm trying to access isn't in my context, exept for how i tell it to build it, nor exists in my Models. The table that holds the only connection between Client And UserProfiles is UserInClients that EF created that has but 2 Columns, USERID and CLIENTID, and very confused on how your LINQ should work / can work – Don Thomas Boyle Aug 19 '13 at 12:26
1

Many-to-many tables work that way, it is like magic. What it will do is query where the join exists. Get LINQPad and it can show you the SQL that is generated, and allow you to test your LINQ more easily. – Timothy Walters Aug 20 '13 at 05:21
So i downloaded LinqPad as recomended, how does one re-write the above to match it / and how would i use that same Linq to Insert a Record in both tables? -- if this extends beyond the scope of the question let me know. Beyond that I'm still attempting to implement your solution atm. – Don Thomas Boyle Aug 20 '13 at 14:25
Ok, unsure why and would still ike to know how to ( Insert records ) but i with both your codes I have created a query that works thanks so much to the both of you! – Don Thomas Boyle Aug 20 '13 at 14:46
1

As far as inserting, `client.UserProfiles.Add(userProfile)` will work assuming `client` and `userProfile` are existing records. Same works for `userProfile.Clients.Add(client)`. Add it once, relationship is created that goes both ways. Under the covers it is creating a UserInClients record with the ID of the Client and the UserProfile, regardless of which way you add it. – Timothy Walters Aug 20 '13 at 23:19
1

For LINQPad, you could just copy/paste either of my above code snippets once you have linked to your EF class (that way you can access your existing model), just drop the `db.` prefix as it sets the scope to be an instance of the data context. If you want to test multiple statements, switch to statements mode and add `.Dump();` to the end of anything to dump the output. – Timothy Walters Aug 20 '13 at 23:23

How to access Related Data built By the EF

2 Answers2

Linked