Programming Entity Framework, 2nd Edition

Chapter 5. Exploring Entity SQL in Greater Depth

Chapter 4 introduced you to a number of new querying concepts and how to express those queries with LINQ to Entities. LINQ to Entities will most likely be the more common form of querying in your applications. But there are still many scenarios where you may find that Entity SQL gives you an advantage, such as with complex dynamic query building. Outside of the MSDN documentation, you will find that the resources for learning Entity SQL are few and far between. Therefore, in this chapter, we will run through the same types of queries explored in Chapter 4 and I will demonstrate how to express them using Entity SQL. But we’ll begin with a look at some nuances for expressing Entity SQL that don’t exist with LINQ to Entities.

Literals in Entity SQL

When writing queries in LINQ to Entities, you don’t have to be too concerned about the data types that you are using for projections or filtering, but in Entity SQL there are rules about including many of the types you may want in your query. As an example, you must use special syntax with date types. Many SQL syntaxes require special handling for date types. T-SQL is very forgiving, as it simply requires that you express the date as a string.

Entity SQL, however, has specialized formatting for a number of literals. There is an MSDN topic called “Literals (Entity SQL)” (http://msdn.microsoft.com/en-us/library/bb399176.aspx) that drills into these, but unfortunately it is very easy to miss the ...