Write own functions to be used in c# linq statement

Question

I have an object

public class Product{
       public int Id { get; set; }
       public double Cost { get; set; }
       public DateTime DatePurchased { get; set; }
       public string Name { get; set; }
       public string Barcode { get; set; }
       public string Category { get; set; }
}

I also have a function public void TotalProductDiscountHistory(DateTime datePurchased, double cost) that does some complex maths and return the discount values from the day it was purchased to date. I would like to basically be able to call this function inside my query statement to return the sum of all possible for all products as show below.

var query = db.Products.where(x => x.clientId == clientId)
.GroupBy(c => c.Category).Select(a => new {
Category = a.Category,
Cost = a.Sum(u => u.Cost),
TotalDiscounts = a.Sum( TotalProductDiscountHistory(a.DatePurchased, 
a.Cost))
});

My questions is how do I achieve this or rather how do I create a function so that I am able to call it within a linq query statement, pass values and return a result.

Answer 1

Your main problem is that the EF is trying to convert the LINQ query into SQL. As there is no SQL equivalent of your function, it really has to just pluck out the data needed for that calculation, and do it after the SQL query, in code.

Linq2SQL was excellent as handling that automatically. EF (even after 6 versions), not so much.

So, we'll have to do it manually:

public double TotalProductDiscountHistory(DateTime datePurchased, double cost) {...}

class CategoryTotals
{
     public int Category {get; set;}
     public double Cost {get; set;}
     public double TotalDiscounts {get; set;}
}

var query = from p in db.Products
            where P.clientId == clientId
            group p by p.Category;


 var totals = new List<CategoryTotals>();
foreach(var grp in query)
{
     var ct = new CategoryTotals
            {
                Category =grp.Category,
                Cost = grp.Sum(u => u.Cost),
                TotalDiscounts = grp.Sum(u=> 
                        TotalProductDiscountHistory(u.DatePurchased, u.Cost))
            };
     totals.add(ct);
}