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;
}
}
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.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow