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 — Henrik Wilshusen
– Henrik Wilshusen, Commented Aug 9, 2018 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
– Panagiotis Kanavos, Commented Aug 9, 2018 at 15:56
@PanagiotisKanavos Do you want me to paste the stack trace ? And the classes like Prestations and Partenaire ? — Pierrick Martellière
– Pierrick Martellière, Commented Aug 9, 2018 at 16:04

Draken · Accepted Answer · 2018-08-09 16:48:01Z

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 9, 2018 at 16:48

Draken

3,19614 gold badges38 silver badges56 bronze badges

answered Aug 9, 2018 at 16:27

Aldert

4,3631 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Pierrick Martellière Over a year ago

If I understand you well, I should put a constructor without parameters in my PartenaireMapItem class ?

Aldert Over a year ago

If that is the class you use with your entity framework as data holder than yes..

Pierrick Martellière Over a year ago

I added a parameterless ctor to my PartenaireMapItem class but nothing changed, all the other classes already have parameterless ctors... What am I missing ?

Aldert Over a year ago

I am going to test your class, give me some time

Aldert Over a year ago

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

|

Russell Jonakin · Accepted Answer · 2018-08-09 22:11:33Z

2

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();

answered Aug 9, 2018 at 22:11

Russell Jonakin

2,06619 silver badges19 bronze badges

1 Comment

Pierrick Martellière Over a year ago

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 ?

Ivan Stoev · Accepted Answer · 2018-08-10 00:17:01Z

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, 2018 at 0:17

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

5 Comments

Pierrick Martellière Over a year ago

Tried this, but the method is not returning anymore.

Ivan Stoev Over a year ago

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()

Pierrick Martellière Over a year ago

adding AsEnumerable after Where() made the exception disappear, but now I need to activeate Multiple Result Set, see stackoverflow.com/questions/43264526/…

Ivan Stoev Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

10 Comments

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related