Unable to create a constant value of type 'System.Tuple

c# entity-framework entity-framework-6 linq

Question

I am trying to implement c# linq expression that will allow me to extract data based on years and months.The error is thrown in the where method of my context.

I am currently getting the error

System.NotSupportedException: 'Unable to create a constant value of type 'System.Tuple`2[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'. Only primitive types or enumeration types are supported in this context.'

Linq expression

var periodTuples = period.ToList() // gives you List<KeyValuePair<int, int[]>>
                .SelectMany(kvp =>
                    kvp.Value.Select(it2 => Tuple.Create(kvp.Key, it2))).ToHashSet(); // to optimize .Contains()


benchmark1Returns = GetViewService<MV_INDEX_PERFORMANCE>()
                .Where(x => x.Mtd != null && x.IndexId == benchMark1 && periodTuples.Contains(Tuple.Create(x.PriceDate.Year, x.PriceDate.Month))).Select(x => x.Mtd);

The Where method define in my context

public IEnumerable<T> Where(Expression<Func<T, bool>> predicate)
        {
            try
            {
                using (new TimedLogger(_perfLogger, GetCompletedText("Where")))
                {
                    return Authorize(_repo.Where(predicate).ToList(), AuthAccessLevel.Read);
                }
            }
            catch (Exception ex)
            {
                _logger.Error(ex);
                throw;
            }
        }
1
1
4/24/2019 8:00:56 AM

Popular Answer

EF6 does not support (cannot translate to SQL) Tuple.Create (and Tuple<> constructors), in-memory collection joins, Contains of in-memory collection on non primitive types and many others.

The solution in your case is to replace (int year, int month) pair with primitive type expression like this:

int period = 12 * year + (month - 1)

and use it for filtering, e.g.

var periods = period.ToList() // gives you List<KeyValuePair<int, int[]>>
    .SelectMany(kvp => kvp.Value, (year, month) => 12 * year + (month - 1));

and then inside the LINQ query

periods.Contains(12 * x.PriceDate.Year + (x.PriceDate.Month - 1))

Note that in LINQ to Entities query periods doesn't need to be HashSet<int> (or List<int> etc.) - IEnumerable<int> is enough because it will be evaluated locally once and converted to SQL IN (val1, val2, ..., valN) criteria.

1
4/24/2019 8:27:03 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow