Alex Rivera | Logout

Why does EF 4.1 not support complex queries as well as linq-to-sql?

Asked 2011-06-20T15:57:47.357
9

I am in the process of converting our internal web application from Linq-To-Sql to EF CodeFirst from an existing database. I have been getting annoyed with Linq-To-Sql's limitations more and more lately, and having to update the edmx after updating a very intertwined database table finally frustrated me enough to switch to EF.

However, I am encountering several situations where using linq with Linq-To-Sql is more powerful than the latest Entity Framework, and I am wondering if anyone knows the reasoning for it? Most of this seems to deal with transformations. For example, the following query works in L2S but not in EF:

        var client = (from c in _context.Clients
                      where c.id == id
                      select ClientViewModel.ConvertFromEntity(c)).First();

In L2S, this correctly retrieves a client from the database and converts it into a ClientViewModel type, but in EF this exceptions saying that Linq to Entities does not recognize the method (which makes sense as I wrote it.

In order to get this working in EF I have to move the select to after the First() call.

Another example is my query to retrieve a list of clients. In my query I transform it into an anonymous structure to be converted into JSON:

        var clients = (from c in _context.Clients
                       orderby c.name ascending
                       select new
                       {
                           id = c.id,
                           name = c.name,
                           versionString = Utils.GetVersionString(c.ProdVersion),
                           versionName = c.ProdVersion.name,
                           date = c.prod_deploy_date.ToString()
                       })
                       .ToList();

Not only does my Utils.GetVersionString() method cause an unsupported method exception i

Edit
Report

1 Answer

15

Edit (9/2/2012): Updated to reflect .NET 4.5 and added few more missing features

This is not the answer - it cannot be because the only qualified person who can answer your question is probably a product manager from ADO.NET team.

If you check feature set of old datasets then linq-to-sql and then EF you will find that critical features are removed in newer APIs because newer APIs are developed in much shorter times with big effort to deliver new fancy features.

Just list of some critical features available in DataSets but not available in later APIs:

  • Batch processing
  • Unique keys

Features available in Linq-to-Sql but not supported in EF (perhaps the list is not fully correct, I haven't used L2S for a long time):

  • Logging database activity
  • Lazy loaded properties
  • Left outer join (DefaultIfEmpty) since the first version (EF has it since EFv4)
  • Global eager loading definitions
  • AssociateWith - for example conditions for eager loaded data
  • Code first since the first version
  • IMultipleResults supporting stored procedures returning multiple result sets (EF has it in .NET 4.5 but there is no designer support for this feature)
  • Support for table valued functions (EF has this in .NET 4.5)
  • And some others

Now we can list features available in EF ObjectContext API (EFv4) and missing in DbContext API (EFv4.1):

  • Mapping stored procedures
  • Conditional mapping
  • Mapping database functions
  • Defining queries, QueryViews, Model defined functions
  • ESQL is not available unless you convert DbContext back to ObjectContext
  • Manipulating state of independent relationships is not possible unless you convert DbContext back to ObjectContext
  • Using MergeOption.Over
answered 2011-06-20T19:08:37.187

Your Answer