The Entity Framework is a flexible beast. So much so that it
provides not one but two ways to create views over the database.
live in the storage schema file, or ssdl, and are written in the native SQL of
the database engine being used, i.e. T-SQL for SqlServer.
the other hand live in the mapping file, or msl, and are written in Entity SQL.
Which the Entity Framework converts* to native SQL as required.
Now surely all this choice is good right? Well maybe.
Technologists tend to think that options aka flexibility are
Unfortunately sometimes this is not true, there is something
called the paradox of
Choice is only good if you can be confident you are making
the right one. Even after you’ve made a choice, if you weren’t that confident about
it to begin with, there is always this nagging thought at the back of your mind
that you could have made a better choice, and that is a pain to be sure.
So let me try to simplify this particular choice for you:
If you want your views to work against
different database vendors you should use QueryViews. They are written in
Entity SQL, so they are portable.
If you only
want a couple of views and the rest of your model goes directly against
tables you should probably use DefiningQueries.
Why? Well today QueryView is an all or nothing affair. Once you have one
QueryView every related EntitySet and AssociationSet needs to be mapped using
QueryViews too. Typically if your model has any degree of connectiveness, as
most do, this means you need to use QueryViews in a lot places where you
would rather just go directly against a table.
A DefiningQuery works directly against the database and uses native SQL, so it can query things not mentioned in the ssdl at all.
Additionally a DefiningQuery has access to functions and features of the database without corresponding first class support in the rest of the Entity Framework.
Being native a DefiningQuery is not subject to the same sort of mapping validation that QueryViews are, so in essense you can tell little white lies, and the Entity Framework is none the wiser.
You are probably already pretty familiar with writing T-SQL or PSQL
or whatever, so it’s likely you will find that easier than writing Entity SQL.
Especially when you consider that the Entity SQL in a QueryView is unlike
most Entity SQL. It differs because it works in two spaces simultaneously, it provides mapping information after all. In a QueryView’s Entity SQL you source information
from the storage model and project or map results into the conceptual model
space. This can make things a little more confusing.
This one is
tricky, DefiningQueries by nature of being native SQL can be highly tuned and
however is that native query languages like T-SQL are essentially opaque to
the Entity Framework.
So what you say? Well imagine if you write a LINQ query
that only pulls back one column from your View: because the Entity Framework doesn’t understand
native SQL, it doesn’t understand how to optimize the request, so it has to
send a capping projection over the top of a non-optimized view request to
get the right shape of results.
This is a little like doing a SELECT * FROM request, when you only need one column. We are requesting columns and perhaps even joins that might not be needed by the final projection. Now if you are lucky these unnecessary joins and
column requests will be optimized out by the database engine,
but don’t count on it.
an Entity SQL view the framework understands the query completely and can do
thingslike join elimination
In .NET 3.5
SP1 the Entity Framework doesn’t do a lot of optimization here, but the fact
remains because QueryView are written in Entity SQL more optimization will be possible
in the future.
I hope this little cheat sheet helps you decide between these two flexible
*Strictly speaking the Entity Framework converts the Entity
SQL to a Canonical (i.e. provider agnostic) Query Tree and then asks the database
provider to convert that tree into native SQL.