Database Reference
In-Depth Information
Following is the output of the code in Listing 3-7:
Students...
Robert Smith is working on a Masters degree
Julia Kerns is working on a Masters degree
How It Works
In Listing 3-7, we add three Students to the DbContext and save them to the database using
SaveChanges()
.
To retrieve the Students who are working on a master's degree, we use the
SqlQuery()
method with a
parameterized SQL statement and a parameter set to “Masters.” We iterate through the returned collection of Students
and print each of them. Note that the associated context object implements change tracking for these values.
Here we use
*
in place of explicitly naming each column in the select statement. This works because the columns
in the underlying table match the properties in the Student entity type. Entity Framework will match the returned
values to the appropriate properties. This works fine in most cases, but if fewer columns returned from your query,
Entity Framework will throw an exception during the materialization of the object. A much better approach and best
practice is to enumerate the columns explicitly (that is, specify each column name) in your SQL statement.
If your SQL statement returns more columns than required to materialize the entity (that is, more column values
than properties in the underlying entity object), Entity Framework will happily ignore the additional columns. If you
think about this for a moment, you'll realize that this isn't a desirable behavior. Again, consider explicitly enumerating
the expected columns in your SQL statement to ensure they match your entity type.
There are some restrictions around the
SqlQuery()
method. If you are using Table per Hierarchy inheritance and
your SQL statement returns rows that could map to different derived types, Entity Framework will not be able to use
the discriminator column to map the rows to the correct derived types. You will likely get a runtime exception because
some rows don't contain the values required for the type being materialized.
Interestingly, you can use
SqlQuery()
to materialize objects that are not entities at all. For example, we could
create a
StudentName
class that contains just first and last names of a student. If our SQL statement returned just these
two strings, we could use
SqlQuery<StudentName>()
along with our SQL statement to fetch a collection of instances of
StudentName
.
We've been careful to use the phrase SQL statement rather than select statement because the
SqlQuery()
method works with any SQL statement that returns a row set. This includes, of course, Select statements, but it can
also include statements that execute stored procedures.
3-4. Querying a Model with Entity SQL
Problem
You want to execute an Entity SQL statement that queries your underlying entity data model and returns
strongly-typed objects.
Solution
Let's say that you have a model like the one shown in Figure
3-5
, which contains a single Customer entity type.
The Customer entity type has a Name and an Email property. You want to query this model using Entity SQL.
