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

Question

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 in EF, the c.prod_deploy_date.ToString() causes one too and it's a simple DateTime. Like previously, in order to fix it I had to do my select transformation after ToList().

Edit: Another case I just came across is that EF cannot handle where clauses that compare entities where as L2S has no issues for with it. For example the query

context.TfsWorkItemTags.Where(x => x.TfsWorkItem == TfsWorkItemEntity).ToList()

throws an exception and instead I have to do

context.TfsWorkItemTags.Where(x => x.TfsWorkItem.id == tfsWorkItemEntity.id).ToList()

Edit 2: I wanted to add another issue that I found. Apparently you can't use arrays in EF Linq queries, and this probably annoys me more than anything. So for example, right now I convert an entity that denotes a version into an int[4] and try to query on it. In Linq-to-Sql I used the following query:

return context.ReleaseVersions.Where(x => x.major_version == ver[0] && x.minor_version == ver[1]
                                          && x.build_version == ver[2] && x.revision_version == ver[3])
                              .Count() > 0;

This fails with the following exception:

The LINQ expression node type 'ArrayIndex' is not supported in LINQ to Entities.

Edit 3: I found another instance of EF's bad Linq implementation. The following is a query that works in L2S but doesn't in EF 4.1:

        DateTime curDate = DateTime.Now.Date;
        var reqs = _context.TestRequests.Where(x => DateTime.Now > (curDate + x.scheduled_time.Value)).ToList();

This throws an ArgumentException with the message DbArithmeticExpression arguments must have a numeric common type.

Why does it seem like they downgraded the ability for Linq queries in EF than in L2S?

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

3 Answers