How to assign value from properties to enum in Linq

Question

As a result of the linq query. CalcMethod and WalkingLineMeasure properties are Enum - I try to parse string values from database but get an exception:

System.NotSupportedException: LINQ to Entities does not recognize the method 'AppForSellers.Models.BusinessLogic.SpiralStairs.Models.DiamDivide ParseDiamDivide' method, and this method cannot be translated into a store expression.

 public List<StandardEnt> ListStandardsWithMethodList()
        {
            var listModel =
                (
                from dane in _dbContext.Standard
                join kraj in _dbContext.CountryStandard
                on dane.IdCountryStandard equals kraj.IdCountryStandard
                join opcja in _dbContext.MeasureDiameterOption
                on dane.IdStandard equals opcja.IdStandard
                join metoda in _dbContext.MeasureDiameterMethod
                on opcja.IdMeasureDiameterMethod equals metoda.IdMeasureDiameterMethod
                join sposob in _dbContext.WalkingLineRadius
                on opcja.IdWalkingLineRadius equals sposob.IdWalkingLineRadius
                join linia in _dbContext.CalcMethodType
                on sposob.IdCalcMethodType equals linia.IdCalcMethodType
                join wyznaczanie in _dbContext.WalkingLineMeasure
                on sposob.IdWalkingLineMeasure equals wyznaczanie.IdWalkingLineMeasure
                select new StandardEnt()
                {
                    IdStandard = dane.IdStandard,
                    IdCountryStandard = dane.IdCountryStandard,
                    StandardName = dane.StandardName,
                    MaxStepDeep = (double)dane.MaxStepDeep,
                    MinStepDeep = (double)dane.MinStepDeep,
                    MaxStepWidth = (double)dane.MaxStepWidth,
                    WidthLimit = (double)dane.WidthLimit,
                    StartOverlap = (double)dane.StartOverlap,
                    InternalHandrailMaxRadius = (double)dane.InternalHandrailMaxRadius,
                    PrimaryCalcMethod = (from metody in _dbContext.MeasureDiameterOption
                                         where dane.IdStandard == metody.IdStandard && metody.MeasureDiameterMethod.CalcMethodName == "PrimaryCalcMethod"
                                         select new MeasureDiameterMethodEnt()
                                         {
                                             IdMeasureDiameterOption = opcja.IdMeasureDiameterOption,
                                             Description = opcja.Description,
                                             IdMeasureDiameterMethod = metoda.IdMeasureDiameterMethod,
                                             IdWalkingLineRadius = sposob.IdWalkingLineRadius,
                                             IdCalcMethodType = linia.IdCalcMethodType,
                                             IdWalkingLineMeasure = wyznaczanie.IdWalkingLineMeasure,
                                             CalcMethod = Enum.Parse<DiamDivide>(linia.CalcMethodNameType),
                                             WalkingLineMeasure = Enum.Parse<WalkingLineMeasureMethod>(wyznaczanie.WalkingLineMeasureName),
                                             CalcMethodName = metoda.CalcMethodName,
                                             FirstWidth = (double)sposob.FirstWidth,
                                             SecondWidth = (double)sposob.SecondWidth
                                         }).FirstOrDefault(),
                    SecondaryCalcMethod = (from metody in _dbContext.MeasureDiameterOption
                                           where dane.IdStandard == metody.IdStandard && metody.MeasureDiameterMethod.CalcMethodName == "SecondaryCalcMethod"
                                           select new MeasureDiameterMethodEnt()
                                           {
                                               IdMeasureDiameterOption = opcja.IdMeasureDiameterOption,
                                               Description = opcja.Description,
                                               IdMeasureDiameterMethod = metoda.IdMeasureDiameterMethod,
                                               IdWalkingLineRadius = sposob.IdWalkingLineRadius,
                                               IdCalcMethodType = linia.IdCalcMethodType,
                                               IdWalkingLineMeasure = wyznaczanie.IdWalkingLineMeasure,
                                               CalcMethod = Enum.Parse<DiamDivide>(linia.CalcMethodNameType),
                                               WalkingLineMeasure = Enum.Parse<WalkingLineMeasureMethod>(wyznaczanie.WalkingLineMeasureName),
                                               CalcMethodName = metoda.CalcMethodName,
                                               FirstWidth = (double)sposob.FirstWidth,
                                               SecondWidth = (double)sposob.SecondWidth
                                           }).FirstOrDefault()
                }
            ).ToList();
            return listModel;
        }

I don't know or understand well. First you need to do this query but assign to the string property not to Enum, then parse to enum and then assign to enum? Can you give me an example? — Przemysław Szkaradek, Mar 18 '21 at 16:32
Because database does not know about your enums that's why it cannot convert them. First you have to fetch those columns as string and then apply the enum conversion in memory. If you call the ToList/ToArray then you materialize your query result in memory, so after that you can convert them to enums. — Peter Csala, Mar 18 '21 at 17:13
@PrzemysławSzkaradek I've left a post where I've demonstrated how to apply that technique to your problem. — Peter Csala, Mar 19 '21 at 08:48

Peter Csala · Accepted Answer · 2021-03-20T06:37:53.817

One way to solve this problem:

Query the data as it is stored in database
1.1) I assumed CalcMethodNameType, WalkingLineMeasureName can be treated as string
Map the retrieved data in-memory to the desired shape

Database query

