C# Web API LINQ Entity Queries - Cast AnonymousType to Class-Object

Question

I've got the following piece of code:

public List<Product> ListAll()
{
    List<Product> products = new List<Product>();

    var db_products = db.Products
        .Where(p => p.Enabled == true)
        .OrderBy(p => p.Name)
        .Select(p => new
        {
            ProductId = p.ProductId,
            Name = p.Name,
            ...
        })
        .ToList();

    foreach (var dbP in db_products)
    {
        Product p = new Product();
        p.ProductId = dbP.ProductId;
        p.Name = dbP.Name;
        ...
        products.Add(p);
    }

    return products;
}

It works as I want, since it successfully returns a List of Product-objects. Still, isn't there a way without the foreach loop, so I can Cast it immediately?

I did try:

public List<Product> ListAll()
{
    List<Product> products = db.Products
        .Where(p => p.Visible == true)
        .OrderBy(p => p.Name)
        .Select(p => new
        {
            ProductId = p.ProductId,
            Name = p.Name,
            ...
        })
        .AsEnumerable()
        .Cast<Product>()
        .ToList();

    return products;
}

And

public List<Product> ListAll()
{
    List<Product> products = db.Products
        .Where(p => p.Visible == true)
        .OrderBy(p => p.Name)
        .Select(p => new Product
        {
            ProductId = p.ProductId,
            Name = p.Name,
            ...
        })
        .ToList();

    return products;
}

But both doesn't work. With the second one I get the following error:

at System.Data.Objects.ELinq.ExpressionConverter.CheckInitializerType(Type type)
   at System.Data.Objects.ELinq.ExpressionConverter.MemberInitTranslator.TypedTranslate(ExpressionConverter parent, MemberInitExpression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
   at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.Convert()
   at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator()
   at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at SeashellBrawlCorvee...ListAll() in c:\Users\...\Documents\Visual Studio 2012\Projects\seashell-brawl-corvee\seashell-brawl-corvee\...\ProductController.cs:line 149
   at SeashellBrawlCorvee...ProductsRepository..ctor() in c:\Users\...\Documents\Visual Studio 2012\Projects\seashell-brawl-corvee\seashell-brawl-corvee\...\ProductsRepository.cs:line 21
   at SeashellBrawlCorvee...ProductsController..cctor() in c:\Users\...\Documents\Visual Studio 2012\Projects\seashell-brawl-corvee\seashell-brawl-corvee\...\ProductsController.cs:line 16

If anyone knows a solution I would appreciate it, otherwise I just stick with the foreach loop.

Thanks in advance for the responses.

if you are returning a list of Product, why are you creating new Product instances? `db_products` is already a list of Product — Thewads, Apr 29 '14 at 14:37
The last code sample is definitely the best way to do this algorithmically, and it should work. Where do you get the error? Just trying to run statement? — DLeh, Apr 29 '14 at 14:39
Why do you use select at the end and not straight ToList(),considering you want to get a list of the same Object? Is this just to fill less fields? — Dimitris Kalaitzis, Apr 29 '14 at 14:44

Felipe Oriani · Accepted Answer · 2014-04-29T15:07:57.557

3

When you call a method from an ORM, it can return a proxy, so, when web api need to deserialize it, you will get a lot of problems.

A way to do this, is create a ViewModel. For sample, create a class with the information you need:

public class ProductViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    // properties
}

And when you query the datam try to return a List<ProductViewModel>, for sample:

return db.Products
    .Where(p => p.Visible == true)
    .OrderBy(p => p.Name)
    .Select(p => new ProductViewModel() { Id = p.ProductId, Name = p.Name })
    .ToList();

Using ViewModel to expose to a View or Request is a good pratice.

edited Apr 29 '14 at 15:07

answered Apr 29 '14 at 14:43

Felipe Oriani

37,948
19
131
194

1

Indeed a much better solution! – jessehouwing Apr 29 '14 at 15:08
Though JotaBe's answer was originally accepted by me, I now changed my mind. I found out that the double .toList() can cause connection errors in the query. And though they rarely occur (about once every 40-hour week while working with it non-stop), they still do occur. After changing my code to your answer and everything is still working (so far without any errors), I accepted your answer as the correct one now instead of JotaBe's. – Kevin Cruijssen May 30 '14 at 13:49
I'm glad to help you improve your solution for this problem. That's the spirit of the Stack Overflow. :) – Felipe Oriani May 30 '14 at 17:26

