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
– Thewads, Commented Apr 29, 2014 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
– DLeh, Commented Apr 29, 2014 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
– Dimitris Kalaitzis, Commented Apr 29, 2014 at 14:44

Felipe Oriani · Accepted Answer · 2014-04-29 15:07:57Z

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, 2014 at 15:07

answered Apr 29, 2014 at 14:43

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jessehouwing Over a year ago

Indeed a much better solution!

Kevin Cruijssen Over a year ago

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.

Felipe Oriani Over a year ago

I'm glad to help you improve your solution for this problem. That's the spirit of the Stack Overflow. :)

Community · Accepted Answer · 2017-05-23 12:03:53Z

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, 2017 at 12:03

CommunityBot

11 silver badge

answered Apr 29, 2014 at 14:50

JotaBe

39.3k9 gold badges102 silver badges121 bronze badges

9 Comments

Thewads Over a year ago

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

jessehouwing Over a year ago

The ToList should not be needed!

JotaBe Over a year ago

@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.

jessehouwing Over a year ago

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 Over a year ago

Instead remove the .AsEnumerable and the .Select to just return the Product Entities as a whole, or apply the ViewModel pattern which Filipe describes.

|

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related