Filtering IQueryable using self defined function causes runtime error

Question

I had a similar query to LINQ: How to remove element from IQueryable<T> and for the most part it answered my question.

My code is set up similarly:

var items = MyDataContext.Items.Where(x =>MyFunction(x.value1, x.value2, x.value3));
...
...
bool MyFunction(decimal val1, decimal val2, decimal val3)
{
 //some calculation with the parameters
 return true;
}

It compiles fine, but when I run it, it throws an error:

"An exception of type 'System.NotSupportedException' occurred in System.Data.Entity.dll but was not handled in user code

Additional information: LINQ to Entities does not recognize the method 'Boolean MyFunction(System.Decimal, System.Decimal, System.Decimal)' method, and this method cannot be translated into a store expression."

I'm new to linq, only started this week, so any help is appreciated. Thanks!

You cannot write the same kind of Linq queries when querying a database. Either run `MyDataContext.Items.ToList().Where(..)` to forst get all `Items` from the database first and then run the where () bad performance. Otherwise rewrite your `Where` query to support [Linq To Sql](http://msdn.microsoft.com/en-us/library/bb425822.aspx) — Robert Fricke, Jul 01 '14 at 07:59
@RobertFricke, hi, i don't really understand the first option. U mean to get all the Items and then run another where() query? — mikevil14rin, Jul 01 '14 at 08:31

score 1 · Answer 1 · answered Jul 01 '14 at 07:58

1

Your linq query is translated into SQL. In this case Linq to Entities doesn't know how to translate your method into SQL correctly. So you can't use custom methods on linq to sql queries. You can only use supported methods

If you want to do that, you have to fetch all data from database and do that in the memory.

answered Jul 01 '14 at 07:58

Selman Genç

100,147
13
119
184

I suspected as much, but then I saw that http://stackoverflow.com/questions/2937528/linq-how-to-remove-element-from-iqueryablet uses a custom defined function as well, that's why I thought that it would work. – mikevil14rin Jul 01 '14 at 08:01
@mikevil14rin in that question, the second query `items.Where(itm => IsWhatINeed(itm));` should be executed on the memory instead of sql server. – Selman Genç Jul 01 '14 at 08:06
Hi, can you elaborate on that? What do u mean by executed on memory instead of sql server? – mikevil14rin Jul 01 '14 at 08:24
@mikevil14rin I mean, first the filtered data is fetched from sql server, then it will be filtered in the memory again but using linq to objects instead of linq to sql. so `itm => IsWhatINeed(itm)` will not be translated into sql. – Selman Genç Jul 01 '14 at 08:28
by linq to objects do u mean declaring a variable var item, then assigning the result from items.where()? – mikevil14rin Jul 01 '14 at 08:33
@mikevil14rin linq to object means, you have some objects and you are operating on them, for example if you have a List and you write: `myList.Where(x => x > 5)` this will be executed on memory. in the same way, linq to entities first fetches the result and creates the entities (classes that you have created for your db tables) and then do the second filtering on those objects. and honestly I don't think that answer will work without a ToList call like in [this](http://stackoverflow.com/a/2938469/3010968) answer. – Selman Genç Jul 01 '14 at 08:37

Marco · Answer 2 · 2014-07-01T08:59:11.870

Try adding .ToList() before your where clause.

I am gussing you are querying a database of some sort, but the data provider can't translate your method Myfunction.

Get your data into memory before firing the where clause:

var items = MyDataContext.Items.ToList().Where(x =>MyFunction(x.value1, x.value2, x.value3)

From the cmoments I take it, there is some additional explanation needed. Your query is not executed until you really need it. By calling .ToList() you materialize your data and every action afterwards is executed in memory on your machine instead on the database. This means you could very well split this into 2 calls:

//First call. Executed as SQL query on database. Materializes data into IEnumerable<Item>
var items = MyDataContext.Items.ToList();

//second call on client
var filteredItems = Items.Where(x => x.Id == MyFunction(value1, value2));

If you omit the ToList() call, Entity Framework trys to translate Myfunction() into SQL, which it can't and then throws an error.

Hi, thanks for your reply. This solved the error message problem, but my items were not filtered, i checked the total before and after filtering and it was the same. I checked MyFunction and it returned "false" for certain records, so the total after filtering should be less, if my understanding is correct. — mikevil14rin, Jul 01 '14 at 08:22
Awesome, but I certainly can't help you with your filtering, since this is not part of the question/problem AND you did not provide any code. Feel free to open up another question to address your filtering problem — Marco, Jul 01 '14 at 08:52

score 0 · Answer 3 · answered Jul 01 '14 at 08:31

0

if you want to run this code perfectly, you should use IEnumurable like this:

var items = MyDataContext.Items.toList().Where(x =>MyFunction(x.value1, x.value2, x.value3));

answered Jul 01 '14 at 08:31

AmirReza

311
1
3
6

Filtering IQueryable using self defined function causes runtime error

3 Answers3