ASP.NET - Entity Framework - Only parameterless constructors and initializers are supported in LINQ to Entities

Question

I'm new to asp.net MVC4 and EntityFramework.

I'm getting this error when trying to add a new method to my API:

Only parameterless constructors and initializers are supported in LINQ to Entities.

Here's my method:

// GET: api/Partenaires_prestations
        [Authorize]
        [Route("api/Partenaires_prestations")]
        public List<PartenaireMapItem> GetPartenairesWithPrestations()
        {
            Random rnd = new Random();

            var queryString = Request.GetQueryNameValuePairs();

            var prestation = queryString.FirstOrDefault();

            return db.Partenaires
                .Where(p => p.PartenairePrestations.Any(pp => pp.Prestation.NomPrestation == prestation.Value))
                .Select(p => new PartenaireMapItem {
                    IdPartenaire = p.IdPartenaire,
                    FirstName = p.FirstName,
                    LastName = p.LastName,
                    NomComplet = p.LastName.Substring(0,1).ToUpper() + ". " + p.FirstName,
                    Type = p.Type,
                    DureeMin = 50,
                    Lat = p.Lat,
                    Lng = p.Lng,
                    ImageUrl = p.ImageUrl,
                    SeDeplace = p.SeDeplace,
                    ADomicile = p.ADomicile,

                    NoteGlobale = p.NoteClientPartenaires.Sum(x => ((double)(x.NoteAimabilite + x.NotePonctualite +
                                             x.NoteProprete + x.NoteQualite)) / 4) / p.NoteClientPartenaires.Count,
                    Prestations = new List<string>(p.PartenairePrestations.Select(y => y.Prestation.NomPrestation))
                }).ToList();
}

Here's my PartenaireMapItem class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Uphair.EfModel;

namespace Uphair.Api.Models.Partenaire
{
    public class PartenaireMapItem
    {
        public int IdPartenaire { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string NomComplet { get; set; }
        public double? Lat { get; set; }
        public double? Lng { get; set; }
        public PartenaireType Type { get; set; }

        public int DureeMin { get; set; }

        public string ImageUrl { get; set; }

        public int SeDeplace { get; set; }

        public bool ADomicile { get; set; }

        public double NoteGlobale { get; set; }

        public ICollection<NoteClientPartenaire> Notes { get; set; }


        public List<String> Prestations { get; set; } 
    }
}

Here's the stacktrace:

"{"Message":"An error has occurred.","ExceptionMessage":"Only parameterless constructors and initializers are supported in LINQ to Entities.","ExceptionType":"System.NotSupportedException","StackTrace":" at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.NewTranslator.TypedTranslate(ExpressionConverter parent, NewExpression linq)\r\n at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)\r\n at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MemberInitTranslator.TypedTranslate(ExpressionConverter parent, MemberInitExpression linq)\r\n at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)\r\n at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)\r\n at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)\r\n at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)\r\n at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)\r\n at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert()\r\n at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable1 forMergeOption)\r\n at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass7.b__6()\r\n at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)\r\n at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass7.b__5()\r\n at System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption)\r\n at System.Data.Entity.Core.Objects.ObjectQuery1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()\r\n at System.Data.Entity.Internal.LazyEnumerator1.MoveNext()\r\n at System.Collections.Generic.List1..ctor(IEnumerable1 collection)\r\n at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)\r\n at Uphair.Api.Controllers.PartenairesController.GetPartenairesWithPrestations()\r\n at lambda_method(Closure , Object , Object[] )\r\n at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)\r\n at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary2 arguments, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter1.GetResult()\r\n at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter1.GetResult()\r\n at System.Web.Http.Controllers.ActionFilterResult.d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Filters.AuthorizationFilterAttribute.d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n at System.Web.Http.Dispatcher.HttpControllerDispatcher.d__1.MoveNext()"}"

Any help would be appreciated.

Thanks to anyone who will take the time to read/answer my post.

