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

Question

I have the following code:

lstcart = (from t in context.T_Order
                           join  s in context.T_OrderDetails on t.ID equals s.Order_ID
                           join  u in context.T_OrderDetailSpecification  on s.ID  equals u.OrderDetails_ID
                           join p in context.M_Product on s.Product_ID  equals p.ID
                           join qrs in context.M_ProductCategory on p.ProductCategory_ID equals qrs.ID
                           where t.User_ID == u_id && s.OrderStatus_ID == 1 || s.OrderStatus_ID == 17
                           select new Cart
                           {
                               ID = s.ID,
                               Path = p.VirtualPath,
                               ProductCategory = qrs.Title,
                               Quantity = 1,
                               Title = p.Title,
                               Amount = Convert.ToSingle(u.Value),
                               Order_Id= s.Order_ID,
                               prod_Id= p.ID,
                               Preview = s.IsPreviewRequired
                           }).ToList();

In which the line Amount = float.Parse(u.Value), causing the above error.Here Amount is defined as public float Amount { get; set; } in model cart .

Answer 1

You can complete conversion process in memory,you can return an anonymous type from db and than Convert it to your object.

var lstcart = (from t in context.T_Order
                   join s in context.T_OrderDetails on t.ID equals s.Order_ID
                   join u in context.T_OrderDetailSpecification on s.ID equals u.OrderDetails_ID
                   join p in context.M_Product on s.Product_ID equals p.ID
                   join qrs in context.M_ProductCategory on p.ProductCategory_ID equals qrs.ID
                   where t.User_ID == u_id && s.OrderStatus_ID == 1 || s.OrderStatus_ID == 17
                   select new /// anonymous type
                   {
                       ID = s.ID,
                       Path = p.VirtualPath,
                       ProductCategory = qrs.Title,
                       Quantity = 1,
                       Title = p.Title,
                       Amount = u.Value,
                       Order_Id = s.Order_ID,
                       prod_Id = p.ID,
                       Preview = s.IsPreviewRequired
                   }).AsEnumerable()
                            .Select(result => new Cart /// create your object after query execution in memory
                             {
                                ID = result.ID,
                                Path = result.VirtualPath,
                                ProductCategory = result.Title,
                                Quantity = result.Quantity,
                                Title = result.Title,
                                Amount = Convert.ToSingle(result.Amount),
                                Order_Id = result.Order_ID,
                                prod_Id = result.ID,
                                Preview = result.IsPreviewRequired
                            }).ToList();

Answer 2

You'll probably also find that you can't use toSingle inside a LinQ, similar to the way you can't use toString method as there is no SQL equivilent: Problem with converting int to string in Linq to entities

Answer 3

That line is causing an error since Linq is trying to convert it to an SQL expression, and no translation exists for 'Float.Parse()'.

One method of solving this that I would recommend would be making use of a Data Transfer Object (DTO). Simply create another class similar to Cart, called something like CartDTO, but set 'Amount' as a String (or whatever the type of 'u.Value' is). You could then map CartDTO to Cart, either manually or by making use of a tool such as Automapper.

EDIT:

Technically you don't even need a DTO. Something like this should work (notice select statement simply left as 'select new'):

lstcart = (from t in context.T_Order
                   join s in context.T_OrderDetails on t.ID equals s.Order_ID
                   join u in context.T_OrderDetailSpecification on s.ID equals u.OrderDetails_ID
                   join p in context.M_Product on s.Product_ID equals p.ID
                   join qrs in context.M_ProductCategory on p.ProductCategory_ID equals qrs.ID
                   where t.User_ID == u_id && s.OrderStatus_ID == 1 || s.OrderStatus_ID == 17
                   select new
                   {
                       ID = s.ID,
                       Path = p.VirtualPath,
                       ProductCategory = qrs.Title,
                       Quantity = 1,
                       Title = p.Title,
                       Amount = u.Value,
                       Order_Id = s.Order_ID,
                       prod_Id = p.ID,
                       Preview = s.IsPreviewRequired
                   }).ToList();

And after the query is executed:

List<Cart> newLstCart = new List<Cart>();
        foreach (var old in lstcart)
        {
            newLstCart.Add(new Cart()
            {
                ID = old.ID,
                Path = old.Path,
                ProductCategory = old.ProductCategory,
                Quantity = old.Quantity,
                Title = old.Title,
                Amount = float.Parse(old.Amount),
                Order_Id = old.Order_Id,
                prod_Id = old.prod_Id,
                Preview = old.Preview
            });
        }

However, I would still recommend experimenting with DTOs and Automapper, they can be very useful in more complex situations.