var databaseQuery =
    from dane in _dbContext.Standard
    join opcja in _dbContext.MeasureDiameterOption on dane.IdStandard equals opcja.IdStandard
    join metoda in _dbContext.MeasureDiameterMethod on opcja.IdMeasureDiameterMethod equals metoda.IdMeasureDiameterMethod
    join sposob in _dbContext.WalkingLineRadius on opcja.IdWalkingLineRadius equals sposob.IdWalkingLineRadius
    join linia in _dbContext.CalcMethodType on sposob.IdCalcMethodType equals linia.IdCalcMethodType
    join wyznaczanie in _dbContext.WalkingLineMeasure on sposob.IdWalkingLineMeasure equals wyznaczanie.IdWalkingLineMeasure
    select new
    {
        dane.IdStandard,
        dane.IdCountryStandard,
        dane.StandardName,
        dane.MaxStepDeep,
        dane.MinStepDeep,
        dane.MaxStepWidth,
        dane.WidthLimit,
        dane.StartOverlap,
        dane.InternalHandrailMaxRadius,
        PrimaryCalcMethod =
            (from metody in _dbContext.MeasureDiameterOption
             where dane.IdStandard == metody.IdStandard && metody.MeasureDiameterMethod.CalcMethodName == "PrimaryCalcMethod"
             select new
             {
                 opcja.IdMeasureDiameterOption,
                 opcja.Description,
                 metoda.IdMeasureDiameterMethod,
                 sposob.IdWalkingLineRadius,
                 linia.IdCalcMethodType,
                 wyznaczanie.IdWalkingLineMeasure,
                 linia.CalcMethodNameType,
                 wyznaczanie.WalkingLineMeasureName,
                 metoda.CalcMethodName,
                 sposob.FirstWidth,
                 sposob.SecondWidth
             }).FirstOrDefault(),
        SecondaryCalcMethod =
            (from metody in _dbContext.MeasureDiameterOption
             where dane.IdStandard == metody.IdStandard && metody.MeasureDiameterMethod.CalcMethodName == "SecondaryCalcMethod"
             select new
             {
                 opcja.IdMeasureDiameterOption,
                 opcja.Description,
                 metoda.IdMeasureDiameterMethod,
                 sposob.IdWalkingLineRadius,
                 linia.IdCalcMethodType,
                 wyznaczanie.IdWalkingLineMeasure,
                 linia.CalcMethodNameType,
                 wyznaczanie.WalkingLineMeasureName,
                 metoda.CalcMethodName,
                 sposob.FirstWidth,
                 sposob.SecondWidth
             }).FirstOrDefault()
    };

I've removed the join clause with CountryStandard, because it seemed that it was not in use
I've used anonymous types to represent the retrieved data
- Because the column names and the property names are matching that's why I could shorten the select clause
- For example instead of IdCountryStandard = dane.IdCountryStandard I could use dane.IdCountryStandard

Result mapping

var inMemoryMapping =
    from row in databaseQuery.AsEnumerable()
    let primary = row.PrimaryCalcMethod
    let secondary = row.SecondaryCalcMethod
    select new StandardEnt
    {
        IdStandard = row.IdStandard,
        IdCountryStandard = row.IdCountryStandard,
        StandardName = row.StandardName,
        MaxStepDeep = (double)row.MaxStepDeep,
        MinStepDeep = (double)row.MinStepDeep,
        MaxStepWidth = (double)row.MaxStepWidth,
        WidthLimit = (double)row.WidthLimit,
        StartOverlap = (double)row.StartOverlap,
        InternalHandrailMaxRadius = (double)row.InternalHandrailMaxRadius,
        PrimaryCalcMethod = new MeasureDiameterMethodEnt
        {
            IdMeasureDiameterOption = primary.IdMeasureDiameterOption,
            Description = primary.Description,
            IdMeasureDiameterMethod = primary.IdMeasureDiameterMethod,
            IdWalkingLineRadius = primary.IdWalkingLineRadius,
            IdCalcMethodType = primary.IdCalcMethodType,
            IdWalkingLineMeasure = primary.IdWalkingLineMeasure,
            CalcMethod = Enum.Parse<DiamDivide>(primary.CalcMethodNameType),
            WalkingLineMeasure = Enum.Parse<WalkingLineMeasureMethod>(primary.WalkingLineMeasureName),
            CalcMethodName = primary.CalcMethodName,
            FirstWidth = (double)primary.FirstWidth,
            SecondWidth = (double)primary.SecondWidth
        },
        SecondaryCalcMethod = new MeasureDiameterMethodEnt
        {
            IdMeasureDiameterOption = secondary.IdMeasureDiameterOption,
            Description = secondary.Description,
            IdMeasureDiameterMethod = secondary.IdMeasureDiameterMethod,
            IdWalkingLineRadius = secondary.IdWalkingLineRadius,
            IdCalcMethodType = secondary.IdCalcMethodType,
            IdWalkingLineMeasure = secondary.IdWalkingLineMeasure,
            CalcMethod = Enum.Parse<DiamDivide>(secondary.CalcMethodNameType),
            WalkingLineMeasure = Enum.Parse<WalkingLineMeasureMethod>(secondary.WalkingLineMeasureName),
            CalcMethodName = secondary.CalcMethodName,
            FirstWidth = (double)secondary.FirstWidth,
            SecondWidth = (double)secondary.SecondWidth
        }
    };

return inMemoryMapping.ToList();

I've used AsEnumarble (as it was suggested by NetMage)
- Related SO topic
All the type conversions have done in-memory

I was not able to test this solution. Please let me know if there is some issue.

How to assign value from properties to enum in Linq

1 Answers1

Database query

Result mapping