Databases Reference
In-Depth Information
table to retrieve entity of any given type. However, if you query instances of a base type,
such as User in the previous example, the Entity Framework has to perform a SQL union
of all tables to which the derived entity types are mapped.
With the
table-per-hierarchy
approach, all entity types derived from a common base are
stored in a single table, with one or more columns serving as discriminators that identify
the entity type stored in each particular row. This approach results in having columns
required to store all properties of all types in a hierarchy in a single table, which could
easily become quite wide as the number of entity types increases.
Each of these approaches has unique trade-offs that can have a significant effect on perfor-
mance and data storage requirements of your application. Assess them carefully before
making a decision. See Chapter 9, “Implementing Business Logic,” for a closer look at this
advanced topic.
Although LINQ to Entities is a very powerful mechanism for creating database queries,
you might find yourself in a situation where it is not enough and use of database stored
procedures is desirable. The most common reasons for using stored procedures in database
applications are performance optimization and security. Both of these reasons are still
applicable but not as relevant as in classic ADO.NET applications because Entity
Framework generates well-performing and secure SQL code out of the box. When optimiz-
ing performance, your first choice should be to simplify and speed-up the LINQ queries.
Making sure that LINQ queries take advantage of indexes, return a minimal number of
rows, and use projections to limit the size of result sets goes a long way to improve appli-
cation performance. Although stored procedures are fully supported by the Entity
Framework, they require additional effort to implement and maintain, so you should
resort to using them for improved performance only after exhausting the LINQ optimiza-
tion options.
Consider the following LINQ query that returns products that need to be re-ordered:
var query = from p in context.Products
where p.UnitsInStock < p.ReorderLevel
select p;
Similar logic can be implemented using T-SQL in the following stored procedure:
CREATE PROCEDURE dbo.GetProductsToReorder
AS
SELECT * FROM dbo.Products
WHERE UnitsInStock < ReorderLevel;
