CODEWITHSUNDEEP
entity-frameworklinqlinq-to-entities
Paul Johnson
Paul Johnson

Reputation: 1418

Reusable Functions in Linq To Entites

I have 2 reusable functions that return lists. If the code from these functions is written directly into the linq to entities query all is good. However, separating these out into functions causes an error as it cannot be translated to a stored expression. I'm sure there must be a way of doing this though. Any ideas how to solve this problems. Ideally I want the reusable parts to be used outside of linq to entity queries also.

var activityBands = DbContext.ActivityBand
                .OrderBy(x => x.ActivityBandDescription)
                .Where(x => x.Active && x.ClientAccountId == clientAccountId)
                .Select(x => new ActivityBandDdl
                {
                    Name = x.ActivityBandDescription,
                    ActivityBandId = x.ActivityBandId,
                    ApplyAwr = x.ApplyAwr,
                    AssignmentLineTimeTypeIds = TimeTypesForActivityBand(x.DailyRate) ,
                    AssignmentTypeIds = AssTypesForActivityBand(x.StagePayment)
                }).ToList();          



public static Func<bool, List<int>> TimeTypesForActivityBand =
                     (dailyRate) => (new int[] { 1, 2, 3, 4 }).Where(t =>
                       ((t != 1 && t != 2) || !dailyRate) //No Timed or NTS for daily rates
                     ).ToList();


public static Func<bool, List<int>> AssTypesForActivityBand =
                             (stagePayment) => (new int[] { 2,3,4,5,6,7,8,9,10 }).Where(t =>
                               ( t!=2 || !stagePayment) //Only stage pay ass have stage pay activity bands
                             ).ToList();

Upvotes: 1

Views: 68

Answers (1)

DarkSquirrel42
DarkSquirrel42

Reputation: 10257

TL;DR; suggested solution for your problem:

get LinqKit ... have a look at it's Expand() function (in the docs: combining expressions) https://github.com/scottksmith95/LINQKit#combining-expressions

the details:

the problem boils down to: what is the difference between the queries in both cases...

A LINQ query works with an expression tree ... in other words: just because the code you typed directly into the query and the code you typed into the static Func<...> looks the same, in fact, is the same, the resulting expression trees in both cases are not the same

what is an expression tree?

imagine a simpler query like ... someIQueryable.Where(x => x.a==1 && x.b=="foo")

the lamda that is passed to Where(...) can be seen as a straigt forward c# lambda expression that can be used as a Func

and it can also be seen as a Expression>

the later is a tree of objects that form the expression, in other words a description about the way, in that the passed in parameter can be evaluated to a bool without actually having the executable code, but just the description about what to do ... take the member a from the parameter, equality-compare it to the constant 1 ... take the boolean AND of the result with the result of: take the member b from the parameter, equality-compare it to the constant "foo" ... return the result of the boolean AND

why all of this?

it's the way LINQ works ... LINQ to entiteis takes the expression tree, looks at all the operations, finds the corresponding SQL, and builds an SQL statement which is executed in the end ...

when you have your extracted Func<...> there is a little problem ... at some point in the resulting expression tree there is something like ... take the parameter x and CALL the static Func ... the expression tree does no longer contain a description of whats happening inside the Func, but just a call to that ... as long as you want to compile that to a .net runtime executable function, it's all fun and games ... but when you try to parse it into SQL, LINQ to entiteis does not know a corresponding SQL for "call some c# function" ... therefore it tells you that this part of the expression tree can not be converted into a store expression

Upvotes: 1

Related Questions