Multiple queries in Entity Framework instead of one

Question

Sorry about this - I'm new to entity framework and I'm not having much luck finding an answer anywhere else.

I have 2 tables, Users and Roles, with a many to one relationship.

Using SQL to get the users role name I'd do something like this:

SELECT r.Name 
FROM Role r
INNER JOIN User u ON u.RoleID = r.ID
WHERE u.UserName = 'someusername'

So in EF I think I should be able to do something like this:

string role = context.Users.Single(u => u.UserName == username).Role.Name;

This works fine but when the code runs it executes 2 queries on the SQL server - one to get the user from the user table and another to get the role from the roles table. This obviosuly seems very heavy compared to the SQL code above so I was wondering how I could make it more efficient and execute in a single query.

Thanks

Thewads · Accepted Answer · 2014-04-29 15:26:25Z

1

Without seeing your models its a little bit tricky to tell if this is right, but you could look to do it all in 1 query like so:

string role = context.Users.Where(u => u.Username == username).Select(u => u.Role.Name).FirstOrDefault();

The code will not run any SQL until the FirstOrDefault is called.

In your scenario, by calling Single, you are executing the username check in SQL, and then the other properties are being lazy loaded in.

answered Apr 29, 2014 at 15:26

Thewads

5,06311 gold badges58 silver badges71 bronze badges

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

3 Comments

Veikedo Over a year ago

why don't use string role = context.Users.FirstOrDefault(u => u.Username == username).Select(u => u.Role.Name);

Rich Over a year ago

Thewads answer ran in a single query. I think it was the confusion with Single that was the problem. Thanks

Thewads Over a year ago

@Veikedo when you call FirstOrDefault it runs the SQL, and then will lazy load in the role name afterwards

Collectives™ on Stack Overflow

Multiple queries in Entity Framework instead of one

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related