Best way for retrieving single record results in LINQ to SQL

Question

If I query a table with a condition on the key field as in:

        var user = from u in dc.Users
                   where u.UserName == usn
                   select u;

I know that I will either get zero results or one result. Should I still go ahead and retrieve the results using a for-each or is there another preferred way to handle this kind of situation.

Answer 1

Why not something like

var user = dc.Users.SingleOrDefault(u=> u.UserName==usn);

Answer 2

I would use the SingleOrDefault method.

var user = (from u in dc.Users
                   where u.UserName == usn
                   select u).SingleOrDefault();

Answer 3

Try something like this:

var user = (from u in dc.Users
                   where u.UserName == usn
                   select u).FirstOrDefault();

The FirstOrDefault method returns the first element of a sequence that satisfies a specified condition or a default value if no such element is found.

Answer 4

I would use First() or FirstOrDefault().

The difference: on First() there will be an exception thrown if no row can be found.

Answer 5

Also it should be noted that First/FirstOrDefault/Single/SingleOrDefault are the point of execution for a LINQ to Sql command. Since the LINQ statement has not been executed before that, it is able to affect the SQL generated (e.g., It can add a TOP 1 to the sql command)

Answer 6

too bad, it does not work this way in SQL 2000. The problem is that link generates the following sql :

select top (1) ...

In sql 2000 is only accepted :

select top 1 ...

So what is the best method ?