Populate list in POCO using subselect - LINQ to entites

Question

I am trying to create a list of POCOs from my database. The POCO class has a List<int> property which I want to populate like so:

public List<Pocos.MyTable> GetData()
{
    using (EFContext c = new EFContext())
    {
        var query = from t in c.MyTable
                    select new Pocos.MyTable()
                    {
                        MyId = t.MyId,
                        MyField = t.MyField,
                        MyRelationIds = t.MyRelations.Select(mr => mr.MyRelationId).ToList()
                    };

        return query.ToList();
    }
}

Unfortunately I am getting the "this method cannot be translated into a store expression" on the ToList()in the query. Can I do this nicely here, or do I have to do lots of subsequent queries to populate the MyRelationIds property?

Do you have a problem with using `MyRelations` directly? – Vladimir Perevalov Nov 14 '11 at 13:07 — Vladimir Perevalov, Nov 14 '11 at 13:07

score 2 · Accepted Answer · edited Nov 14 '11 at 13:18

From your code and description I got, that if try to just use MyRelations, it is not loaded. And then you try to populeta MyRelationsIds during query. There are two ways to solve this. I personally prefer the first one:

Replace your MyRelations property with public virtual List<Relation> MyRelations. Virtual is a convention, after which EF will create a proxy, that will act as a lazy property. I.e. it will be loaded as soon as you touch it.
Another way is to use eager loading, with it you don't have to change the signature. Just use Include method, to tell EF which props do you want to load eagerly. See that .Include("MyRelations") it tell, that MyRelations must be loaded during this query. So you can use them later. And if you want to have only ids of relations, then you should first materialize your objects and then do mapping (note AsEnumerable())
```
using (EFContext c = new EFContext())
{
    var query = from t in c.MyTable.Include("MyRelations").AsEnumerable()
    select new Pocos.MyTable()
    {
        MyId = t.MyId,
        MyField = t.MyField,
        MyRelationIds = t.MyRelations.Select(mr => mr.MyRelationId).ToList()
    };

    return query.ToList();
 }
```

@errorstacks thanks for help with the code, I was messing with it for past 5 minutes :) It seems, there is some conflict between list and code markups. — Vladimir Perevalov, Nov 14 '11 at 13:20
Oh man, that's it? Its not loaded? I just assumed the lazy loading would kick in and make sure that relation is loaded. I just threw in your second suggestion (as its a quick win), and it works. I guess I need to read up more on this stuff.. :| — Sprintstar, Nov 14 '11 at 14:02

score 0 · Answer 2 · answered Nov 14 '11 at 13:15

0

Did you try remove the ".ToList()" from the line:

MyRelationIds = t.MyRelations.Select(mr => mr.MyRelationId).ToList()

so it looks like this:

MyRelationIds = t.MyRelations.Select(mr => mr.MyRelationId)

?

answered Nov 14 '11 at 13:15

Marc

4,715
3
27
34

This gives me `Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'System.Collections.Generic.List'. An explicit conversion exists (are you missing a cast?)` – Sprintstar Nov 14 '11 at 13:53

Populate list in POCO using subselect - LINQ to entites

2 Answers2