CODEWITHSUNDEEP
c#linqlinq-to-sqllinq-to-entities
Chris
Chris

Reputation: 27384

Reusable functions for use with Linq-to-Entities

I have some stats code that I want to use in various places to calculate success / failure percentages of schedule Results. I recently found a bug in the code and this was due to the fact it was replicated in each LINQ statement, I then decided it would be better to have common code to do this. The problem being, of course, is that a normal function, when executed on SQL server, throws a NotSupportedException because the fuinction doesnt exist in SQL Server.

How can I write a reusable stats code that gets executed on SQL server or is this not possible?

Here is the code I have written for Result

public class Result
{
    public double CalculateSuccessRatePercentage()
    {
        return this.ExecutedCount == 0 ? 100 : ((this.ExecutedCount - this.FailedCount) * 100.0 / this.ExecutedCount);
    }

    public double CalculateCoveragePercentage()
    {
        return this.PresentCount == 0 ? 0 : (this.ExecutedCount * 100.0 / this.PresentCount);
    }
}

And it is used like so (results is IQueryable, and throws the exception):

schedule.SuccessRatePercentage = (int)Math.Ceiling(results.Average(r => r.CalculateSuccessRatePercentage()));
schedule.CoveragePercentage = (int)Math.Ceiling(results.Average(r => r.CalculateCoveragePercentage()));

or like this (which works, because we do this on a single result)

retSchedule.SuccessRatePercentage = (byte)Math.Ceiling(result.CalculateSuccessRatePercentage());
retSchedule.CoveragePercentage = (byte)Math.Ceiling(result.CalculateCoveragePercentage());

Edit

As per @Fred's answer I now have the following code, which works for an IQueryable

schedule.SuccessRatePercentage = (int)Math.Ceiling(scheduleResults.Average(ScheduleResult.CalculateSuccessRatePercentageExpression()));
schedule.CoveragePercentage = (int)Math.Ceiling(scheduleResults.Average(ScheduleResult.CalculateCoveragePercentageExpression()));

The only problem, albeit a minor one, is that this code will not work for individual results i.e.

retSchedule.SuccessRatePercentage = (byte)Math.Ceiling(/* How do I use it here for result */);

Upvotes: 2

Views: 605

Answers (1)

Fred Kleuver
Fred Kleuver

Reputation: 8037

You can't pass functions to SQL - you would need to declare the function on the actual SQL database and then call that from your code.

What you could do/try is this:

Expression<Func<Result, double>> CalculateCoveragePercentage()
{
    return r => r.PresentCount == 0 ? 0 : (r.ExecutedCount * 100.0 / r.PresentCount);
}

It needs to be interpreted instead of executed so that EF can translate it to SQL. The problem is, I've only heard of this being possible when it's passed directly into a where clause.

Since you are able to do these calculations when you apply them directly inside of your LINQ query, I'm inclined to think that it should also be possible to declare those calculations as Expression<Func<..., ...>> and them pass them in.

The only way to know for sure is to try (unless you feel like looking into EF's ExpressionBuilder)

UPDATE:

I should have mentioned that, if this would work, you need to pass this expression into a Select statement:

// Assuming you have Results declared as a DbSet or IDbSet, such as:
DbSet<Result> Results

// You could do something like this (just to illustrate that
// it would be interpreted rather than executed):
List<double> allCoveragePercentages = Results.Select(CalculateCoveragePercentage)
                                             .ToList();

UPDATE #2:

In order for this to work with individual results (or in any case whatsoever), you need to pass it into a clause that accepts the expression. Examples are Select, Where, Average (apparently), anything that does not returns results.

From the top of my head (I'm sure I'm missing a few):

  • List: ToArray, ToDictionary, ToList, ToLookup

  • Single result: First, FirstOrDefault, Single, SingleOrDefault, Last, LastOrDefault

  • Computation: Count, Sum, Max, Min

Since the above clauses return results, they (for as far as I know) only accept Predicates (a function that can only return 'true' or 'false')

You may have coincidentally got it right with your .Average(CalculateCoveragePercentage)

So if you were to get a single result with .FirstOrDefault(), you would pass in your expression inside of a select clause right before that: .Select(CalculateCoveragePercentage).FirstOrDefault(). That is, if you don't need the actual entity but just the calculation. Be aware though that this particular example will return 0 if there were no Result results. You may or may not want this behavior.

Of course, if you already have your result (it's not an IQueryable anymore) then you can simple do:

var coveragePercentage = CalculateCoveragePercentage().Compile().Invoke(result);

But that would kind of defeat the purpose of the expression - for this situation you should just add a method to your Result class that calculates the CoveragePercentage of a given instance.

Upvotes: 4

Related Questions