Integrating custom method into LINQ to Entities query

Question

I have a custom method that performs some calculation on a set of data:

 private int GetPercentages(int OriginalValue, int TotalValue)
        {
            var newValue = (int)Math.Round(((decimal)OriginalValue / (decimal)TotalValue) * 100);

            return newValue;
         }

I need to be able to run this method inside of a LINQ to Entities query:

var data = from SurveyResponseModel in db.SurveyResponseModels
                       group SurveyResponseModel by SurveyResponseModel.MemberId into resultCount
                       select new ResultsViewModel()
                       {
                           MemberId = resultCount.Key,
                           PatientFollowUpResult = db.SurveyResponseModels.Count(r => r.PatientFollowUp),
                           PatientFollowUpResultPct = GetPercentages(db.SurveyResponseModels.Count(r => r.PatientFollowUp),totalResponsesResult),
                           ChangeCodingPracticeResult = db.SurveyResponseModels.Count(r => r.ChangeCodingPractice),
  };

I need to run this on about 20 more lines inside of the query so just sticking it inline doesn't seem like a great option. I understand that it needs to be converted into SQL syntax, but is there anything else like this that I can do?

david.s · Answer 1 · 2012-06-25T16:32:54.073

3

You need to make a lambda expression that calculates the percentage like this:

Expression<Func<int, int, int>> calcPercentage =
    (OriginalValue, TotalValue) => (int)Math.Round(((decimal)OriginalValue / (decimal)TotalValue) * 100);

And use it like this:

var data = from SurveyResponseModel in db.SurveyResponseModels.ToExpandable()
           group SurveyResponseModel by SurveyResponseModel.MemberId into resultCount
           select new ResultsViewModel()
           {
               MemberId = resultCount.Key,
               PatientFollowUpResult = db.SurveyResponseModels.Count(r => r.PatientFollowUp),
               PatientFollowUpResultPct = calcPercentage.Invoke(db.SurveyResponseModels.Count(r => r.PatientFollowUp), totalResponsesResult),
               ChangeCodingPracticeResult = db.SurveyResponseModels.Count(r => r.ChangeCodingPractice),
           };

More info about calling functions in LINQ queries here.

edited Jun 25 '12 at 16:32

answered Jun 22 '12 at 23:58

david.s

11,283
6
50
82

Hmm.. The expression is giving me an error "Delegate system.func does not take 2 parameters" – user547794 Jun 25 '12 at 15:45
@user547794 I corrected the answer. The expression type should be `Func` which takes 2 `int` parameters and returns an `int`. – david.s Jun 25 '12 at 16:37
Looks like the expression is working, but I'm getting a "has some invalid arguments" on "calcPercentage.Invoke(db.SurveyResponseModels.Count(r => r.PatientFollowUp), totalResponsesResult)" – user547794 Jun 25 '12 at 17:50
@user547794 What is the type of `totalResponsesResult`? It should be `int`. If it is not you need to cast it to `int` or change the expression delegate to accept some other type. – david.s Jun 25 '12 at 18:33
totalResponseResult is indeed an int. When I try to build I get "Error 9 Argument 1: cannot convert from 'int' to 'System.Linq.Expressions.Expression" and Error 10 Argument 2: cannot convert from 'int' to 'System.Linq.Expressions.Expression[]'" – user547794 Jun 25 '12 at 18:48

Integrating custom method into LINQ to Entities query

1 Answers1

Linked