Linq Select with inner method error - LINQ to Entities does not recognize the method

Question

I am using a View / Presenter structure for my project. The project is a e-comm site with OrderedItems and Products. When calling GetOrdersItemsByOrderID(orderID), I get an LINQ error.

LINQ to Entities does not recognize the method 'DAL.Views.ProductView GetProductBySku(System.String)' method, and this method cannot be translated into a store expression.

I know that LINQ does not like dynaimc content within its query so the proplem is the SELECT Product = Presenters.ProductsPresenter.GetProductBySku(c.productSKU)

I dont know how to Build the GetOrdersItemsByOrderID TOLIST while also have another GetProductBySku method filling the field ProductDetail

/----------------/

public static List<OrderItemsView> GetOrdersItemsByOrderID(int orderID)
{
    using (var ctx = new ProductEntities())
{

    var result = ctx.OrderedItems.Where(o => o.orderID == orderID)
        .Select(c => new OrderedItemsView
                         {
                             OrderItemID = c.orderItemID,
                             OrderID = c.orderID,
                             ProductSku = c.productSKU,
                             ProductPrice = c.productPrice,
                             ProdQuantity = c.prodQuantity,
                             ProductDetail = Presenters.ProductsPresenter.GetProductBySku(c.productSKU)
                         }).ToList();
    return result;
    }
}

/----------------/

public class OrderedItemsView
    {
        public int OrderItemID { get; set; }
        public int OrderID { get; set; }
        public string ProductSku { get; set; }
        public decimal ProductPrice { get; set; }
        public int ProdQuantity { get; set; }
        public decimal? ProductWeight { get; set; }

      public ProductView ProductDetail { get; set; } <-- Get Product Details
    }

/----------------/

public static Views.ProductView GetProductBySku(string sku)
    {
    using (GroupProductEntities ctx = new GroupProductEntities())
    {
        //-------------------------------------------------------------------//
        var results =
            ctx.Products.Where(p => p.clientID == Config.ClientID && p.productSKU == sku)
            .Select(p => new Views.ProductView
                             {
                                 ProductID = p.productID,
                                 ClientID = p.clientID,
                                 CategoryID = p.categoryID,
                                 ProductName = p.productName,
                                 ProductImage1 = p.productImage1,
                                 ProductPrice = p.productPrice,
                                 ProductInventory = p.productInventory,
                                 ProductSku = p.productSKU,
                             }).FirstOrDefault();
        return results;
    }
 }

/----------------/

public class ProductView
{
    public int ProductID { get; set; }
    public int? CategoryID { get; set; }
    public string ProductName { get; set; }
    public string ProductShortDesc { get; set; }
    public string ProductImage1 { get; set; }
    public decimal? ProductPrice { get; set; }
    public decimal? ProductInventory { get; set; }
    public string ProductSku { get; set; }
}

/----------------/

score 2 · Accepted Answer · answered Apr 01 '12 at 15:59

2

You might be able to get away with adding a "ToList" to execute the query before you call that method.

Try this:

   var result = ctx.OrderedItems.Where(o => o.orderID == orderID).ToList()
        .Select(c => new OrderedItemsView
                         {
                             OrderItemID = c.orderItemID,
                             OrderID = c.orderID,
                             ProductSku = c.productSKU,
                             ProductPrice = c.productPrice,
                             ProdQuantity = c.prodQuantity,
                             ProductDetail = Presenters.ProductsPresenter.GetProductBySku(c.productSKU)
                         });

I moved your ToList() up a bit. You may still need one at the end of the Select as well.

answered Apr 01 '12 at 15:59

Robaticus

22,857
5
54
63

Yikes. If `OrderedItems` is what it sounds like, this code will die on any realistic data. – Kirk Woll Apr 01 '12 at 16:00
I'm assuming that the .ToList() where it is after the "Where" would be appropriately limited. – Robaticus Apr 01 '12 at 16:01
Perfect .. after the moved the ToList I need to return as 'results.ToList()' .. thanks – Ravi Ram Apr 01 '12 at 16:15
You should use `.AsEnumerable()`, not `.ToList()`. – Timwi Apr 01 '12 at 16:27
@Timwi - I'm sorry to say that I'm not familiar with the guidance as to why to use .AsEnumerable() vs. .ToList(). Can you elaborate? – Robaticus Apr 01 '12 at 16:44
Based on http://stackoverflow.com/questions/3389855/am-i-misunderstanding-linq-to-sql-asenumerable - it looks like to force the query to execute, they should use the .ToList(). – Robaticus Apr 01 '12 at 16:45
1

@Robaticus: You should use `.ToList()` at the end, after all the `Where`, `Select`, etc., and only if you *actually need a list*. But to transition from a SQL query to C# code, you should use `AsEnumerable`. Otherwise you’ll be generating the list only to instantiate another iterator (`Select` in this case) on top of it, from which you might have to create *another* list. That’s redundant. In technical terms, `AsEnumerable` maintains lazy evaluation. – Timwi Apr 01 '12 at 16:51
@Timwi - I changed the first .ToList to .AsEnumerable .. the code worked. Thanks for explanation and code update. – Ravi Ram Apr 01 '12 at 20:03

Linq Select with inner method error - LINQ to Entities does not recognize the method

1 Answers1