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.List`1[t] ToList[t](System.Collections.Generic.IEnumerable`1[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();
Advertisements

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

    • Thanks for the comment however I think you may have misunderstood. I’m trying to project my EF entities onto a separate model (such as a DTO model). Including the Addresses in the query doesn’t relate to this problem.

  1. Sorry about that, I skimmed the post and was perhaps too quick 🙂

    About what you’re writing though… In the anonymous type you don’t do the .ToList() and in the typed DTO you do, I don’t understand why you change the behaviour simply because it’s an anonymous type, there is no difference between them as long as you stay within the scope of the function.

    The statement “An anonymous type can be created with a nested collection property of type List.” is incorrect. Select will return you an IQueryable, not a List.

    EntityFrameWork can quite obviously not understand the .ToList() method, because it doesn’t translate into SQL. In fact asking EF to include this as a feature to be ignored when constructing SQL will make code ambiguous and plain unreadable. Furthermore, do you really need those properties to be Lists? Or do you simply want to force evaluation?

    Having said all that, I’m not sure I quite understand your scenario.
    1: You want to return something of the same type, but with less records, so why try and map this onto a DTO? The EF type will do just fine. And if you want to get the minimal set all resolved, my original suggestion of using the .Include will do just fine.
    2: If you really want to change the types of what is returned, use a 2-step process. L2E is basically allowing you to write SQL in code, if you try to mix it in with non-SQL you’re going to end up writing horribly inefficient code (like Linq2Sql)

    As much as I understand your predicament, I think it’s a bad idea. It promotes bad coding.
    (Try writing the expression tree yourself… ToList/ToArray/AsEnumerable/Etc… would have to be executed cascadingly. The evaluation of the SQL would need to be executed in many separate queries. It would be a genuine pain in the neck to try and do this)

    Anyway, take it how you want 🙂 Just my 5cents

  2. Hmm.

    The statement “An anonymous type can be created with a nested collection property of type List.” is incorrect. Select will return you an IQueryable, not a List.

    You agree an interface cannot be instantiated correct? Obviously there is an underlying concrete type you are forgetting. In the case that I’m describing it is a List. EF makes the decision that it should be a list for us, we do not get to specify this. It then however does not return a List it returns an IEnumerable interface from the inner Select statement, thus not allowing us to fill a property of type list with the result of the materialization. There is no way around this currently. That is what I’m hoping will change.

    EntityFrameWork can quite obviously not understand the .ToList() method, because it doesn’t translate into SQL. In fact asking EF to include this as a feature to be ignored when constructing SQL will make code ambiguous and plain unreadable. Furthermore, do you really need those properties to be Lists? Or do you simply want to force evaluation?

    Of course I’m forcing evaluation, the code is a projection. I’m asking the EF team to consider ignoring it when building the expression tree which creates an SQL statement but then DO NOT ignore it when the objects are being materialized. This happens all throughout LINQ to Entities.

    For example consider Select(p =’ new MyType { // properties }) where MyType is NOT an EF data entity and is a different model.

    At the point of SQL generation it doesn’t matter that you are creating a ‘MyType’ all that is important is what properties need to be SELECTed in sql so that you can fill the object initializer. Obviously ‘MyType’ has no place in SQL and will not be translated to a store expression. Once the query has been executed on the database server and we are materializing the results that is when we are interested in what type needs to be created to return the results.

    The above is an example of how LINQ to Entities lets you state what objects should be created as a result of the sql query, why does it not let you state what collections should be created?

    Having said all that, I’m not sure I quite understand your scenario.
    1: You want to return something of the same type, but with less records, so why try and map this onto a DTO? The EF type will do just fine. And if you want to get the minimal set all resolved, my original suggestion of using the .Include will do just fine.

    Yes you are misunderstanding I wan’t to return something of a DIFFERENT type. That is the whole point of my example.

    In sql it would be something along the lines of this:

    SELECT x.a, x.b, y.c, y.d — Only return certain attributes of the join
    FROM Foo x
    LEFT JOIN Bar y ON x.Id = y.FooId

    Now the data returned from this query does not match my data model in EF. I want to project this flattened view of the join on to a graph data structure (which EF is great at doing) however I want to use an existing model that I have in my C# project. For instance perhaps this above sql query fills a DTO that I return from one of my service methods.

    That is the example that I am describing.

    2: If you really want to change the types of what is returned, use a 2-step process. L2E is basically allowing you to write SQL in code, if you try to mix it in with non-SQL you’re going to end up writing horribly inefficient code (like Linq2Sql)

    As much as I understand your predicament, I think it’s a bad idea. It promotes bad coding.

    (Try writing the expression tree yourself… ToList/ToArray/AsEnumerable/Etc… would have to be executed cascadingly. The evaluation of the SQL would need to be executed in many separate queries. It would be a genuine pain in the neck to try and do this)

    The expression tree which gets mapped to SQL does not change, there are no extra queries. The same SQL is written if the change is implemented. The only difference is that you get to specify what type of collection should be instantiated and this would allow you to fit the projections to already existing concrete data models.

  3. When you say:
    “You agree an interface cannot be instantiated correct? Obviously there is an underlying concrete type you are forgetting.”
    You’re messing with me right? The concrete type of that thing is a DbQuery, not a list. It obviously returns a new list when you project it with .ToList().

    If you want to specify what type of collection should be instantiated you could never do this by allowing any random LINQ operation (or custom extension method or simply a different return type that takes the query as its input) .

    You would have to specify this at the level of the DbQuery (or even your DbContext), and what you’re doing then is actually defining a schema for your sql result. We need a way of defining that schema. Oh wait, it’s called EF…

    Anyway, my point is that I feel you’re confusing LINQ (nothing to do with EF, but a wicked way to work with data and data structures, transform data, etc…) and the way EF supports certain LINQ operations for mapping onto SQL expressions.

    Ignoring operations within this setting would have to be pretty strictly defined, and though I do think it would be possible. It would confuse the crap out of people when deciding how to implement their L2E expressions.

  4. Ok, thanks Brent. I want you to know I recognise and respect your knowledge of the EF framework, and genuinely tried to express my pov on this matter. I’m sorry it was not useful to you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s