Entity Framework - Correct way to check for single records before using them

Question

To get a LIST of records I normally do something along the lines of:

var efCompany = from a in _dbRiv.Company where a.CompanyId == companyFeedInfo.CompanyId select a;

To get a single record, when I know I am using the PK to retrieve it, I use something like:

var efCompany = (from a in _dbRiv.Company where a.CompanyId == companyFeedInfo.CompanyId select a).First();

Now, using the single record approach, if the PK is a faulty value (like it purposefully is in testing) the 2nd line throws an error.

What is the best practice way of getting a single record and dealing with it?

Answer 1

Use SingleOrDefault if you expect 0 or 1, or FirstOrDefault if you just need the first record out of potentially many, but can cope with 0. Both will return the default value for the type (usually null) if there are no results.

By the way, queries like this are generally more readable (IMO) without using a query expression, so you might have something like:

var efCompany = _dbRiv.Company
                      .Where(a => a.CompanyId == companyFeedInfo.CompanyId)
                      .SingleOrDefault();

if (efCompany != null)
{
    // Use it
}
else
{
    // Report to user, or whatever
}

Query expressions are great when you're using multiple operators, or doing relatively complex things like joins - but if you've just got a where clause or just got a projection, this "dot notation" is simpler IMO. It also works better when you need to call a method like FirstOrDefault at the end.

Answer 2

Note that both SingleOrDefault() and FirstOrDefault() will not allow you to specify the default value.

There's DefaultIfEmpty(), which allows you to specify the default value you want returned if there are no items in the enumerable. You can combine this one with First() (as in DefaultIfEmpty().First()) to achieve FirstOrDefault()-like behavior and a lambda to wrap creating a new instance and adding it to the set.

If you just need to check for the existence of a record, you can also use Any(). However, this will result in two queries, if you need to process the record if it exists.