`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?

The second is the way to go if you do not want ALL the data collected, but just a few fields. Fire up a SQL server profiler and check the difference in the querries — Mario Stoilov, Jan 17 '14 at 13:23
And don't forget to throw a little `Any()` security check before doing anything ^^ — Ethenyl, Jan 17 '14 at 16:40
i've seen people sprinkle "any" queries before every actual query to make sure that they will be getting a result ... this is a terrible idea, it creates unnecessary round-trips to the database ... "FirstOrDefault()" handles the no-data case perfectly well in one round-trip to the database — TCC, Jan 17 '14 at 16:55
@NewAmbition, SingleOrDefault throws an exception if there is more than one result. It determines this by querying the top 2 results and throwing an exception if two are actually retrieved. If one is retrieved, it is returned. If none are retrieved, the object default is returned. — Aaron Palmer, Jan 20 '14 at 06:22
I use both when appropriate. In this case without knowing all of the details I think I would use `SingleOrDefault`. There is a third option , `Take(2)`, which I sometimes use. `SingleOrDefault` lets you know if the query would return exactly one record or not, when the method returns default you are left to wonder "so were there 0, or more than 1 records". If the database has a unique constraint, then you know the answer must be 0. Otherwise `Take(2)` is an efficient way to determine if a query matches 0, 1, or more than 1 records (this is what `SingleOrDefault` does behind the scenes anyway). — TCC, Jan 20 '14 at 17:32
Would `Take(2)` not result in more bandwidth / processing time? — TheGeekZn, Jan 21 '14 at 05:45
@Ethenyl there's no _security_ in that. The code will blow up if it's not there (and none are found). But then again you might have meant robustness — Rune FS, Jan 22 '14 at 06:35
I'm assuming that if the query returns two or more users that would constitute an error. If that's correct you might want to consider `SingleOrDefault` instead. THis will enforce that. Yes you'd then need to handle an exception but since it is then an exceptional case that's a good thing. If the assumption is correct you are hidding an error instead of handling the error, not so good and makes for loong debuggin nights when the bug surfaces — Rune FS, Jan 22 '14 at 20:15
Hrm I have to correct my previous comment... `SingleOrDefault` throws if there are more than one result. My larger point remains that there are clear times when FirstOrDefault, SingleOrDefault and something like Take(2) will get you what you want, and any of these is one round trip to the database, and is far better than calling `.Any()` before executing your query and getting results. If you are unclear when to use each of these, do a lot of testing until you understand their behavior. not to be rude but this is fundamental behavior you really must understand, so test until you understand :) — TCC, Jan 24 '14 at 00:11
@RuneFS to add to your point, `SingleOrDefault` is especially the way to go if there is a database unique constraint guaranteeing only one user would be returned. In that case you might not even put a local exception handler, but let it bubble up, as it would represent a database failure (really bad) and surely your app isn't designed to work well in the case that all of your unique constraints are failing to function properly :) ... at least I don't protect against those types of errors myself, maybe i should — TCC, Jan 24 '14 at 00:13

score 67 · Accepted Answer · edited Jan 17 '14 at 16:36

67

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

edited Jan 17 '14 at 16:36

Smi

13,850
9
56
64

answered Jan 17 '14 at 13:22

Sergey Berezovskiy

232,247
41
429
459

4

It all of a sudden seems so obvious. – TheGeekZn Jan 17 '14 at 13:26
3

_"If you want to get only two fields, then you should project your entity before query gets executed "_ Does it really matter if i select two properties of an anonymous type or a fully initialized object? The latter has the advantage that i can use it everywhere where i need `DbUsers` objects. A table should not contain hundred columns anyway. Also, you're normally not selecting millions of users but few (or only one). – Tim Schmelter Jan 17 '14 at 13:33
4

It also worth mentioning that you can project the data into a separate type aswell. It does not have to be a anonymous type => `new MyType { Username = u.u_Name }` – scheien Jan 17 '14 at 13:44
@TimSchmelter I agree that usually you just select whole object and does not think which fields you need. But sometimes you should work with legacy database, or denormalized tables (ouch!) and loading of hundred columns becomes real problem. – Sergey Berezovskiy Jan 17 '14 at 13:50
@scheien: You could, but this has the disadvantage that you have half-initialized objects which could cause problems somewhere. – Tim Schmelter Jan 17 '14 at 14:00
@TimSchmelter - Sure, if you do not account for that situation :) – scheien Jan 17 '14 at 14:01
@scheien: Maybe you or your fellows trust that database objects follow the same rules as in the database. So if you have a non-nullable column somebody could think that the property can also never be null. – Tim Schmelter Jan 17 '14 at 14:04
Enumerate all columns that you need, and select just them from database (for sake of standardization of code, explicit field, etc..) Why would you bring info from database, consuming **bandwidth**, which you won't need? – Andre Figueiredo Jan 17 '14 at 15:59
1

In the sql-like syntax, you can still get the single-result as well, by combining with the extension methods: `(from s in ... select new {...}).FirstOrDefault();` – Brian S Jan 17 '14 at 16:33
@BrianS I honestly did not know you could do that! Thanks! – TheGeekZn Jan 20 '14 at 05:07
@TimSchmelter you can use an object of an anonymous time anywhere you like with in the same assembly too. Yes you need and extention method for the cast to accomplish that ` T Cast(T pattern, object obj){ return (T)obj;}` If you provide an anonymously typed object with the same structure as the projection then it'll work just like a cast to a named type. However since anonymous types have assmebly scope this is internal only – Rune FS Jan 22 '14 at 20:12

score 11 · Answer 2 · edited Jan 17 '14 at 13:35

11

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();

edited Jan 17 '14 at 13:35

Ondrej Janacek

12,486
14
59
93

answered Jan 17 '14 at 13:23

Cosmin Vană

1,562
12
28

score 2 · Answer 3 · answered Jan 17 '14 at 13:25

If you need not whole entity, but some values from it, then use new {name = s.u_Name, email = s.u_Email}. Because, this object is much "lighter" for cunstruction. When you get entity with FirstOrDefault, it' saved in DBContext, but you don't do anything with it. So, i advice you to get only data you need.

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

3 Answers3

Linked