Only parameterless constructors and initializers are supported in LINQ to Entities

Question

I have this error in this linq expression :

var naleznosci = (from nalTmp in db.Naleznosci
                              where nalTmp.idDziecko == idDziec
                              select new Payments
                              (
                                  nalTmp.Dziecko.Imie,
                                  nalTmp.Dziecko.Nazwisko,
                                  nalTmp.Miesiace.Nazwa,
                                  nalTmp.Kwota,
                                  nalTmp.RodzajeOplat.NazwaRodzajuOplaty,
                                  nalTmp.RodzajeOplat.TypyOplat.NazwaTypuOplaty,
                                  nalTmp.DataRozliczenia,
                                  nalTmp.TerminPlatnosci
                              )).ToList();

Any idea how solve this problem? I try with any combination of expression... :/

Answer 1

without more info on 'Payments' this doesn't help much, but assuming you want to create a Payments object and set some of its properties based on column values:

var naleznosci = (from nalTmp in db.Naleznosci
                              where nalTmp.idDziecko == idDziec
                              select new Payments
                              {
                                  Imie = nalTmp.Dziecko.Imie,
                                  Nazwisko = nalTmp.Dziecko.Nazwisko,
                                  Nazwa= nalTmp.Miesiace.Nazwa,
                                  Kwota = nalTmp.Kwota,
                                  NazwaRodzajuOplaty = nalTmp.RodzajeOplat.NazwaRodzajuOplaty,
                                  NazwaTypuOplaty = nalTmp.RodzajeOplat.TypyOplat.NazwaTypuOplaty,
                                  DataRozliczenia = nalTmp.DataRozliczenia,
                                  TerminPlatnosci = nalTmp.TerminPlatnosci,
                              }).ToList();

Answer 2

If you still want to use your constructor for initialization and not properties (sometimes this behaviour is desired for initialization purposes), you will need to use LINQ to Collections at some point, because LINQ to SQL doesn't know how to translate constructors to SQL queries. You can do that by using AsEnumerable().

So your code should look something like this:

var naleznosci = 
    db.Naleznosci
        .Where(nalTmp => nalTmp.idDziecko == idDziec)
        .Select(nalTmp => new
            {
                DzieckoImie = nalTmp.Dziecko.Imie,
                DzieckoNazwisko = nalTmp.Dziecko.Nazwisko,
                MiesiaceNazwa = nalTmp.Miesiace.Nazwa
                Kwota = nalTmp.Kwota,
                NazwaRodzajuOplaty = nalTmp.RodzajeOplat.NazwaRodzajuOplaty,
                NazwaTypuOplaty = nalTmp.RodzajeOplat.TypyOplat.NazwaTypuOplaty,
                DataRozliczenia = nalTmp.DataRozliczenia,
                TerminPlatnosci = nalTmp.TerminPlatnosci
            }) // Select necessary columns from DB into anonymous type.
        .AsEnumerable() // Here comes transfer to LINQ to Collections.
        .Select(nalImp => new Payments
            (
                nalTmp.DzieckoImie,
                nalTmp.DzieckoNazwisko,
                nalTmp.MiesiaceNazwa,
                nalTmp.Kwota,
                nalTmp.NazwaRodzajuOplaty,
                nalTmp.NazwaTypuOplaty,
                nalTmp.DataRozliczenia,
                nalTmp.TerminPlatnosci
            )) // Use constructor to create your models.
        .ToList();

Answer 3

Having just encountered this error myself, I thought I would add that if the Payment type is a struct, you would also encounter the same error because struct types do not support parameterless constructors.

In that event, converting Payment to a class and using the object initializer syntax will resolve the issue.

Answer 4

If you're like me and don't want to have to populate your properties for each query you're building, there is another way to solve this issue.

var query = from orderDetail in context.OrderDetails
            join order in context.Orders on order.OrderId equals orderDetail.orderId
            select new { order, orderDetail };

At this point you have an IQueryable containing an anonymous object. If you want to populate your custom object with a constructor you can simply do something like this:

return query.ToList().Select(r => new OrderDetails(r.order, r.orderDetail));

Now your custom object (which takes two objects as a parameter) can populate your properties as needed.

Answer 5

First I would avoid the solution with

from ....
select new Payments
{
  Imie = nalTmp.Dziecko.Imie,
  ....
}

This requires an empty constructor and ignores encapsulation so you are saying new Payments() is a valid payment without any data, but instead the object must have at least a value and probably other required fields depending on your domain.

