There are many LINQ functions that you will need to know in order to be proficient with queries in Entity Framework.
Element operations allow you to select a single row. Sometimes they are enhanced to select null if a row that matches the target condition does not exist. Typically, you would combine element functions with a filter condition, though this is not necessary. As element functions work on sets of data, you will need to construct a query first and then apply an element function. If you are using the method syntax, you can combine both actions into a single statement. If you are using the query syntax, you would need to apply an element function to the entire query. For example, let's select a single record based on the last name of a person, as shown in the following code:
var query = from person in context.People where person.LastName == "Doe" select person; var first = query.First(); var methodQuery = context.People.Where(p => p.LastName == "Doe"); first = methodQuery.First();
In the preceding example, we used the First()
method to find a first matching row in the database. If you look at the type of the first
variable, you will see that it is of the type person
. Hence, we fetch an item from a set of persons. Here is the same example in VB.NET:
Dim query = From person In context.People Where person.LastName = "Doe" Select person Dim first = query.First() Dim methodQuery = context.People _ .Where(Function(p) p.LastName = "Doe") first = methodQuery.First()
You can easily combine the last two lines of the example into a single statement, which is much more common. The First
function has an overload that accepts an expression for the filter condition or the Where
clause as shown in the following code line:
first = context.People.First(p => p.LastName == "Doe");
This can also be represented in VB.NET as follows:
first = context.People.First(Function(p) p.LastName = "Doe")
You will notice that the LINQ query syntax is a bit more verbose in the case of element operations. Hence, most people use the method syntax, as shown in the last example, for the purposes of finding a single row in the database. This typically results in a single line of code that you have to write.
When you use the First
function and you have multiple rows in the database that match the condition, only one row will be picked up and returned. If you have no rows that match the condition, an exception will be thrown. Hence, if you want to guard against such an exception, you can use the FirstOrDefault
function instead of First
. As we deal with entities, which are classes or reference types, their default is null. So, before you use the results of the FirstOrDefault
execution, you need to test for a null value. Outside of this fact, the FirstOrDefault
code is identical to First
.
There are two more operations that are similar to First
, called Single
and SingleOrDefault
. The only difference from First
is that if you have more than one row that matches your condition, an exception is thrown. Feel free to write an example now, that is using the Single
function. LINQ has other element operators, such as Last
and ElementAt
, however, they do not make much sense in terms of Entity Framework, hence an exception will be thrown if you use them inside LINQ to entities queries. You can use them inside LINQ to objects queries. If you want to use the Last
function with Entity Framework, simply use First
and reverse the sorting order.
Occasionally, you need to check whether you have at least one row that matches a condition, or all rows match a filter. This is where the Any
and All
operations come in. Together, they are referred to as quantifiers. As the name implies, these operators return a Boolean value. Both are applied to a query, hence the code is quite similar to the preceding First
example. This similarity is shown in the code:
var hasDoes = (from person in context.People where person.LastName == "Doe" select person).Any(); hasDoes = context.People.Any(p => p.LastName == "Doe"); var allHaveJ = context.People.All(p => p.FirstName.Contains("J"));
There are a few things to notice here. First of all, we combined a query with a quantifier in a single statement to illustrate how you can cut down on the number of lines of code using the query syntax. Then, we performed the same check—whether there is at least one person with the last name of Doe
in the database using the method syntax. Finally, we checked to make sure that all people have the letter J
in their first names. Here is the same code using VB.NET:
Dim hasDoes = (From person In context.People Where person.LastName = "Doe" Select person).Any() hasDoes = context.People.Any(Function(p) p.LastName = "Doe") Dim allHaveJ = context.People.All(Function(p) p.FirstName.Contains("J"))
You can easily substitute the Any
call with a standard query with a Where
clause and check whether the result has any rows. However, you want to use Any
for such a purpose instead, because it will result in a more efficient SQL statement. Typically, it will use the EXISTS
syntax instead of the WHERE
SQL syntax, thus short-circuiting the execution upon finding the first row.
3.147.85.181