Not supported exception when running lambda on data model in C#

Question

So I'm building an event calendar that has recurring events. In order to capture recurring events, I've gone with a bit-shifting approach to store the recurring information. It's not too important how or why I took this approach, but I'm getting a not-supported exception when running the bit-shifting code inside my lambdas.

Is there an obvious reason here why I'm getting a not supported exception?

ab = db.Availabilities.Where(s => (((s.day_cycle_bitmask >> (fromDate.AddDays(i).Subtract(s.start_date).Days % s.num_days_in_cycle)) & 1) == 1
                                                        || ((s.eve_cycle_bitmask >> (fromDate.AddDays(i).Subtract(s.start_date).Days % s.num_days_in_cycle)) & 1) == 1
                                                        || ((s.night_cycle_bitmask >> (fromDate.AddDays(i).Subtract(s.start_date).Days % s.num_days_in_cycle)) & 1) == 1
                                                        ) && s.employee_id_fk == employee.employee_id_pk
                                                        ).ToList();

Edit - Adding my bit-shift by division code, because that code definitely works in TSQL

//ab = d

b.Availabilities.Where(s => (s.night_cycle_bitmask /
                    //                                         (int)Math.Pow(fromDate.AddDays(i).Subtract(s.start_date).TotalDays % s.num_days_in_cycle, 2) & 1) == 1
                    //                                         || (s.day_cycle_bitmask / (int)Math.Pow(fromDate.AddDays(i).Subtract(s.start_date).Days % s.num_days_in_cycle, 2) & 1) == 1
                    //                                         || (s.eve_cycle_bitmask / (int)Math.Pow(fromDate.AddDays(i).Subtract(s.start_date).Days % s.num_days_in_cycle, 2) & 1) == 1
                    //                                         && s.employee_id_fk == employee.employee_id_pk).ToList();

Here are the exception details:

Exception thrown: 'System.NotSupportedException' in EntityFramework.dll
Exception thrown: 'System.NotSupportedException' in EntityFramework.dll
Exception thrown: 'System.NotSupportedException' in EntityFramework.SqlServer.dll
'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/3/ROOT-1-131260520212908911): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.Debugger.Runtime\14.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.Debugger.Runtime.dll'. 
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.NotSupportedTranslator.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.BitwiseBinaryTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.EqualsTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.BinaryTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.BinaryTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.BinaryTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert()
   at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at RCHHRATool.Controllers.AvailabilitiesController.GetEvents(String start, String end) in C:\Users\Sean\Documents\Visual Studio 2015\Projects\RCHHRATool\RCHHRATool\Controllers\AvailabilitiesController.cs:line 170

SQL doesn't support bit-shifting operations. It's attempting to translate the lambda to SQL, but can't. So you get the exception. — , Dec 12 '16 at 21:29
What is the full text of the exception? I suspect it gives more detail on your problem... — Chris, Dec 12 '16 at 21:30
In this case the approach you took IS important. There is no translation to SQL for that operator, hence the problem. — JuanR, Dec 12 '16 at 21:35
For the right shift? Yeah that's not the problem because I was previously using a division instead of the right shift operator, and it was throwing the same exception. The division DOES work in TSQL, as I have a working TSQL query that's my prototype for this function. — Scuba Steve, Dec 12 '16 at 21:37
It doesn't matter if it works in TSQL. What matters is that the expression translator is able to take your code and turn it into SQL. If it's not supported by the translator, you are out of luck. — JuanR, Dec 12 '16 at 21:39
I hear you. I may try to use raw SQL. I think that may solve my problem. — Scuba Steve, Dec 12 '16 at 21:44
Negative. See comments to most upvoted answer below. You can implement this with stored procs and function imports. — JuanR, Dec 12 '16 at 21:53

score 7 · Accepted Answer · edited May 23 '17 at 12:34

7

You are running your right-shift (>>) operator on an entity framework query. Since the query is not executed in the CLI, but is translated to SQL and run on the DB, you are not free to use just any operator in your query. And the right-shift operator is not translatable to SQL by EF. The same would apply to the equivalent power-of-two division.

You could get the data from the DB and perform the shift in memory, but you are using the shifts in the Where clause, which is always executed on the DB. So I am afraid your bit-shifting approach won't work with EF.

UPDATE:

As pointed out by @Juan, you could transfer your entire query to a stored procedure and call it from EF. You might want to check out this Q&A for the details on how to perform bit-shifts in SQL.

edited May 23 '17 at 12:34

Community

1
1

answered Dec 12 '16 at 21:32

Sefe

13,731
5
42
55

2

Just to add to this answer, an alternative approach would be to use a stored procedure and map it to an imported function. You can then perform your bit shifting on the SQL server side and get the desired results. Check this out: http://stackoverflow.com/questions/1110155/bitshift-operations-in-t-sql – JuanR Dec 12 '16 at 21:36
@Juan: That's correct, you OK if I add it to the reply? – Sefe Dec 12 '16 at 21:38
See my edit above, the bit-shift operator was added to simplify the code, but I'm still getting the exception with the commented out code. – Scuba Steve Dec 12 '16 at 21:40
Using a raw SQL query works. Is there a particular advantage to using a stored procedure over just using raw SQL? – Scuba Steve Dec 12 '16 at 22:08
@ScubaSteve: You can run a raw SQL query, but with EF you add a layer of abstraction between the entities in your code and the tables in your DB. This is done in the mappings. Raw SQL queries have the disadvantage that you break this abstraction. Changes in your DB might break your query and you only realize at runtime. I don't use raw SQL by general principle and most of my DB changes only affect the mappings. So I see what breaks immediately (it won't compile). It's a nightmare to search through scattered raw queries that might have broken. – Sefe Dec 12 '16 at 22:18
1

@ScubaSteve: My rule of thumb that I live pretty well with is that raw SQL belongs to the DB, not to your EF code. – Sefe Dec 12 '16 at 22:25
This one instance is kindof a special case. I definitely prefer the lambda expression for pretty much anything else. I'm more concerned with performance. We're also on a pretty small budget, so I'm trying to assess if it's worth my time to learn to use stored procedures for this one thing I need them for (for this project). – Scuba Steve Dec 12 '16 at 22:41

Andrés Robinet · Answer 2 · 2016-12-12T21:46:37.723

1

Though this is not a complete solution, it may help you:

First, define the table-valued functions for shiftLeft, shiftRight, etc

Then, you can follow this approach to add support for store functions

It may or may not be beneficial in your case, but if you have to live with legacy code, it might be worth giving it a shot

CodeFirstFunctions Github repo

edited Dec 12 '16 at 21:46

answered Dec 12 '16 at 21:41

Andrés Robinet

1,527
12
18

Not supported exception when running lambda on data model in C#

2 Answers2