I have been learning a lot about EDM QueryViews lately, though
more of what I'm learning (the hard way) is what you can't do with QueryViews.
Most of what you'll find documented about QueryViews is to replace
mappings so that your resulting entity is read only. But there are more uses for QueryViews.
THe problem is that I"m having a very hard time discovering what those are and therefore
it's trial and error (and some help from Srikanth on the EF team who seemed to get
stuck with Holiday Weekend forum duty).
It started with an old hope of mine to be able to embed a FullName
property directly into the model. I thought I could do this with QueryViews by adding
a new property to the Entity called FullName and then using EntitySQL string concatenation
syntax to combine Trim(LastName) + ", " + FirstName or at the least Concat(LastName,
After working out the various validation errors and then weeding
out what causes them (Trim, Contact and +) I have come to the conclusion that
you just can't use any functions at all. I could be wrong, but so far that's where
In fact it took me a while to realize that this error
query view specified for 'Contacts' EntitySet's type(s) 'IsTypeOf(BAModel.Contact)'
contains an unsupported expression of kind 'Function'.
be telling me that the Trim function wasn't supported but that no Functions are supported
I still can't tell, but expect to get some confirmation one way or another (and it
*is* a holiday weekend in the U.S. so I'll be patient).
I'm still waiting
for confirmation of my conclusion, but after going around in circles for days, I wonder
if I've finally hit the nail on the head.
I also sent an
email to the doc team to please include more details about writing QueryViews.
support a bunch (but not all ) of the EntitySQL operators. You can do things like
write CASE statements, perform UNIONS, test for ISNULL. So you can write some logic
It also supports
operators that can't be used in Conditional Mapping. WIth Conditional Mapping you
can only test for = or IS NULL or IS NOT NULL. By using a QueryView you can add
int greater than and less than, BUT - on what?
I thought - ah
okay, I'll try a condition on a date field.
FROM dbo.Contact as
I was trying
to emulate T-SQL here but I
need a date, not a string. I thought that without a functino I was hosed again, until
I discovered ENtitySQL's Literals and
rewrote the query successfully this way.
SELECT VALUE BAModel.Contact(c.ContactID,c.FirstName,c.LastName,c.Title,c.AddDate,c.ModifiedDate)
FROM dbo.Contact as c
WHERE c.AddDate>= DATETIME'2007-01-1 00:00'
So this gives
me two benefits over Conditional Mapping. Not just the greater than operator, but
testing against literals. Conditional mappings with "=" can only be used with strings
and integers as strings (which therefore also covers booleans). However I haven't
pushed the envelope on those, so maybe there's a way after all to use a Date in a
literal as well.
At the same time,
I'm still working out when I would use a Defining Query over a QueryView.
One obvious scenario would be when I can't even express the query with a QueryView.
With a DefiningQuery,
you can write native queries. Above, I'm close to doing that anyway, since this EntitySQL
is written against the store layer not against the CSDL layer. So I can just back
down one step further and write T-SQL queries inside the model which will return (read
only, but updatable via function mappings) entities for me.
Not to say that
QueryView is as limited as I'm discovering. We just need more code samples of when
to use them besides the scenario of creating a read-only entity.