Linq query can't be converted to SQL: how to optimize the code?

Question

Here is snapshot of my code that works fine:

var q1 = from msg in db.GetTable<Message>()
                .Where(msg0 => ...)
         from mt in db.GetTable<MessageTo>()
                .Where(mt0 => ...)                                       
                .DefaultIfEmpty()
         select new { msg, mt }
                    ;

AgeTypeEnum eAgeType = (AgeTypeEnum)age.Value;
switch (eAgeType)
{
case AgeTypeEnum.Invalid:
    break;

case AgeTypeEnum.LastWeek:
    q1 = q1.Where(q => q.msg.CreatedDate >= DateTime.Now.AddDays(-7));
    break;

case AgeTypeEnum.LastMonth:
    q1 = q1.Where(q => q.msg.CreatedDate >= DateTime.Now.AddMonths(-1) && q.msg.CreatedDate < DateTime.Now.AddDays(-7));
    break;

case AgeTypeEnum.CurrentSeason:
    q1 = q1.Where(q => q.msg.CreatedDate >= Season.CurrentSeason.FirstPlayedDay.RealDate && q.msg.CreatedDate < DateTime.Now.AddDays(-7));
    break;

case AgeTypeEnum.LastSeason:
    q1 = q1.Where(q => q.msg.CreatedDate >= Season.PreviousSeason.FirstPlayedDay.RealDate && q.msg.CreatedDate < Season.CurrentSeason.FirstPlayedDay.RealDate);
    break;

case AgeTypeEnum.PreviousSeasons:
    q1 = q1.Where(q => q.msg.CreatedDate < Season.PreviousSeason.FirstPlayedDay.RealDate);
    break;

default:
    throw new MyException("'{0}' age type is not supported", eAgeType);
}
return q1.Select(q => new {MessageObj = q.msg}).ToList();

This code works fine, but it is quite bit. And contains potentially reusable logic. I would like to optimize it in the following way:

Func<Message, bool> qAgeFilter;
AgeTypeEnum eAgeType = (AgeTypeEnum)age.Value;
switch (eAgeType)
{
case AgeTypeEnum.Invalid:
    qAgeFilter = null;
    break;

case AgeTypeEnum.LastWeek:
    qAgeFilter = msg => msg.CreatedDate >= DateTime.Now.AddDays(-7);
    break;

case AgeTypeEnum.LastMonth:
    qAgeFilter = msg => msg.CreatedDate >= DateTime.Now.AddMonths(-1) && msg.CreatedDate < DateTime.Now.AddDays(-7);
    break;

case AgeTypeEnum.CurrentSeason:
    qAgeFilter = msg => msg.CreatedDate >= Season.CurrentSeason.FirstPlayedDay.RealDate && msg.CreatedDate < DateTime.Now.AddDays(-7);
    break;

case AgeTypeEnum.LastSeason:
    qAgeFilter = msg => msg.CreatedDate >= Season.PreviousSeason.FirstPlayedDay.RealDate && msg.CreatedDate < Season.CurrentSeason.FirstPlayedDay.RealDate;
    break;

case AgeTypeEnum.PreviousSeasons:
    qAgeFilter = msg => msg.CreatedDate < Season.PreviousSeason.FirstPlayedDay.RealDate;
    break;

default:
    throw new MyException("'{0}' age type is not supported", eAgeType);
}
if (qAgeFilter != null)
{
    q1 = q1.Where(q => qAgeFilter(q.msg));
}

In fact, the difference is that instead of modifying the q1 object (query itself) I combine new delegate and after use it in query expression.

When I try to execute optimized code I receive an exception:

'Invoke(value(vfm_elita.ServiceLayer.DataLogicLayer.Messages.MessagesExtension+<>c_DisplayClass21+<>c_DisplayClass2c).qAgeFilter, q.msg)' cannot be converted to SQL.

Questions:

1. What wrong with my code?

2. How would you recommend to optimize the source code in order to extract reusable logic to compose 'filter' delegate?

Thank you

P.S. I use ASP.NET 4.0, MySQL 5.0, BLToolKit as DB access engine.

Answer 1

for point 2 you can use an extension method,

public Table<Message>      Messages             { get { return GetTable<Message>(); } }

from msg in db.Messages.ForAge(age)
                       .Where(msg0 => ...)



private static IQueryable<Message> ForAge(this IQueryable<Message> messages, AgeTypeEnum  ageType) 
{
   switch(ageType)    
   {
      case AgeTypeEnum.Invalid:   return messages;
      case AgeTypeEnum.LastWeek:  return messages.Where(q => q.msg.CreatedDate >= DateTime.Now.AddDays(-7));
      case AgeTypeEnum.LastMonth: return messages.Where(q => q.msg.CreatedDate >= DateTime.Now.AddMonths(-1) && 
                                                             q.msg.CreatedDate <  DateTime.Now.AddDays(-7));    
    } 
}

guess you can also use an interface here for classes with a CreatedDate field/prop