Possible duplicate of [LINQ to Entities does not recognize the method 'Char get\_Chars(Int32)' method, and this method cannot be translated into a store expression](https://stackoverflow.com/questions/12706160/linq-to-entities-does-not-recognize-the-method-char-get-charsint32-method-a) — Henrik Wilshusen, Aug 09 '18 at 15:47
@PierrickMartellière have you checked what the error says? Do any of the classes have constructors? What is the *actual, full* exception, including its call stack? You can get it easily using `Exception.ToString()` — Panagiotis Kanavos, Aug 09 '18 at 15:56
@PanagiotisKanavos Do you want me to paste the stack trace ? And the classes like ```Prestations``` and ```Partenaire``` ? — Pierrick Martellière, Aug 09 '18 at 16:04

score 2 · Answer 1 · edited Aug 09 '18 at 16:48

2

Entity Framework instantiates objects for you. To be able to do so, it expects you to have a constructor without parameters in your class. Check if your class has such a constructor (I do not see it).

Remember that you can have more than one constructor in your class.

edited Aug 09 '18 at 16:48

Draken

3,134
13
34
54

answered Aug 09 '18 at 16:27

Aldert

4,209
1
9
23

If I understand you well, I should put a constructor without parameters in my PartenaireMapItem class ? – Pierrick Martellière Aug 09 '18 at 16:31
If that is the class you use with your entity framework as data holder than yes.. – Aldert Aug 09 '18 at 16:33
I added a parameterless ctor to my PartenaireMapItem class but nothing changed, all the other classes already have parameterless ctors... What am I missing ? – Pierrick Martellière Aug 09 '18 at 16:55
I am going to test your class, give me some time – Aldert Aug 09 '18 at 16:58
When I only use the PartenaireMapItem class to create a DB table it gives me the message: no Key defined, this is correct on the class if you have use fluent api to do the mapping between class and DB. My assumptions are that you are much further in the project and you are able to store and retrieve induvidual objects. Am I correct? I am using EF6.0 – Aldert Aug 09 '18 at 17:10
Yup, perfectly correct. The model is correct and the API is currently working. – Pierrick Martellière Aug 10 '18 at 10:40
Can you also confirm you are using EF6 and can you past the fluent api code for your mapping. I can then use the same and see if i can reproduce. – Aldert Aug 10 '18 at 10:53
The answer from @JustSomeDude made the exception disappear, now apparently I must activate multiple result set, see https://stackoverflow.com/questions/43264526/entity-framework-there-is-already-an-open-datareader-associated-with-this-connec – Pierrick Martellière Aug 10 '18 at 10:55
Excellent you can move on! – Aldert Aug 10 '18 at 13:19
apparently according to @Stefan this was not a good solution, I'm now on this thread => https://stackoverflow.com/questions/51786737/asp-net-entityframework-why-my-request-fails-to-load-response-data?noredirect=1#comment90530765_51786737 – Pierrick Martellière Aug 10 '18 at 13:45

score 2 · Answer 2 · answered Aug 09 '18 at 22:11

Your exception has the important details to explain the problem here:

Only parameterless constructors and initializers are supported in LINQ to Entities.

To use the object initializer syntax like you are doing to get the values into the PartenaireMapItem object, you can use the .AsEnumerable() method after .Where(...). Using AsEnumerable() will allow you to use LINQ to Objects functionality.

return db.Partenaires
            .Where(p => p.PartenairePrestations.Any(pp => pp.Prestation.NomPrestation == prestation.Value))
            .AsEnumerable()
            .Select(p => new PartenaireMapItem {
                IdPartenaire = p.IdPartenaire,
                FirstName = p.FirstName,
                LastName = p.LastName,
                NomComplet = p.LastName.Substring(0,1).ToUpper() + ". " + p.FirstName,
                Type = p.Type,
                DureeMin = 50,
                Lat = p.Lat,
                Lng = p.Lng,
                ImageUrl = p.ImageUrl,
                SeDeplace = p.SeDeplace,
                ADomicile = p.ADomicile,

                NoteGlobale = p.NoteClientPartenaires.Sum(x => ((double)(x.NoteAimabilite + x.NotePonctualite +
                                         x.NoteProprete + x.NoteQualite)) / 4) / p.NoteClientPartenaires.Count,
                Prestations = new List<string>(p.PartenairePrestations.Select(y => y.Prestation.NomPrestation))
            }).ToList();

Seems to work like a charm, but I'm getting another error: "MySql.Data.MySqlClient.MySqlException: There is already an open DataReader associated with this Connection which must be closed first." Any clue ? — Pierrick Martellière, Aug 09 '18 at 22:28

score 0 · Answer 3 · answered Aug 10 '18 at 00:17

0

The exception is because here

Prestations = new List<string>(p.PartenairePrestations.Select(y => y.Prestation.NomPrestation))

you are using List<T> constructor with IEnumerable<T> parameter, and as the exception message indicates, LINQ to Entities allows only parameterless constructors, i.e. constructors without parameters.

Simply replace it with ToList extension method which is recognized, hence supported by EF query translator:

Prestations = p.PartenairePrestations.Select(y => y.Prestation.NomPrestation).ToList()

answered Aug 10 '18 at 00:17

Ivan Stoev

195,425
15
312
343

Tried this, but the method is not returning anymore. – Pierrick Martellière Aug 10 '18 at 10:39
This should be applied for your original L2E query from the question, right? Without `AsEnumerable()` which totally changes the execution context. And should execute single SQL which you can see if you take the LINQ query from `return …` and replace the *last* `ToList()` with `ToString()` – Ivan Stoev Aug 10 '18 at 10:45
adding AsEnumerable after Where() made the exception disappear, but now I need to activeate Multiple Result Set, see https://stackoverflow.com/questions/43264526/entity-framework-there-is-already-an-open-datareader-associated-with-this-connec – Pierrick Martellière Aug 10 '18 at 10:54
Adding `AsEnumerable()`? I didn't wanted to comment the other answers, but they are totally wrong in this particular scenario. My answer covers the cause of the exception in your question and how to avoid it. It doesn't require `AsEnumerable()`. The whole idea is to execute the whole thing server side. So use the query from the question, not from the currently accepted answer, and just modify the `Prestantions = …` as shown above. The exception should disappear. Issue solved. If you have new issues, probably you should post a new question :) – Ivan Stoev Aug 10 '18 at 11:00
You know what, actually your current issue with MARS is caused by the `AsEnumerable()` "fix", because as I said, the "fixed" query select is executing in different context (client side, LINQ to Objects), so it activates a lot of additional SQL queries due to lazy loading of navigation properties accessed, while still processing the main query, hence MARS. If you can execute the whole thing server side (by using LINQ to Entities compatible constructs) as I suggest, there won't be not supported or MARS related exceptions. – Ivan Stoev Aug 10 '18 at 11:13

ASP.NET - Entity Framework - Only parameterless constructors and initializers are supported in LINQ to Entities

3 Answers3