asp.net - Linq 查询无法转换为 SQL : how to optimize the code?

Question

这是我的代码的快照，运行良好:

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();

这段代码工作正常，但是有点多。并包含潜在的可重用逻辑。我想通过以下方式优化它:

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));
}

事实上，区别在于，我没有修改 q1 对象(查询本身)，而是组合了新的委托(delegate)，并在查询表达式中使用它。

当我尝试执行优化代码时，我收到一个异常:

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

问题:

1. 我的代码出了什么问题？

2. 您建议如何优化源代码以提取可重用逻辑来组成“过滤器”委托(delegate)？

谢谢

附注我使用 ASP.NET 4.0、MySQL 5.0、BLToolKit 作为数据库访问引擎。

Answer 1

对于第 2 点，您可以使用扩展方法，

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));    
    } 
}

我猜您还可以在此处使用具有 CreatedDate 字段/属性的类的接口(interface)