It's better to have a constructor for required fields but only bring needed data:

from ....
select new
{
  Imie = nalTmp.Dziecko.Imie,
  Nazwisko = nalTmp.Dziecko.Nazwisko
  ....
}
.ToList() // Here comes transfer to LINQ to Collections.
.Select(nalImp => new Payments
 (
  nalTmp.Imie,//assume this is a required field
  ...........
  )
  {
     Nazwisko = nalTmp.Nazwisko //optional field
  })
.ToList();

Answer 6

Sorry for being late to the party, but I after finding this, I thought this should be shared as it's the cleanest, fastest and also memory-saving implementation I could find.

Adapted to your example, you'd write:

public static IQueryable<Payments> ToPayments(this IQueryable<Naleznosci> source)
{
  Expression<Func<Naleznosci, Payments>> createPayments = naleznosci => new Payments
  {
    Imie = source.Dziecko.Imie,
    Nazwisko = source.Dziecko.Nazwisko,
    Nazwa= source.Miesiace.Nazwa,
    Kwota = source.Kwota,
    NazwaRodzajuOplaty = source.RodzajeOplat.NazwaRodzajuOplaty,
    NazwaTypuOplaty = source.RodzajeOplat.TypyOplat.NazwaTypuOplaty,
    DataRozliczenia = source.DataRozliczenia,
    TerminPlatnosci = source.TerminPlatnosci,
  };

  return source.Select(createPayments);
}

The big advantages here (as Damien Guard pointed out in the comments at the link) are:

• Safes you from using initialization pattern on each occurrence.
• Usage via var foo = createPayments(bar); as well as myIQueryable.ToPayments() is possible.

Answer 7

You can try to do the same, but using the methods of extension. What is the provider of the database use?

var naleznosci = db.Naleznosci
                          .Where<TSource>(nalTmp => nalTmp.idDziecko == idDziec)
                          .Select<TSource, TResult>(
                             delegate(TSource nalTmp) { return new Payments
                             (
                                 nalTmp.Dziecko.Imie,
                                 nalTmp.Dziecko.Nazwisko,
                                 nalTmp.Miesiace.Nazwa,
                                 nalTmp.Kwota,
                                 nalTmp.RodzajeOplat.NazwaRodzajuOplaty,
                                 nalTmp.RodzajeOplat.TypyOplat.NazwaTypuOplaty,
                                 nalTmp.DataRozliczenia,
                                 nalTmp.TerminPlatnosci
                             ); })
                          .ToList();

Answer 8

I had the same problem today and my solution was similar to what Yoda listed, however it only works with fluent syntax.

Adapting my solution to your code: I added the following static method to the object class

    /// <summary>
    /// use this instead of a parameritized constructor when you need support
    /// for LINQ to entities (fluent syntax only)
    /// </summary>
    /// <returns></returns>
    public static Func<Naleznosci, Payments> Initializer()
    {
        return n => new Payments
        {
             Imie = n.Dziecko.Imie,
             Nazwisko = n.Dziecko.Nazwisko,
             Nazwa = n.Miesiace.Nazwa,
             Kwota = n.Kwota,
             NazwaRodzajuOplaty = n.RodzajeOplat.NazwaRodzajuOplaty,
             NazwaTypuOplaty = n.RodzajeOplat.TypyOplat.NazwaTypuOplaty,
             DataRozliczenia = n.DataRozliczenia,
             TerminPlatnosc = n.TerminPlatnosci
        };
    }

and then updated the base query to the following:

var naleznosci = (from nalTmp in db.Naleznosci
    where nalTmp.idDziecko == idDziec
    select new Payments.Initializer());

This is logically equivalent to James Manning's solution with the advantage of pushing the bloat of member initialization to the Class / Data Transfer Object

Note: Originally I was using more descriptive names that "Initializer" but after reviewing how I was using it, I found that "Initilizer" was sufficient (at least for my purposes).

Final Note:
After coming up with this solution I was originally thinking it would be simple to share the same code and adapt this to work for Query syntax as well. I am no longer believe that to be the case. I think that if you want to be able to use this type of shorthand construction you would need a method for each (query,fluent) fluent as described above which can exist in the object class itself.

For query syntax an extension method (or some method outside of the base class being used) would be required. (since query syntax wants to operate an IQueryable rather than T)

