2

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 */);
Improve this question
1
  • Could you include the declarations of your variables so we can see their data types? Commented Apr 8, 2015 at 14:12

1 Answer 1

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This worked, thanks, really nice solution. Although I couldnt get the same code to work for a single result (any ideas?) but at least all the code is now all in one place
Could you add the code you're trying to execute to your question?
Not sure if you saw my edit though. Did you try it with the Select(CalculateCoveragePercentage) syntax I provided?
Hey Fred, it works for LINQ without the edit, it just doesnt work for single (POCO) results. I updated my question with more info
Apologies, I screwed that one up. It's updated again - the information below 'UPDATE #2' should help you fix your issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.