Accessing columns in cross reference table via LINQ with Entity Framework

Question

I have a Users table, a Roles table, and a cross reference table Users_Roles which has the following columns:

User_ID Role_ID Beamline_ID Facility_ID Laboratory_ID

The Beamline_ID, Facility_ID, Laboratory_ID are only filled in depending on the Role_ID. If someone has the Role_ID of 2 ("Lab Admin") then they will have an entry in Laboratory_ID.

I am trying to figure out how to get the Laboratory_ID for a specific row in this table. For example, I know I have User_ID = 1. I want to get the Laboratory_ID for User_ID = 1 and Role_ID = 2 ("Lab Admin").

This is obviously simple when dealing with SQL but I am new to Entity Framework and I am trying to do this with Entities and I'm having some trouble. I am using MVC so in my controller I have done this:

User user = new User();
user.GetUser(User.Identity.Name);
var labID = user.Users_Roles.Where(r => r.Role_ID == 2);

That should get me the "row" of that user when the Role = Lab Admin but I don't know how to grab the Labortory_ID column now. I thought maybe it would be:

var labID = user.Users_Roles.Where(r => r.Role_ID == 2).Select(l => l.Laboratory_ID);

But that is not correct. Any help would be greatly appreciated.

EDIT: I am using the database first approach and I am using DBContext. So typically I would access the context like this:

var context = new PASSEntities();

Can you give a little more information about how you load data? It should be fairly simple to write a LINQ query to find what you're looking for, but it's hard to say without seeing where your data is coming from. Are you using Code First? If so, do you have a DbContext class? — Jeremy Todd
– Jeremy Todd, Commented May 14, 2013 at 17:00
I have updated the post. Please let me know if this is not enough detail. — Steve Giordano
– Steve Giordano, Commented May 14, 2013 at 18:33

Jeremy Todd · Accepted Answer · 2013-05-14 19:21:57Z

1

As for why the code you've already tried doesn't work, at first glance I think you just need to tack on a .Single() to the end:

var labID = user.Users_Roles.Where(r => r.Role_ID == 2)
                            .Select(l => l.Laboratory_ID)
                            .Single();

Select() returns a sequence, and it looks like you want a single value, so I think that might be the cause of the problem you were having. Single() will throw an exception if there's not exactly one result, which sounds appropriate for your table structure, but there's also First() if you don't care about enforcing that.

But for this kind of thing, you might also think about just querying manually using your DbContext, instead of trying to load entities individually and then traversing down through their navigation properties -- that results in more local searching than you probably need, and, depending on the circumstances, might be performing more/larger queries against your context and database than necessary too.

Here's an example LINQ query you could use (you might have to adjust the names of your tables and so on):

 var labID = (from ur in context.Users_Roles
              where ur.User_ID == 1 && ur.Role_ID == 2
              select ur.Laboratory_ID).Single();

When you execute that statement, it will get translated into SQL equivalent to this:

SELECT TOP 1 Laboratory_ID FROM Users_Roles WHERE User_ID = 1 AND Role_ID = 2

...so it's pretty efficient. (Actually, if User_ID and Role_ID form the primary key for the Users_Roles table, and that record has already been loaded previously, I think it'll just return the cached copy, and won't need to query the database at all.)

That's a very bare-bones query, though; more than likely, you're eventually going to want to query based on properties of the user or role, instead of just searching for hard-coded IDs you know in advance. In that case, you can adjust your query to something like this:

var labID = (from u in context.Users
             join ur in context.Users_Roles on u.User_ID equals ur.User_ID
             join r in context.Roles on ur.Role_ID equals r.Role_ID
             where u.UserName == "John Smith" && r.RoleName == "Lab Admin"
             select ur.Laboratory_ID).Single();

That would, as you can probably tell, return the Lab ID for the user John Smith under the role Lab Admin.

Does that make any sense? LINQ syntax can take a little getting used to.

edited May 14, 2013 at 19:21

answered May 14, 2013 at 19:10

Jeremy Todd

3,2991 gold badge20 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steve Giordano Over a year ago

Thank you that worked! Thanks for the insight into using the DBContext in this case instead, that makes sense. Is there any benefit to write it that way instead of with lambda expressions?

Jeremy Todd Over a year ago

You mean the from u in context.Users where... syntax instead of context.Users.Where(u => ...)? They both get compiled to the same thing, so the only benefit is clarity of the code. Some queries are more readable in one format or the other. Queries with joins, for example, tend to be a lot easier to follow using the first syntax.

Collectives™ on Stack Overflow

Accessing columns in cross reference table via LINQ with Entity Framework

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related