LinqToSQL: Using custom methods to calculate expressions

Question

I'm running a Linq To SQL query and would like to retrieve a calculated column based on some settings in my table, I want to add a time unit to a date field.

I'm running the following:

var q = from p in db.PublicHolidays
select new {
     Holiday = s.Holiday,
     ReminderDay = Convert.ToDateTime(p.Holiday).AddByUnit(p.ReminderUnits, -p.ReminderQuantity)
};

On my date calculation I get an error that 'System.DateTime' does not contain a definition for 'AddByUnit' .

Intellisense does bring up AddByUnit so I'm not sure what is wrong. Removing the explicit date conversion gives the same error

Answer 1

As the error message says, the method AddByUnit (probably an extension method) was not found to run at runtime. Make sure your code can see this method.

Important tip for you Linq to Sql

You cannot use an custom method into a linq-to-sql. Depending of the provider you are using, it will not support because linq-to-sql will try to transform your linq query into sql query. Try to bring it the property you need to a anonymous object into a concrete result and lead with this in memory. For sample.

// call ToList() to execute it on database and bring to memory.    
var list = (from p in db.PublicHolidays
            select new {
                Holiday = s.Holiday,
                ReminderUnits = p.ReminderUnits
                ReminderQuantity = p.ReminderQuantity
            }).ToList();

// use it in memory
var result = list.Select(x => new {
                  Holiday = x.Holiday,
                  ReminderDay = Convert.ToDateTime(x.Holiday).AddByUnit(x.ReminderUnits, -p.ReminderQuantity))
              }).ToList();