8 Entity Framework Gotchas As a developer, it is no surprise to encounter unexpected behavior when working with a new technology.Microsoft added the Entity Framework (EF) to ADO.NET with the .NET 3.5 Service Pack 1 released in 2008 enabling developers to incorporate a data model directly in their application and interact with their data through the model rather than working directly against the database. For background on EF, see my previous article, “Introducing ADO.NET Entity Framework” in the Nov/Dec 2007 issue of CODE Magazine. Because ADO.NET Entity Framework approaches data access from a perspective that is new to many developers, what might be expected behavior in EF can stump developers who are new to working with the technology. I have been working with EF for over two years and spent a good part of that time writing an in-depth book on the topic (Programming Entity Framework, O’Riely). So along the way I encountered many bumps. Once I had a better understanding of EF, I was able to avoid many of these issues or at least understand them quickly when I saw an error or exception message. Being a version 1 product, there are also a number of surprises that are not “by design”. In this article, I will introduce you to some of the more painful “gotchas” that you are likely to encounter while working with Entity Framework. These are not the obvious pain points about EF that you may have heard about frequently such as lack of lazy loading or the difficulties of change tracking across tiers. Instead I will focus on some lesser known gotchas which I have experienced myself, and whose repeated occurrence in the MSDN Forums [http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/threads/] assures me that I am not alone with these surprises. For each one of the gotchas, I’ll explain the behavior, why it is occurring and how to avoid or work around it, if possible. My goal is that you will be aware and prepared for these behaviors and therefore your workflow won’t be severely interrupted by them. 1. Runtime LINQ Query Failures LINQ is an enhancement to both the Visual Basic and C# languages. LINQ provides you with strongly typed query syntax and IntelliSense to aid you in constructing queries. With LINQ to Entities, and LINQ to SQL for that matter, there is something that you need to be considerate of when constructing LINQ queries. Not everything that is valid in the eyes of the .NET Framework is equally valid in the eyes of your database engine. It is quite possible to write a LINQ to Entities query that compiles perfectly well, yet throws an exception at run time. | " |
It is quite possible to write a LINQ to Entities query that compiles perfectly well, yet throws an exception at run time.
| " |
Take, for example, this very tempting query that tries to format my return values as part of the query using a very convenient .NET method-ToShortDateString. C#
from c in context.Customers
select new {
FirstName = c.FirstName.Trim(),
LastName = c.FirstName.Trim(),
ModDate=c.ModifiedDate.ToShortDateString()
}
VB
From c In context.Customers
Select
FirstName = c.FirstName.Trim,
LastName = c.LastName.Trim,
ModDate = c.ModifiedDate.ToShortDateString
This feels right when I construct it and both the C# and VB compilers are happy. The ToShortDateString method is a huge convenience and I expect it to return nice looking results. But at runtime, the query throws a NotSupportedException when EF attempts to compile the query. The exception message is straightforward. LINQ to Entities does not recognize the method 'System.String ToShortDateString()' method, and this method cannot be translated into a store expression. Why is this? SQL Server has no method, operator or function that is comparable to ToShortDateString. Entity Framework and LINQ to SQL can only compile methods and functions that have some counterpart in the target store. In the case of Entity Framework, the store could just as easily be Firebird or Oracle as it could be SQL Server. Even if SQL Server had a comparable method, every EF provider would also need to target a database that supports this method as well. Therefore, LINQ to Entities does not support the method. But the .NET compiler has no way of knowing this at compile time because of the way Visual Studio evaluates LINQ expressions. All the compiler sees is valid .NET syntax. It is not until runtime that the process of translating the LINQ to Entities query into a store command (e.g., T-SQL or PL/SQL) is performed; and it is at that time that the problem with the query is detected. As a side note, because Entity SQL is a string-based syntax, the compilation of its queries is completely deferred until runtime. Entity SQL does, however, have the advantage of leveraging provider-specific functions and operators. There’s no way to avoid this gotcha except to have an awareness of what methods are reasonable store functions. Other than that, testing your application and all of your queries will protect you from releasing these problems into the wild. 2. Modified SSDL Gotcha The Entity Framework metadata (the Entity Data Model (EDM) schema along with the mapping and store schema) support a wide variety of customization possibilities. However, the current iteration of the EF designer tools does not support all of these features. One of the interesting features of the Store Schema Definition Language (SSDL) is that while it is a representation of the schema of your database, you can modify the SSDL to enhance your model. A change to the SSDL does not impact your database, but allows you to build logic into your model that is not part of the data store. Some of these enhancements are DefiningQueries (which allow you to inject store commands), modifying attributes of columns or even defining new parameters in stored procedures. You can solve a lot of quandaries by modifying the SSDL directly. However, there is an expensive gotcha when taking advantage of this powerful capability. Some (but not all) of these manually added enhancements to the SSDL will be overwritten when you use the Update Model Wizard. Why would this happen? The Update Model Wizard recreates any element in the SSDL which represents an object in the database. For example, if you have a Customer table in the database, the wizard’s job is to ensure that the SSDL correctly represents that table. If you have made any manual changes to the elements or attributes of that table’s definition in the SSDL, you will lose them completely as the wizard rebuilds the description of the Customer table. The wizard will not touch elements that it does not recognize. For example, if you create a DefiningQuery to define a “virtual” stored procedure, in other words a procedure that does not exist in the database, but one which you want Entity Framework to be able to execute, this DefiningQuery will remain intact when you update the model. There is nothing to overwrite it with. In all, the Update Model Wizard does not completely rewrite the SSDL, but only updates pieces of it. Those pieces that have a true counterpart in the database will be rewritten from scratch and any changes to those particular elements will be lost. I’ve suffered from this overwrite enough times to make me think twice about customizing entities in the SSDL. There is, unfortunately, no workaround for this problem. I tend to be very strict with my use of SSDL customizations and when I do insert them, I keep a separate file that I can use to copy and paste them back in if necessary. I hope that someday, some clever developer will find a way to create a tool for merging SSDL elements, perhaps something like “partial classes for XML”. |