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.

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!