entity-framework, how to project into a list

Question

I've been using linq2sql on a few projects, but i decided it was time to try out EF as its supposed to be more powerful and better. There are a few things that is really annoying tho. One of them is projecting a result into a list, this works well in l2sql but not in EF.

public class bo.Transaction
{
  public long Id { get; set; }
  public List<bo.Line> Lines { get; set; }
}

public class bo.Line
{
  public int RowNo { get; set; }
  public string Descripton{ get; set; }
  public double Amount{ get; set; }
}

return from t in Db.Transaction.Include("Lines")
       select new bo.Transaction {
         Id = t.Id,
         Lines = t.Lines.OrderBy(l => l.RowNo).ToList(),
       };

The ToList() call fails however, with a message "System.NotSupportedException: LINQ to Entities does not recognize the method 'List[bo.Line] ToListLine' method, and this method cannot be translated into a store expression..".

Any way to get around this? Or do i just have to use ienumerable instead of lists?

Answer 1

This is a stab in the dark, but does your project have a reference to the System.Core dll? Without this reference, the .ToList() extension method won't be available.

Also, as Workshop Alex points out, your .ToList() call is in the wrong place.

Answer 2

return (from t in Db.Transaction.Include("Lines")
       select new bo.Transaction {
         Id = t.Id,
         Lines = t.Lines.OrderBy(l => l.RowNo),
       }).ToList();

The Linq query just doesn't support list, so we execute this on the Enumeration result. It does force the Query to execute right at that moment, though. Returning the IEnumerable would delay the execution of the query a bit longer. (Basically until the moment when you're going to access the data.)

Answer 3

What wrote Workshop Alex is right, but let me explain the theory behind it:
as you probably know, when you write a LINQ-to-Entities query, you're writing something that will be executed against your database. In order for that to be done in an efficient way, instead of fetching each entity, testing it for the given criteria, sorting the result and such operations, the query will be translated to SQL and run against your database.
The fact, however, that any valid C# is accepted for your query, doesn't mean that all of the code you could write has a translation to SQL. Notable examples of code without translation is a query mixing data access and reflection on your entity classes, or - just like in your case - code using peculiar .NET data types. That kind of queries usually can be performed in two steps, one operating against the database and one against the entities in RAM. While that will not probably be as efficient and as clean as you'd like, since personally I dislike stored procedures I still find it much better than most other approaches you may take, provided that your constraints allow you to make the choice.

Edit:
the scenario can indeed be easily handled running part of the query on the db and a part in memory this way:

Db.Transaction.Include("Lines").Select(t => new bo.Transaction { t.Id, Lines = t.Lines.OrderBy(l => l.RowNo) }).AsEnumerable().Select(t => new bo.Transacton { t.Id, Lines = t.Lines.ToList()});

The AsEnumerable call helps making sure that the second Select is run against a set of objects already fetched from the DB and thus there's no longer the problem of translating ToList to SQL.