How can I query for only one item from the database using LINQ?

Question

I would like to receive 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 specific name. There are no products with duplicate names in the database, so I want to be able to request and return exactly this instance of this product.

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

How do I do something like this?

Answer 1

Use Single :

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

or

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

There is no syntax for the query expression for Single, so you should call it as a regular extension method. At this point, your request is easier to write in full with a dotted record, therefore, the form above. You could write this as:

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

But it got a lot more fluff. If there are no items as a result, an exception will be thrown.

Answer 2

I recommend using IQueryable.SingleOrDefault() . IQueryable.SingleOrDefault() and IQueryable.Single() will throw an exception if there are more than one record, but IQueryable.Single() also throw one if there is less than one record. This means that if you are looking for one record and for some reason it does not exist, you need to handle the exception.

Much better is IQueryable.SingleOrDefault (), because you are just checking 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 

Answer 3

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 is not exactly 1 product, SingleOrDefault() will return null if it does not exist, or will throw an exception if there are several.

Answer 4

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

Answer 5

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

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

Or, if the product is not found, the product will be null:

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