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.

Answer 1

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?

Answer 2

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.