Entity-structure how to design into a list
I've used linq2sql for several projects, but decided it was time to try EF as it should be more powerful and better. There are a few things that are really annoying. One of them projects the 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(),
};
However, the call to ToList () fails with the message " System.NotSupportedException: LINQ to Entities does not recognize the method" List [Bo.Line] ToListLine "and this method cannot be translated into expression store .. ".
How do I get around this? Or do I just need to use ienumerable instead of lists?
source to share
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 a list, so we execute it as a result of the enumeration. However, this forces the request to execute at that moment. Returning an IEnumerable delays query execution a little longer. (Mostly up to the point where you are going to access the data.)
source to share
Which is written in Alex's workshop, but let me explain the theory behind it:
as you probably know, when you write a LINQ-to-Entities query, you are writing something that will execute against your database. In order for this to be done in an efficient way, instead of fetching each object, testing it against given criteria, sorting the result and such operations, the query will be translated to SQL and work with your database.
However, the fact that any valid C # is accepted for your query does not mean that all the code you could write is translated to SQL. Notable examples of code without translation are a query that mixes data access and reflection in your entity classes, or - just like in your case - code using .NET specific data types. Such queries can usually be executed in two stages: one works with the database and one with objects in RAM. While this probably won't be as efficient and clean as you'd like, since I personally don't like stored procedures, I still find it much better than most other approaches you can take, assuming your constraints allow you to make choices.
Edit: The
script can indeed be easily handled with a db request part and an in-memory part like this:
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()});
Calling AsEnumerable helps ensure that the second selection is fired against the set of objects already fetched from the DB, and therefore no longer a problem translating ToList to SQL.
source to share