Consider I have some code first model in my app and some snippet:
var myQueryable = (IQueryable<MyEntity>dbContext.MyEntitySet;
var myFilteredQueryable = externalService.Filter(myQueryable);
Now, I need to call stored function from database in order to add additional criteria, sth like this:
WHERE MyFunc(MyCol) = 1;
I know, there is no posibility to create own function with EdmFunciton to use it like this:
myFilteredQueryable = myFilteredQueryable.Where(myEnt => MyFunctions.MyFunc(myEnty.MyCol);
It is available in Databse first / Model first.
I know I can run it directly:
var selectedIds = myFilteredQueryable.Select(m => m.Id).ToArray();
var filteredIds = dbContext.Database.ExecuteSqlQuery<int>("SELECT Id FROM MyEntity WHERE MyFunc(MyCol) = 1 AND ID in (" + string.Join(selectedIds, ",") + ")").ToArray();
myFilteredQueryable = myFilteredQueryable.Where(myEnt => filteredIds.Contains(myEnt.Id));
Performance of this solution is not good one.. What are other ways to filter by queryable by function? I am obligated to use Code First and cannot modyify external service.
