Querying Literals in Entity SQL

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!  

4 thoughts on “Querying Literals in Entity SQL

  1. Hi Julie. What does the Entity SQL team claim is the justification for introducing these literals? Why is it necessary to introduce complexity where it isn’t apparent that it is justified?

  2. @TA

    You’ll have to ask someone on the EF team. Either ask in the MSDN Forums for EF or ask in a comment of a relevant post on their blog (blogs.msdn.com/adonet). If you do, please leave a URL here so anyone (including me) who is intersted cvan follow up.

    Thanks!

  3. Thanks for the post. I was testing my first OData Service and couldn’t figure out why I got a "syntax error" message when I queried an entity with a decimal key. Your post answered my predicament. I Added a capital ‘M’ after the number, and presto, problem solved.

    Thank you!

Leave a Reply to Muhammad Mosa Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.