score 2 · Answer 2 · edited May 23 '17 at 12:03

2

I suppose that Product is a class of your DbContext.

If so, LINQ will try to map your projection (Select) to the DB, so it will try to construct a query, and doesn't know how to do it.

The solution is:

etiher to materialize the query, so the query is created an executed at this point, with .ToList() (or ToArray(), ToDictionary()...)
or cast it to enumerable, so it's no longer queryable and can not be mapepd to the model/DB, using AsEnumerable()

And then the Select projection can be done without it being mapped to the EF model/DB:

public List<Product> ListAll()
{
  List<Product> products = db.Products
      .Where(p => p.Visible == true)
      .OrderBy(p => p.Name)
      . AsEnumerable() // or .ToList()
      .Select(p => new Product
      {
          ProductId = p.ProductId,
          Name = p.Name,
          ...
      })
      .ToList();

Generally, using AsEnumerable is more efficient. For more information see:

this extensive answer to this question: What's the difference(s) between .ToList(), .AsEnumerable(), AsQueryable()?
and this Q&A: What is the effect of AsEnumerable() on a LINQ Entity?

edited May 23 '17 at 12:03

Community

1
1

answered Apr 29 '14 at 14:50

JotaBe

38,030
8
98
117

I would be careful with this type of approach. The first ToList() will be pulling out the full fields from the database, and not just the fields that you are wanting – Thewads Apr 29 '14 at 15:03
The `ToList` should not be needed! – jessehouwing Apr 29 '14 at 15:06
@jessehouwing I Know it can be done in a different way, so I have updated my answer to reflect it. But you need to do one of this two things. – JotaBe Apr 29 '14 at 15:47
AsEnumerable as well as ToList will execute the query and get all the data and since the last thing was to query Product, it will fetch all fields of Product, which can be very expensive if you're only after a few (hard to tell, but why else do a projection). And then you do the projection, but all the performance hit has already been taken. – jessehouwing Apr 30 '14 at 06:39
Instead remove the `.AsEnumerable` and the `.Select` to just return the Product Entities as a whole, or apply the ViewModel pattern which Filipe describes. – jessehouwing Apr 30 '14 at 06:40
The OP wanted to load part of the columns in a DbContext class, as he told in the comments. I've given a working solution to do it, with explanation on why it works, and why his solution failed. I'm, not discussing if that's a good or a bad thing to do. And, of course, in my code base I always use ViewModel or DTOs, if you prefer that name. But that's another discussion. If you're on legacy code you can be forced to make things like this, because many times you're not allowed the time to refactor. – JotaBe Apr 30 '14 at 08:19
Finally, I like the phrase "Premature optimization is the root of all evil". You don't know if he is loading one product or 3 or 3 billion. You don't know if he is doing it one time or many times. You don't even know how many columns there are and how many he is loading... And you don't know nothing about the code and the app. There are always better, more optimized, or more clear ways to solve problems. But, until proved true, you don't need to do it in the best way, specially if you don't have enough knoledge to do it. – JotaBe Apr 30 '14 at 08:26
Sometimes I get an Exception with the message (`An error occurred while closing the provider connection.`). Since this error was only occurring like once every 40-hour week, I first ignored it because I had more important issues. But today it happened twice in a row at different queries and I traced it back to this double .toList(). After changing it to Felipe Oriani everything still works, and so far the error hasn't occurred anymore. So, though I had accepted this answer originally, I now changed my mind because this can cause errors. (Though they rarely happen, they CAN occur). – Kevin Cruijssen May 30 '14 at 13:45
Thanks for informing, but the problem is not my query. I bet that sooner or later you'll get the same error with any other query. If a piece of code works sometimes, and sometimes it doesn't, and this doesn't depend on a particular "input" (for example a null object, that shuldn't be null) then, get worried about it. Hint from MSDN, DbContext class: *Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe* Another hint: how are you controlling contention on the db object of your `ListAll()` method? – JotaBe May 30 '14 at 14:36

C# Web API LINQ Entity Queries - Cast AnonymousType to Class-Object

2 Answers2