DefiningQuery versus QueryView

  • Article

The Entity Framework is a flexible beast. So much so that it
provides not one but two ways to create views over the database.

  • DefiningQueries
    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.

  • QueryViews on
    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
always good.

Unfortunately sometimes this is not true, there is something
called the paradox of
choice.

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:

What is important

My Recommendation

Portability

QueryView 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.

 

Mixing

DefiningQuery

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.

 

Flexibility

DefiningQuery

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.

 

Simplicity

DefiningQuery

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.

Performance

Toss-up

This one is tricky, DefiningQueries by nature of being native SQL can be highly tuned and efficient.

The downside 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.

However with an Entity SQL view the framework understands the query completely and can do things like join elimination automatically.

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
techniques.

Cheers

Alex

 

*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.