Here is a sample of what I used to finally get this to work for query syntax. (Yoda already nailed this but I think the usage could be clearer because I didn't get it at first)

/// <summary>
/// use this instead of a parameritized constructor when you need support
/// for LINQ to entities (query syntax only)
/// </summary>
/// <returns></returns>
public static IQueryable<Payments> Initializer(this IQueryable<Naleznosci> source)
{
    return source.Select(
        n => new Payments
        {
            Imie = n.Dziecko.Imie,
            Nazwisko = n.Dziecko.Nazwisko,
            Nazwa = n.Miesiace.Nazwa,
            Kwota = n.Kwota,
            NazwaRodzajuOplaty = n.RodzajeOplat.NazwaRodzajuOplaty,
            NazwaTypuOplaty = n.RodzajeOplat.TypyOplat.NazwaTypuOplaty,
            DataRozliczenia = n.DataRozliczenia,
            TerminPlatnosc = n.TerminPlatnosci
    };
}

and the usage

var naleznosci = (from nalTmp in db.Naleznosci
    where nalTmp.idDziecko == idDziec
    select nalTmp).Initializer().ToList();

Answer 9

In addition to the aforementioned methods, you can also parse it as an Enumerable collection, like so:

(from x in table
....
).AsEnumerable()
.Select(x => ...)

This also has the added benefit of making life easier when building an anonymous object, like this:

 (from x in tableName
select x.obj)
.Where(x => x.id != null)
.AsEnumerable()
.Select(x => new {
   objectOne = new ObjectName(x.property1, x.property2),
   parentObj = x
})
.ToList();

Remembering, however, that parsing a collection as Enumerable pulls it into memory, so it can be resource intensive! Caution should be used here.

Answer 10

yeh, try it like this....

var naleznosci = (from nalTmp in db.Naleznosci
                              where nalTmp.idDziecko == idDziec
                              select new Payments()
                              {
                                  Dziecko.Imie,
                                  Dziecko.Nazwisko,
                                  Miesiace.Nazwa,
                                  Kwota,
                                  RodzajeOplat.NazwaRodzajuOplaty,
                                  RodzajeOplat.TypyOplat.NazwaTypuOplaty,
                                  DataRozliczenia,
                                  TerminPlatnosci
                              }).ToList();

this will new up your Payment object using a parameterless constructor, and then initialize the properties that are listed inside the curly braces { }

Answer 11

Also, if you want to use a constructor with multiple objects to initialize, you might get error if no values are returned by Linq.

So you might want to do something like this:

(from x in table_1
   join y in table_2
   on x.id equals y.id
   select new {
   val1 = x,
   val2 = y
})
.DefaultIfEmpty()
.ToList()
.Select(a => new Val_Constructor(a.val1 != null ? a.val1 : new Val_1_Constructor(),
                            a.val2 != null ? a.val2 : new Val_2_Constructor()))
.ToList();

Answer 12

Just use ToList() before the Select statement.. something like that:

myDbSet.Where(x => something that minimizes the number of items).ToList().Select(...)

The actual DbSet is not out of the db yet. After calling ToList() you're playing with objects, and then you can use a non-default constructor in the query.

Not the most efficient way usage-time wise, but it's an option on small sets.

Answer 13

Although it is late to answer, it could still help someone in distress. Since LINQ to entities do not support the parameter-less object constructions. However, the projection methods for IEnumerable.

So prior to selection, just convert your IQueryable to IEnumerable by using this code:

var result = myContext.SomeModelClass.AsEnumerable().Select(m => m.ToString());

It will work fine. However, it will, of course, loose the benefits of native queries.

Answer 14

IQueryable<SqlResult> naleznosci = (from nalTmp in db.Naleznosci
                              where nalTmp.idDziecko == idDziec
                              select new Payments
                              {
                                  Imie = nalTmp.Dziecko.Imie,
                                  Nazwisko = nalTmp.Dziecko.Nazwisko,
                                  Nazwa= nalTmp.Miesiace.Nazwa,
                                  Kwota = nalTmp.Kwota,
                                  NazwaRodzajuOplaty =                          nalTmp.RodzajeOplat.NazwaRodzajuOplaty,
                              NazwaTypuOplaty = nalTmp.RodzajeOplat.TypyOplat.NazwaTypuOplaty,
                              DataRozliczenia = nalTmp.DataRozliczenia,
                              TerminPlatnosci = nalTmp.TerminPlatnosci,
                          });
Repeater1.DataSource  = naleznosci.ToList(); 
Repeater1.DataBind();


public class SqlResult
{
        public string Imie { get; set; }
        public string Nazwisko { get; set; }
        ...
}