locked
NotSupportedException: Unable to create a constant value of type... Only primitive types or enumeration types are supported in this context. RRS feed

  • Question

  • Hello,

    I have a link statement that's throwing a NotSupportedException with the following message:

    Unable to create a constant value of type 'ACM.RiskAlive.Core.Data.AverageSafeRecProfile'. Only primitive types or enumeration types are supported in this context.

    Here's my code:

    var facilityTypeId = _context.Projects.Where(x => x.ProjectId == projectId).Select(x => x.Facility.TypeCodeId).First();

    var consq = projectIdsForAverage == null
                    ? _context.ConsequencesByProject(x => x.ProjectId != projectId && x.Facility.TypeCodeId == facilityTypeId)
                    : _context.ConsequencesByProject(x => projectIdsForAverage.Contains(x.ProjectId));

    var recs = _context.GetAverageRecommendationProfile(projectId, projectIdsForAverage.ToCommaSeperated());

    try {
    var dto = consq.Select(x => new
            {
            ClientCode = x.Category.HazopCode,
                    ChartLabel = x.Category.Label,
                    ClientColor = x.Category.Color,
                    Category = x.Category.DataMiningCode.Name,
                    Color = x.Category.DataMiningCode.Color,
                    Credit = recs.Where(r => r.ConsequenceId == x.ConsequenceId).Sum(r => r.NonLowCreditTotal)
                    })
                        .Where(x => x.Category != null && x.ClientCode != null)
                        .GroupBy(x => new { x.Category, x.Color })
                        .Select(x => new ChartDataDto
                        {
                            Label = x.Key.Category,
                            Color = x.Key.Color,
                            Value = x.Sum(y => y.Credit)
                        });

                    return dto.ToList();
            }
            catch (Exception e)
            {
                    Type t = e.GetType();
                    return null;
            }

    Essentially, I have two sets of data: consequences (consq) and recommendations (recs). In the linq statement which I assign to dto, I create a new object out of the consequences in the first Select statement, and I assign the Credit field of the new object to the sum of the NonLowCreditTotal found in those recommendations where the consequence ID matches the consequence ID of the current consequence. Then I try to convert dto to a List and that's when the exception is thrown.

    Can anyone see what I'm doing wrong. Thank you.
    • Moved by CoolDadTx Friday, July 22, 2016 1:57 PM EF related
    Thursday, July 21, 2016 5:40 PM

