Database Reference
In-Depth Information
How It Works
We start off the code in Listing 3-10 by deleting prior test data and inserting new blog posts and comments into the
database. We left one of the blog posts without any comments to make sure our query performs correctly.
In the LINQ query, we leverage the LINQ Extension Method
Any()
in the
where
clause to determine whether
there are comments for a given post. The query finds all of the posts for which the
Any()
method returns
true
. In
this usage, we iterate through each blog post with
Any()
returning true if there are comments for the specific post.
Moreover, that's just what we want: all of the posts for which there is at least one comment.
For the Entity SQL approach, we use the SQL
exists()
operator, again in a
where
clause, to determine whether
the given post has at least one comment.
Of course there are other ways to get the same result. For example, we could use the
Count()
method in the
LINQ query's
where
clause and test to see if the count is greater than 0. For the Entity SQL approach, we could use
count(select value 1 from p.Comments) > 0
in the
where
clause. Either one of these approaches would work.
However, the code in Listing 3-10 seems a bit cleaner and, from a performance perspective, the semantics behind
Any()
and
exists()
don't require the enumeration of the entire collection on the server (meaning that, after finding
the first comment for a blog entry, the process moves onto to the next blog entry), whereas
count()
does require
a full enumeration on the server (meaning that, each comment will be enumerated, despite the fact that one was
already found).
3-6. Setting Default Values in a Query
Problem
You have a use case for which you must assign a default value to a property when the query returns a null value. In our
recipe, we'll assign a value of '0' to the Years Worked property when a null value for it is returned from the database.
Solution
Let's say that you have a model like the one shown in Figure
3-7
. You want to query the model for employees.
In the database, the table representing employees contains a nullable YearsWorked column. This is the column
mapped to the YearsWorked property in the Employee entity. You want the rows that contain a null value for the
YearsWorked to default to the value 0.
Figure 3-7.
A model with an Employee entity type containing an EmployeeId property, a Name property,
and a YearsWorked property
To start, this example leverages the Code-First approach for Entity Framework. In Listing 3-11, we create the
Student entity class.
