How do I query only a single item from a database using LINQ?

Question

I would like to get a LINQ-to-SQL query that returns only one item, not a collection of them?

For example, I have a list of products with a particular name. There are no products with duplicate names in the database, so I want to be able to query and return just that instance of that product.

Products product = from p in _productContext.Products 
                   where p.name.Equals("BrownShoes") 
                   select p;

How do I do something like that?

Answer 1

You need to use .Single() or .SingleOrDefault:

Products product = (from p in _productContext.Products where p.name.Equals("BrownShoes") select p).Single();

Single() will throw an exception if there's not exactly 1 product there, SingleOrDefault() will return null

Answer 2

If you are sure you will find a product. An exception will be thrown if no product is found:

Products product = _productContext.Products
                       .Single(p => p.name.Equals("BrownShoes"));

Or, if no product is found, product will be null:

Products product = _productContext.Products
                       .SingleOrDefault(p => p.name.Equals("BrownShoes"));

Answer 3

Use Single:

Product product = _productContext.Products
                                 .Single(p => p.Name == "BrownShoes");

or

Product product = _productContext.Products
                                 .Where(p => p.Name == "BrownShoes")
                                 .Single();

There's no query expression syntax for Single, so you have to call it as a normal extension method. At that point your query is simpler written entirely with dot notation, hence the form above. You could write it as:

Product product = (from p in _productContext.Products 
                   where p => p.Name == "BrownShoes"
                   select p).Single();

But that's got a lot more fluff. If there isn't exactly a single element in the result, an exception will be thrown.

Answer 4

Products product = (from p in _productContext.Products 
                    where p.name.Equals("BrownShoes") 
                    select p).FirstOrDefault();

Answer 5

I recommend using IQueryable.SingleOrDefault(). IQueryable.SingleOrDefault() and IQueryable.Single() will throw an exception if there is more than one record, but IQueryable.Single() will also throw one if there is less than one record. This means that if you are searching for a single record and for whatever reason it doesn't exist, you have to handle an exception.

Much better is IQueryable.SingleOrDefault(), because you just check for null:

var employee = (from e in context.Employees
                where e.ID == employeeID
                select e).SingleOrDefault();

if (employee == null)
{
    // Cope with employee not found
}

// Do stuff with employee