eSqlBlast – Entity SQL Query Helper
I could not have figured out some of these more complex Entity SQL queries without the help of a tool called eSqlBlast written by Zlatko Michailov (in his free time) who is the Program Manager for Entity SQL. The tool is available on Code Gallery along with a number of other tools for Entity Framework at http://code.msdn.com/adonetefx. With eSqlBlast, you can test queries and receive immediate feedback, whether it is an error message describing a problem with the query, or the results along with the generated SQL for the database. I have used the tool in its early stages and look forward to seeing it evolve.

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, ",", FirstName).

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 I’m at.

In fact it took me a while to realize that this error

The query view specified for ‘Contacts’ EntitySet’s type(s) ‘IsTypeOf(BAModel.Contact)’ contains an unsupported expression of kind ‘Function’.

might not 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.

QueryView does support a bunch (but not all ) of the Entity SQL operators. You can do things like write CASE statements, perform UNIONS, test for ISNULL. So you can write some logic into them.

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.

SELECT VALUE BAModel.Contact(c.ContactID,c.FirstName,c.LastName,c.Title,c.AddDate,c.ModifiedDate)
FROM dbo.Contact as c
WHERE c.AddDate>="1/1/2007"

I was trying to emulate T-SQL here but I need a date, not a string. I thought that without a function I was hosed again, until I discovered Entity SQL’s Literals and rewrote the query successfully this way.

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.