`from..where` or `FirstOrDefault` in LINQ

Question

Traditionally, when I've tried to get data for a user from a database, and I've used the following method (to some degree):

DbUsers curUser = context.DbUsers.FirstOrDefault(x => x.u_LoginName == id); string name = curUser.u_Name; string email = curUser.u_Email; 

You can see that all I want to do is get the Name and Email, but it seems to me that this LINQ query is getting everything stored in the database of that user, bringing it back, then allowing me to get what I want.

I have been doing some research and have found the following alternative:

var current = from s in context.DbUsers where s.u_LoginName == id select new {              name = s.u_Name,               email = s.u_Email            }; foreach (var user in current) {     //Stuff Here } 

Which would be better, if any at all? Is there a lighter method to use when I only want to retrieve a few results / data?

Answer 1

If you want to get only two fields, then you should project your entity before query gets executed (and in this case query gets executed when you call FirstOrDefault). Use Select operator for projection to anonymous object with required fields:

var user = context.DbUsers                   .Where(u => u.u_LoginName == id)                   .Select(u => new { u.u_Name, u.u_Email })                   .FirstOrDefault(); // query is executed here  string name = user.u_Name; // user is anonymous object string email = user.u_Email; 

That will generate SQL like:

 SELECT TOP 1 u_Name, u_Email FROM DbUsers  WHERE u_LoginName = @id 

In second case you are doing projection before query gets executed (i.e. enumeration started). That's why only required fields are loaded. But query will be slightly different (without TOP 1). Actually if you will convert second approach to lambda syntax, it will be almost same:

var query = context.DbUsers                    .Where(u => u.u_LoginName == id)                    .Select(u => new { u.u_Name, u.u_Email });   // query is defined but not executed yet foreach (var user in query) // executed now {    //Stuff Here } 

And just to show complete picture, without projection you get all fields of first found user:

DbUsers user = context.DbUsers                       .Where(u => u.u_LoginName == id)                       .FirstOrDefault(); // query is executed here  string name = user.u_Name; // user is DbUsers entity with all fields mapped string email = user.u_Email;     

In that case user entity is not projected before query is executed and you'll get all fields of user loaded from database and mapped to user entity:

 SELECT TOP 1 u_LoginName, u_Name, u_Email /* etc */ FROM DbUsers  WHERE u_LoginName = @id 

Answer 2

The second is better. You only get the needed data from database so the network traffic is lighter.

You can have the same result with extension methods:

var user = context.DbUsers                   .Where(x => x.u_LoginName == id)                   .Select(x => new {...})                   .FirstOrDefault();