Only parameterless constructors are supported in LINQ to Entities

Question

I have this error in my LINQ statement, however i dont understand whats the problem with it or how can i solve it:

This is my LINQ:

 int year = 2016;
        int month = 11;
        DateTime dateFrom = new DateTime(year, month, 1);
        DateTime dateTo = dateFrom.AddMonths(1);
        int daysInMonth = DateTime.DaysInMonth(year, month);
        var data = db_pdv.Pdv.Where(x => x.Fecha >= dateFrom && x.Fecha < dateTo);

        var model_pdv = data.GroupBy(x => new { Pdv = x.Clave_PDV, Nombre_Pdv = x.Nombre_Pdv, Turno = x.Turno, Nombre_Turno = x.Nombre_Turno, Pla_ID = x.Platillo, Nombre_Platillo = x.Nombre_Platillo, Precio = x.Precio })
            .Select(x => new DishVM()
            {
                Clave_PDV = x.Key.Pdv,
                Nombre_Pdv = x.Key.Nombre_Pdv,
                Turno = x.Key.Turno,
                Nombre_Turno = x.Key.Nombre_Turno,
                Platillo = x.Key.Pla_ID,
                Nombre_Platillo = x.Key.Nombre_Platillo,
                Precio = x.Key.Precio,
                Days = new List<int>(new int[daysInMonth]),
                Data = x
            }).ToList();

And this is my "DishVM" CLass

 public class DishVM
{
    public string Clave_PDV { get; set; }
    public string Nombre_Pdv { get; set; }
    public string Turno { get; set; }
    public string Nombre_Turno { get; set; }
    public int Platillo { get; set; }
    public string Nombre_Platillo { get; set; }

    [DisplayFormat(DataFormatString = "{0:C}")]
    public decimal Precio { get; set; }
    public List<int> Days { get; set; }
    [Display(Name = "Quantity")]
    public int TotalQuantity { get; set; }
    [DisplayFormat(DataFormatString = "{0:C}")]
    [Display(Name = "Total")]
    public decimal TotalPrice { get; set; }
    public IEnumerable<Pdv> Data { get; set; }
}

How can i solve this problem? Thanks in advance

score 3 · Answer 1 · edited May 23 '17 at 12:30

How can I solve this problem?

Start by knowing what the problem is.

In Entity Framework, any expression that operates on a DbSet, like db_pdv.Pdv, is translated into SQL. The whole expression. In your case, this "whole expression" is model_pdv, which is structured as db_pdv.Pdv.Where(...).GroupBy(...).Select(). The expression contains new List<int>(new int[daysInMonth]). You'll understand that it's impossible to translate this into SQL; how would a database engine know how to construct a .Net List<T> object?

So how to solve it?

You could build the list first and then build the expression:

...
var daysList = new List<int>(new int[daysInMonth]);
var data = db_pdv.Pdv.Where(...
        ...
        Precio = x.Key.Precio,
        Days = daysList,
        Data = x

Now you've reduced the SQL translation task to converting primitive values (integers) into SQL. EF knows perfectly well how to do that. But the result is... funny. If you check the generated SQL you see that EF converts the list of integers into some sort of a SQL table. Which looks like ...

    CROSS JOIN  (SELECT 
        0 AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
    UNION ALL
        SELECT 
        0 AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]
    UNION ALL
        SELECT 
        0 AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]
    UNION ALL
        ...

... etcetera.

What happens here, basically, is build a list in c#, convert it into a SQL construct, let the database construct a result set, convert the result set into a list in c# -- complicated.

Another option is to run the whole statement in memory:

var data = db_pdv.Pdv.Where(x => x.Fecha >= dateFrom && x.Fecha < dateTo)
                 .AsEnumerable();
var model_pdv = ...

Normally, I don't advocate this approach, for reasons explained here. But in this case it would be OK, because in the end you're going to use all data (Data = x), so you won't fetch more than you need from the database.

A more profound solution would be to remove the list from the view model altogether. Redundancy is the mother of inconsistency. Why should all model instances need the same list of integers? You should be able to build the list only once where you need it to present a UI.

Only parameterless constructors are supported in LINQ to Entities

1 Answers1