I once wrote this, but then never posted it. Thought it could be a good article – but never polished it enough. So here it is; untouched from somewhere in 2009, hence outdated?; still too good content to keep it for myself…
Code is available here: lcorneliussen/Discovering-Entity-Framework
LINQ to Entities is great for demos
Ever since the ADO.NET Entity Framework 1.0 released as a part of .NET 3.5 SP1 in August 2008 it has gotten a lot of bad press. This article is not about bad press. It’s about real world project experiences, where we hit walls almost everywhere we tried to go with Entity Framework. Our Scenario is maybe not the most common one, but exactly that’s where EF proves that it’s not mature enough for production.
You know those scenarios where you have to implement real world requirements? Those, where telling your customer that you can’t implement a certain requirement because your framework doesn’t support it, would barely help?
Well, than we are on the same page!
Scenario
Let me introduce you to our scenario. We have a set of plug-ins organized by categories. Every plug-in manages it’s own tables, but the framework requires some certain tables and columns per category. The data is managed by some legacy DAOs.
Let’s say, just to prevent being fired, we have a plugin-category “People” and two plug-ins named “Singles” and “Spouses” managing some people together their cars.
And another category
Now, as in every real world product, we need to easily query this data without having to care about all the different tables. So we decided to dynamically create views on top of each of the different plug-in-tables that fake a consistent relational model per Plug-in-Category:
This, as we thought, could be easily mapped to a object structure letting a O/R-mapper do all the complex joining and sub querying over the admittedly much larger production model.
Choosing a Product
My customer, as many customers are, was afraid of using something that does not come from Redmond. Since this scenario is so simple I decided to give both EF and LINQ2SQL a chance.
Relationships (Many-to-Many) were easier to model in EF, so I went for it.
After overcoming some initial problems, as were
- While generating the Entities for my views it turned out db-views support in EF is quite poor. It just sets all non-null-fields as compound entity keys. After changing it to something more natural, you can’t update your model from the database anymore. This makes changes quite expensive in this case.
- Mapping one-to-many in the designer feels quite unnatural.
… finally I got it working. And it demos very well!! But that is basically what it does. It demos well. Not more.
Almost every real task I had to solve using EF was a nightmare! First of all let me tell about the difference between LINQ, LINQ-to-SQL, EF, LINQ to Entities and EF Object Query. If you know all about these, just skip it.
LINQ
Linq is a set of interfaces and extension methods, a C#/VB.NET language feature plus a runtime that enables Language-INtegrated Querying against any structured data source. See here for a list of LINQ implementations, and here. If you want to know more on the background, read this.
## LINQ example (on IQueryable and IEnumerable) ##
LINQ to SQL
LINQ to SQL is way to often just refereed to as LINQ. That is simply wrong. LINQ is neither the same as LINQ to SQL, nor is it any O/R-mapper implementation.
Instead, as Microsoft released the language feature along C# 3.0 and the .NET Framework 3.5, they also delivered some implementation for LINQ as LINQ to XML, LINQ to Objects, LINQ-to-SQL and LINQ to DataSet.
EF (Entity Framework)
In addition, Microsoft released another O/R-mapper called ADO.NET Entity Framework. Using EF, the user can choose between querying via the embedded LINQ support called LINQ to Entities, or using Object Queries.
LINQ to Entities
LINQ to Entities simply is a implementation for querying Entity Framework using LINQ-syntax. This gives you nice features as type safety, selecting subsets using anonymous types, and so on.
var query = from p in model.AllPeople
where p.Age > 30
select p.Name;
foreach (var name in query)
{
Console.WriteLine(name);
}
/* Results
* Michael Jendryschik (A)
* Michael Kloss (B)
*/
EF Object Query
Object Queries are similar to SQL, but operates on the conceptual object model. It does not offer type safety, but enables much more sophisticated queries.
Within Object Queries you can either use the Query Builder Methods:
var query = model.AllPeople
.Where("it.Age > @age", new ObjectParameter("age", 30))
.SelectValue<string>("it.Name");
… or go for manual queries using Entity SQL:
var query = model.CreateQuery<string>(
"SELECT VALUE p.Name FROM AllPeople AS p WHERE p.Age > @age",
new ObjectParameter("age", 30));
All these queries result into the same query:
SELECT
[Extent1].[Name] AS [Name]
FROM (SELECT
[AllPeople].[Id] AS [Id],
[AllPeople].[Name] AS [Name],
[AllPeople].[Age] AS [Age]
FROM [dbo].[AllPeople] AS [AllPeople]) AS [Extent1]
WHERE [Extent1].[Age] > 30
So what to choose? Choice necessary?
Problems with LINQ to Entities and/or Object Query
The problem is, that LINQ and ObjectQuery operate on the same objects, but still have a separate architecture. Sometimes it is mixable, sometimes it’s not.
ObjectQuery<T> and IQueryable<T>
Since ObjectQuery<T> implements IQueryable (which is default for LINQ queries) you get all Query Builder Methods plus all default LINQ extension methods for IQueryable.
As soon as you execute one of the many nice Extension Methods you’re locked into LINQ to Entities which won’t let you specify any ObjectQuery-specific Expressions on top anymore. But you can start with Object Query and then use LINQ-queries against ObjectQueries.
So what? Then let’s just use LINQ to Entities, right?
Lets sum up the good news about LINQ to Entities first:
- Nice syntax for those who are used to it
- Type safety !!!
- Very easy to join relationships (OK in Object Query)
- Simple sub-queries using Any(…)
- Very easy to use with anonymous types
(which are not really good supported in ObjectQuery)
Well, the problem is, that you can get very, very far with LINQ to Entities and then suddenly you’ll hit the wall. It has some pretty major limitations that in my opinion make it unsuitable for production use:
- No support for the LIKE-operator !!!!
string.Contains() does map to the LIKE-operator in the database, but instead to CHARINDEX-function (function mappings) - No support for string.StartsWith() and string.EndsWith()
- Bad support for dynamic/parameterized queries (Dynamic LINQ helps here)
- Some LINQ-methods/operators are not supported
Back to some queries
Let me demonstrate those problems using some simple queries.
“List all people with their cars, ordered by the model name”
from p in model.AllPeople
from c in p.Cars
orderby c.Model
select new { p.Name, c.Model };
/* Results
* { Name = Michael Kloss (B), Model = Mercedes E350 CDI Coupé }
* { Name = Lars Corneliussen (B), Model = Skoda Oktavia }
* { Name = Lars Corneliussen (B), Model = VW Fox }
* { Name = Michael Jendryschik (A), Model = VW Golf 5 }
* { Name = Michael Kloss (B), Model = Vw Golf 6 }
*/
Very, very nice! And very simple!
LINQ to Entities does automatically join each Person p with it’s cars c in p.Cars. In the further restrictions, ordering or the select-statement you have access to a joined pair of a Person p and a Car c.
This results in following SQL select statement:
SELECT
[Project1].[C1] AS [C1],
[Project1].[Name] AS [Name],
[Project1].[Model] AS [Model]
FROM ( SELECT
[Extent1].[Name] AS [Name],
[Extent2].[Model] AS [Model],
1 AS [C1]
FROM (SELECT
[AllPeople].[Id] AS [Id],
[AllPeople].[Name] AS [Name],
[AllPeople].[Age] AS [Age]
FROM [dbo].[AllPeople] AS [AllPeople]) AS [Extent1]
INNER JOIN (SELECT
[AllPeopleCars].[PersonId] AS [PersonId],
[AllPeopleCars].[Model] AS [Model],
[AllPeopleCars].[Id] AS [Id]
FROM [dbo].[AllPeopleCars] AS [AllPeopleCars]) AS [Extent2] ON [Extent1].[Id] = [Extent2].[PersonId]
) AS [Project1]
ORDER BY [Project1].[Model] ASC
Don’t ask me what the nested select statement and C1 is for, but else this is quite straight forward.
But, as you also can see, the two from statements result in a INNER JOIN. But what about ecologic people, as for example my colleague Hergen?
“Also list people without cars”
On the SQL side we just have to use a LEFT OUTER JOIN instead of the current INNER JOIN. But how to do that with LINQ?
LINQ does natively support joins, but doesn’t offer a direct keyword that would let you specify how the join should be mapped to a relational structure.
But there is a simple trick, that semantically should do the same thing:
from p in model.AllPeople
from c in p.Cars.DefaultIfEmpty()
orderby c.Model
select new { p.Name, Model = c != null ? c.Model : null };
DefaultIfEmpty() should just pretend a collection with a single type default of the expected type, default(Car)==null in this case and return it as single collection item, if the base collection p.Cars is empty.
It depends on the LINQ implementation, whether Model on c (which can be null) is accessible directly (as in SQL) or has to be checked. Since this code is never really compiled and ran, but instead converted to an Expression Tree and passed into the specific LINQ implementation for interpretation, it could gracefully do the null-checking internally – which some implementations do.
So, LINQ would let you express it this way. But the LINQ implementation for Entity Framework (LINQ to Entities) does not support DefaultIfEmpty(). There is a lot of information out there on left outer joins in LINQ to Entities, but nothing that really works. You know a solution? Please, tell me!
So here we have to switch to Entity SQL (Object Query). We could also use the Query Builder Methods, but lets save that for later.
var query = model.CreateQuery<IDataRecord>(
"SELECT p.Name, c.Model FROM AllPeople as "
+ "p OUTER APPLY p.Cars as c ORDER BY c.Model");
var casted = from item in query.ToArray() /* executes here */
select new {
Name = item.GetString(0),
Model = (item.IsDBNull(1) ? null : item.GetString(1))
};
/* Results
* { Name = Hergen Oltmann (A), Model = }
* { Name = Michael Kloss (B), Model = Mercedes E350 CDI Coupé }
* { Name = Lars Corneliussen (B), Model = Skoda Oktavia }
* { Name = Lars Corneliussen (B), Model = VW Fox }
* { Name = Michael Jendryschik (A), Model = VW Golf 5 }
* { Name = Michael Kloss (B), Model = Vw Golf 6 }
*/
Now we got what we wanted. The SQL query send to the server just has a LEFT OUTER JOIN instead of the INNER JOIN.
But we had to refactor the whole query and missed all the convenience LINQ to SQL offers for this simple requirement. Since Object Query has no support for anonymous types, we also have to access the result set in a generic manner, which throws us back to, say, SqlDataReader.
The story goes on. Lets forget people without cars for now and try some other real world restrictions.
“Lets list all people driving Volkswagen”
Back to our favorite LINQ-syntax:
from p in model.AllPeople
where p.Cars.Any(c => c.Model.Contains("VW"))
select p;
/* Results
* { Name = Michael Jendryschik (A), Age = 42 }
* { Name = Lars Corneliussen (B), Age = 26 }
* { Name = Michael Kloss (B), Age = 33 }
*/
The results are what we expected, and this is the SQL query being executed:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[Age] AS [Age]
FROM (SELECT
[AllPeople].[Id] AS [Id],
[AllPeople].[Name] AS [Name],
[AllPeople].[Age] AS [Age]
FROM [dbo].[AllPeople] AS [AllPeople]) AS [Extent1]
WHERE EXISTS (SELECT
cast(1 as bit) AS [C1]
FROM (SELECT
[AllPeopleCars].[PersonId] AS [PersonId],
[AllPeopleCars].[Model] AS [Model],
[AllPeopleCars].[Id] AS [Id]
FROM [dbo].[AllPeopleCars] AS [AllPeopleCars]) AS [Extent2]
WHERE ([Extent1].[Id] = [Extent2].[PersonId]) AND ((CAST(CHARINDEX(N'VW', [Extent2].[Model]) AS int)) > 0)
)
Instead of doing a sub-query we could also easily use a join in combination with distinct. That doesn’t really make a difference.
But do you see this ugly part there? ((CAST(CHARINDEX(N’VW’, [Extent2].[Model]) AS int)) > 0) Well it works for a demo, but usually what we really want Contains to map to is a LIKE, right.
Back to the real customer; do you know those DataGrids with filter fields beneath each title column? Well lets pretend someone enters “M” for filtering the resulting rows by name.
“Give me only those people driving Volkswagen, whose names start with ‘M’“
Since LINQ to Entities neither supports StartsWith nor Like, we have to switch to Object Query again.
This time, we’ll use the query builder methods. At the same time we also replace the CHARINDEX for “VW” to a proper LIKE.
var query = model.AllPeople
.Where("EXISTS(SELECT c FROM it.Cars as c "
+ "WHERE c.Model LIKE '%VW%')")
.Where("it.Name LIKE @pattern",
new ObjectParameter("pattern", "m%"));
/* Results
* { Name = Michael Jendryschik (A), Age = 42 }
* { Name = Michael Kloss (B), Age = 33 }
*/
This results into following SQL statement:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[Age] AS [Age]
FROM (SELECT
[AllPeople].[Id] AS [Id],
[AllPeople].[Name] AS [Name],
[AllPeople].[Age] AS [Age]
FROM [dbo].[AllPeople] AS [AllPeople]) AS [Extent1]
WHERE ( EXISTS (SELECT
cast(1 as bit) AS [C1]
FROM (SELECT
[AllPeopleCars].[PersonId] AS [PersonId],
[AllPeopleCars].[Model] AS [Model],
[AllPeopleCars].[Id] AS [Id]
FROM [dbo].[AllPeopleCars] AS [AllPeopleCars]) AS [Extent2]
WHERE ([Extent1].[Id] = [Extent2].[PersonId]) AND ([Extent2].[Model] LIKE '%vw%')
)) AND ([Extent1].[Name] LIKE 'm%')
I hope my example queries were able to give you an overview over the state of LINQ to SQL and its current limitations.
Resources on EF
http://www.amazon.com/Programming-Entity-Framework-Julia-Lerman/dp/059652028X
Inheritance in EF
Entity Data Model Inheritance (Application Scenarios)
ADO.NET team blog : Inheritance in the Entity Framework
My Advice
If you have to stick with Entity Framework, use ObjectQuery and Entity SQL and avoid using LINQ to Entities except you know what you are doing. Maybe you should also look into some community tools around EF then.
If you haven’t chosen yet, or if you still have the chance to get out of the tight spot, consider other O/R-mappers as for example NHibernate, which we also switched to later in the project.
I’ll show how we solved our problems using NHibernate in a follow-up post soon.
Lars Corneliussen 