Entity Framework ToList() in nested type (LINQ to Entities does not recognize the method)

Update:
The issue is described here:
http://entityframework.codeplex.com/workitem/808
I've made a fork of EF source which solves this issue @
http://entityframework.codeplex.com/SourceControl/network/forks/brentmckendrick/Issue808/changeset/b18e48b3e51f
however more work could be done to enhance the code by allowing for ToArray and ToDictionary.

So today I have a suggestion for an improvement to the Entity Framework.

First let me set the scenario:

Say I have an entity called Person which has many Addresses, as shown below.

public class Person  
{
    // .. lots of fields ..
    public ICollection<Address> Addresses { get; set; }
}

public class Address  
{
    // .. lots of fields ..
}

Now say I would like to create a projection so that I do not return all of the information contained in the Person and Address models.
I could write this:

Context.Persons  
.Select(p => new
{
    Addresses = p.Addresses.Select(m => new
    {
        // address properties
    })
    // other person properties
}).ToList();

This would obviously give me an anonymous type (A1) which contains a property of List'(T) called Addresses where T is anonymous type (A2). The key here is that the type instantiated is in fact a List'(T)

Now lets say I have a set of data transfer objects. One is PersonDTO which has a property of type List'(T) where T is an AddressDTO as shown below.

public class PersonDTO  
{
    // .. a few fields ..
    public List<AddressDTO> Addresses { get; set; }
}

public class Address  
{
    // .. a few fields ..
}

I want to map a person to a PersonDTO. This is shown here:

Context.Persons  
.Select(p => new PersonDTO
{
    Addresses = p.Addresses.Select(m => new AddressDTO
    {
        // address properties
    }).ToList() // not supported
    // other person properties
}).ToList();

I get an Exception : System.NotSupportedException - "LINQ to Entities does not recognize the method 'System.Collections.Generic.List1[t] ToList[t](System.Collections.Generic.IEnumerable1[t])' method, and this method cannot be translated into a store expression.

Now all of you LINQ to Entities guys are screaming "You can't call ToList() inside of a nested projection!". OK, I can see that the ToList() will not be able to be translated to an SQL query, obviously if I leave it out then my code will not compile since PersonDTO.Addresses is a List'(T). If I change the PersonDTO.Addresses field to an IEnumerable and remove the ToList() then it works! (and by reflecting the runtime type it is in fact populated with a generic list). So the list IS being instantiated anyway.

Now I know this projection could be done in memory and I would not have any problems calling ToList() because it would not need to be translated into an SQL query, however this defeats the purpose as I am trying to write an SQL query.

Now to the point!:

  • An anonymous type can be created with a nested collection property of type List.
  • A concrete type can be created with a nested collection of type IEnumerable and a List will be created and populated for this variable
  • A concrete type with a nested property collection of a List (or any derivative of type ICollection) can NOT be projected to (because of the ToList() issue)

Now it seems to me that since in all of these cases a List is created anyway that perhaps when the expression is mapped to an SQL function the call to the nested ToList() should be ignored? Simply by doing this all of the problems would be solved. (Please correct me if this assumption is incorrect). Better yet what if it WASN'T ignored but removed from the SQL expression and stored for later use so that we could also call ToArray(), ToList(), etc. and change the type which is being returned from the EF materialization? Eg.

Context.Persons  
.Select(p => new PersonDTO
{
    Addresses = p.Addresses.Select(m => new AddressDTO
    {
        // address properties
    }).ToArray()
    // other person properties
}).ToList();
comments powered by Disqus