Entity SQL might surprise you if you are building query expressions with some non-string types.

I’ve blogged about this before with the DateTime literal after trying to use a string to represent the date in my query as I’m used to with TSQL. Here is a snip from that post:

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.

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′

I’ve had  number of emails recently with people using Entity SQL and having problems similar to this, most commonly with decimals & doubles (e.g., 123.24). First I will point you to the MSDN documentation page on Literals for Entity SQL.

The clues to expressing the queries are in this doc, however they are REALLY easy to miss.

Let me highlight a few of these with some emphasis on the specific clues:

Integer

Integer literals can be of type Int32 or Int64. An Int32 literal is a series of numeric characters. An Int64 literal is series of numeric characters followed by an uppercase L.

Decimal

A fixed-point number (decimal) is a series of numeric characters, a dot (.) and another series of numeric characters followed by an uppercase "M".

Float, Double

A double-precision floating point number is a series of numeric characters, a dot (.) and another series of numeric characters possibly followed by an exponent. A single-precisions floating point number (or float) is a double-precision floating point number syntax followed by the lowercase f.

Notice those “followed by” notes now? 🙂

So, this ESQL expression will fail:

select p.amount from BAEntities.Payments as p where p.amount=125.25

The exception message is “The argument types ‘Edm.Decimal’ and ‘Edm.Double’ are incompatible for this operation. Near WHERE predicate, line 1, column 61.”

The correct way to express this query is to be sure the decimal is followed by an uppercase "M".

select p.amount from BAEntities.Payments as p where p.amount=125.25M

That query works like a charm.

I’ve been using a lot of NoTracking queries to grab lists of data that I don’t need change tracked. It enhances performance and cuts down on resources. There are some nuances about these entities, however.

One of the interesting behaviors of EF4’s Lazy Loading is that even if you have entities that you have queried with NoTracking on, they will still lazy load related entities.

Unless you’ve read this somewhere (it’s on the ADO.NET team’s blog post which introduces lazy loading) or experienced it yourself (that’s me, since I had forgotten about that point in the blog post) you may not expect, or plan ahead, for this behavior.

You’d think that because the entity is not attached  to the context, it would not be able to pull this off. But it does. The related entity or collection will get automatically retrieved and the results are also detached and deep inside of the EntityObject sealed properties, there’s a flag that indicates they are NoTracking entities.

Another surprise is in store when you use NoTracking entities…again it’s only a surprise if you don’t happen to be aware of this behavior and it’s not what you might expect.

Normally when you join a detached entity to an attached entity, EF’s relationship span will kick in and automatically pull that detached entity into the context to be managed. However, this won’t happen with NoTracking entities and you’ll receive a very explicit exception message that explains this. You need to attach the “NoTracking” entity to the context first and then you can attach it to the other entity.

When I have done “what’s new in EF4” talks at user groups and conferences, I like to show off the new pluralization support in the EDM Wizard. I also like to have a little fun showing some cases where it doesn’t do so well. For example, it correctly singularized Breweries to Brewery, but uses the same rule on Movies, turning it to Movy.

The wizard uses a runtime feature referred to as the Pluralization Service which you can code against yourself.

Ken Cox recently built a WCF Service that uses the pluralization service to provide his own cloud plurlization service. And then ran a bunch of words against it. This is a lot easier than testing it in the designer! Here’s his blog post about the service: EF 4’s PluralizationService Class: A Singularly Impossible Plurality

A warning, though. Do not try to say the title of his blog post out loud or you may not be able to untwist your tongue until EF5 is released!

Ayende has reminded us that Entity Framework Profiler’s Beta period ends tomorrow when it goes RTM. But that means the 30% discount also ends. It will go from $220US to $315US tomorrow.

I spend a lot of time looking at what’s going on in my database when using EF and the views that EFProf provide are indispensible. It’s organized by context instance and provides links back to the .NET code that executed each command.

I sure wish I had stock in this tool!