All replies

  • Unable to create a constant value of type 'ACM.RiskAlive.Core.Data.AverageSafeRecProfile'. Only primitive types or enumeration types are supported in this context.

    It's telling you what is wrong Linq can't do it, and you can't use type 'ACM.RiskAlive.Core.Data.AverageSafeRecProfile' in the Linq query. You can use  primitive types like int, string, double,  or something that has a enumeration than can be enumerated over like a collection etc, etc.

    Thursday, July 21, 2016 6:09 PM
  • Thanks for the reply DA924x,

    I tried a few ways to make recs enumerable:

    var recs = _context.GetAverageRecommendationProfile(projectId, projectIdsForAverage.ToCommaSeperated()).AsEnumerable();

    var recs = _context.GetAverageRecommendationProfile(projectId, projectIdsForAverage.ToCommaSeperated()).ToList();

    var recs = _context.GetAverageRecommendationProfile(projectId, projectIdsForAverage.ToCommaSeperated()).ToArray();

    But those don't seem to work.

    I'm not sure how I can convert recs into a list of primitives since I need both the consequenceId and the NonLowCreditTotal fields.

    Thursday, July 21, 2016 6:48 PM
  • You would have to put whatever the recs are into a List<T> within the called method,  and the called method returns the List<T> out of the method. You putting ToList() at the end means nothing if the method is not making a List<T> within the method itself and returning it.
    Thursday, July 21, 2016 7:12 PM
  • Hmm... Here's what I tried:

    I took the method _context.GetAverageRecommendationProfile(...) which looks like this:

       

            public virtual IEnumerable<AverageSafeRecProfile> GetAverageRecommendationProfile(Nullable<int> ref

    erenceProjectId, string projectsForAvg)
            {
                var referenceProjectIdParameter = referenceProjectId.HasValue ?
                    new ObjectParameter("referenceProjectId", referenceProjectId) :
                    new ObjectParameter("referenceProjectId", typeof(int));

                var projectsForAvgParameter = projectsForAvg != null ?
                    new ObjectParameter("projectsForAvg", projectsForAvg) :
                    new ObjectParameter("projectsForAvg", typeof(string));

                return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<AverageSafeRecProfile>("GetAverageRecommendationProfile", referenceProjectIdParameter, projectsForAvgParameter);
            }

    ...and created a new method called _context.GetAverageRecommendationProfileList(...) which looks like this:

            public virtual List<AverageSafeRecProfile> GetAverageRecommendationProfileList(Nullable<int> referenceProjectId, string projectsForAvg)
            {
                var referenceProjectIdParameter = referenceProjectId.HasValue ?
                    new ObjectParameter("referenceProjectId", referenceProjectId) :
                    new ObjectParameter("referenceProjectId", typeof(int));

                var projectsForAvgParameter = projectsForAvg != null ?
                    new ObjectParameter("projectsForAvg", projectsForAvg) :
                    new ObjectParameter("projectsForAvg", typeof(string));

                IEnumerable<AverageSafeRecProfile> temp = ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<AverageSafeRecProfile>("GetAverageRecommendationProfile", referenceProjectIdParameter, projectsForAvgParameter);

                List<AverageSafeRecProfile> result = new List<AverageSafeRecProfile>();
                foreach (AverageSafeRecProfile avgSRProfile in temp)
                {
                    result.Add(avgSRProfile);
                }

                return result;
            }

    Both methods call the stored procedure GetAverageRecommendationProfile and stores the results in a collection of AverageSafeRecProfiles. The first one returns them as IEnumerable<AverageSafeRecProfile> and the second (new) one adds them to a List<AverageSafeRecProfile> and returns them as List<AverageSafeRecProfile>.

    Back at the original method, I changed this:

    var recs = _context.GetAverageRecommendationProfile(projectId, projectIdsForAverage.ToCommaSeperated());

    ...to this:

    List<AverageSafeRecProfile> recs = _context.GetAverageRecommendationProfileList(projectId, projectIdsForAverage.ToCommaSeperated());

    So there's no question now that recs is a List of AverageSafeRecProfiles which is enumerable. But I still get the same exception being thrown.

    Also, if GetAverageRecommendationProfile(...) was returning an IEnumerable<AverageSafeRecProfile>, shouldn't that mean it was already enumerable?

    Thanks again for your help.


    Thursday, July 21, 2016 8:24 PM
  • What are you using here the ADO.NET Entity Framework or what in this Linq query?

    Thursday, July 21, 2016 8:50 PM
  • Yes, I'm using EntityFramework.SqlServer v6.0, runtime version 4.0.30319.
    Thursday, July 21, 2016 9:05 PM
  • Yes, I'm using EntityFramework.SqlServer v6.0, runtime version 4.0.30319.

    Well then what is happening is that the EF engine cannot make the T-SQL based on the Linq query to submit for execution to the DB engine,  and EF can't support it the Linq query you have formulated.

    Your other alternative here is to use Entity SQL to query the conceptual virtual model, which is much like using T-SQL with a ADO.NET Datareader. E-SQL has all the power of T-SQL, but all you can do is query and return results. Your final results would be to return the DTO(s) out of the method that is using ESQL. 

    https://msdn.microsoft.com/en-us/library/bb387145%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

    https://msdn.microsoft.com/en-us/library/bb399359(v=vs.110).aspx

    https://msdn.microsoft.com/en-us/library/bb399764%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

    https://msdn.microsoft.com/en-us/library/bb738684(v=vs.100).aspx

    This last link is showing how to obtain the EntityConnection.

    https://blogs.msdn.microsoft.com/alexj/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/

    You can bet that I have had to use Entity SQL when Linq and EF couldn't do it.

    Thursday, July 21, 2016 9:43 PM
  • Ok, thanks DA. I'll have a read through those.
    Thursday, July 21, 2016 10:49 PM
  • Have you received errors when you appended .ToList() to ‘var consq = …’ and ‘var recs = …’ (before achieving the dto query)?

    Also try commenting some parts of dto query in order to localise the problem.

    Maybe also show some details about AverageSafeRecProfile.


    • Edited by Viorel_MVP Friday, July 22, 2016 6:00 AM
    Friday, July 22, 2016 5:55 AM
  • Have you received errors when you appended .ToList() to ‘var consq = …’ and ‘var recs = …’ (before achieving the dto query)?

    Also try commenting some parts of dto query in order to localise the problem.

    Maybe also show some details about AverageSafeRecProfile.


    I have not received any errors on the execution of the ToList() call on those collections directly.

    However, if I call ToList() on consq I get a different error on dto.ToList():

    Calling ToList() on consq without calling ToList() on recs gives me an InvalidOperationException with the message:

    "The result of a query cannot be enumerated more than once."

    Calling ToList() on both consq and recs gives me a NullReferenceException with the message:

    "Object reference not set to an instance of an object."

    Not sure what object is null (it's not consq, recs, or dto <-- probably something inside dto).

    I have already localized the source of the error (the original one) to:

    Credit = recs.Where(r => r.ConsequenceId == x.ConsequenceId).Sum(r => r.NonLowCreditTotal)

    AverageSafeRecProfile is an auto-generated Complex Type class produced by our database model. It was generated from a stored procedure through a Function Import. It looks like this:

        public partial class AverageSafeRecProfile
        {
            public string SafeRecCategory { get; set; }
            public string SafeRecColor { get; set; }
            public string SafeRecClientColor { get; set; }
            public string CauseCategory { get; set; }
            public string ConsequenceCategory { get; set; }
            public string ConsequenceColor { get; set; }
            public int ConsequenceId { get; set; }
            public Nullable<decimal> TotalCredit { get; set; }
            public Nullable<decimal> NonLowCreditTotal { get; set; }
            public Nullable<decimal> HighCreditTotal { get; set; }
        }

    and implements:

        public interface ISafeRecCauseTotalCredit
        {
            string SafeRecCategory { get; }
            string SafeRecColor { get; }
            string SafeRecClientColor { get; }
            string CauseCategory { get; }
            Nullable<decimal> TotalCredit { get; }
            Nullable<decimal> NonLowCreditTotal { get; }
        }



    Friday, July 22, 2016 3:23 PM
  • I have not received any errors on the execution of the ToList() call on those collections directly.

    However, if I call ToList() on consq I get a different error on dto.ToList():

    Calling ToList() on consq without calling ToList() on recs gives me an InvalidOperationException with the message:

    "The result of a query cannot be enumerated more than once."

    You sliver bullet here is ESQL. Maybe, it's time to start thinking about peeling yourself off the wall and move past it.

    You can also post to the forum below.

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

    Friday, July 22, 2016 4:15 PM