覆盖 Linq-to-Sql Datetime.ToString() 默认转换值

Question

是否可以覆盖默认的 CONVERT 样式？我想要默认的 CONVERT函数始终返回 ISO8601 样式 126。

重现步骤：

DROP TABLE DATES;

CREATE TABLE DATES
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    MYDATE DATETIME DEFAULT(GETUTCDATE())
);

INSERT INTO DATES DEFAULT VALUES;
INSERT INTO DATES DEFAULT VALUES;
INSERT INTO DATES DEFAULT VALUES;
INSERT INTO DATES DEFAULT VALUES;

SELECT CONVERT(NVARCHAR,MYDATE) AS CONVERTED, 
       CONVERT(NVARCHAR(4000),MYDATE,126) AS ISO, 
       MYDATE FROM DATES
WHERE MYDATE LIKE'Feb%'

输出：

CONVERTED                   ISO                          MYDATE
--------------------------- ---------------------------- -----------------------
Feb  8 2011 12:17AM         2011-02-08T00:17:03.040      2011-02-08 00:17:03.040
Feb  8 2011 12:17AM         2011-02-08T00:17:03.040      2011-02-08 00:17:03.040
Feb  8 2011 12:17AM         2011-02-08T00:17:03.040      2011-02-08 00:17:03.040
Feb  8 2011 12:17AM         2011-02-08T00:17:03.040      2011-02-08 00:17:03.040

Linq-to-Sql 调用 CONVERT(NVARCHAR,@p) 当我投射 ToString() 时。但是，我以 ISO8601 格式显示所有数据。如果可能的话，我想覆盖数据库默认值CONVERT(NVARCHAR,@p,126)。

我正在使用 动态 Linq-to-Sql，如 ScottGu 来处理我的数据。

PropertyInfo piField = typeof(T).GetProperty(rule.field);
if (piField != null)
{
    Type typeField = piField.PropertyType;
    if (typeField.IsGenericType && typeField.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
    {
        filter = filter
           .Select(x => x)
           .Where(string.Format("{0} != null", rule.field))
           .Where(string.Format("{0}.Value.ToString().Contains(\"{1}\")", rule.field, rule.data));
    }
    else
    {
        filter = filter
            .Select(x => x)
            .Where(string.Format("{0} != null", rule.field))
            .Where(string.Format("{0}.ToString().Contains(\"{1}\")", rule.field, rule.data));
    }
} 

我希望我的属性能够将表达式从 CONVERT(NVARCHAR,@p) 转换为 CONVERT(NVARCHAR,@p,126)，但是我得到了 NotSupportedException : ...不支持 SQL 转换。

public string IsoDate
{
    get
    {
        if (SUBMIT_DATE.HasValue)
        {
            return SUBMIT_DATE.Value.ToString("o");
        }
        else
        {
            return string.Empty;
        }
    }
}

Answer 1

我没有使用动态 linq，而是使用了表达式生成器。请参阅 Ilya Builuk 在 代码项目。它根据类参数动态构建表达式。显然我不能使用“包含”，但我可以使用大于或小于表达式。

您可以用字符串替换 WhereOperation。在这个特定的示例中，它是一个枚举。

我实现了更大和更少的表达式，并且我能够缩小搜索范围，而不是使用单词包含。

public static class LinqExtensions
{  
    public static IQueryable<T> Where<T>(this IQueryable<T> query,
        string column, object value, WhereOperation operation)
    {
        if (string.IsNullOrEmpty(column))
            return query;

        ParameterExpression parameter = Expression.Parameter(query.ElementType, "p");

        MemberExpression memberAccess = null;
        foreach (var property in column.Split('.'))
            memberAccess = MemberExpression.Property
               (memberAccess ?? (parameter as Expression), property);

        //change param value type
        //necessary to getting bool from string
        ConstantExpression filter = Expression.Constant
            (
                Convert.ChangeType(value, memberAccess.Type)
            );

        //switch operation
        Expression condition = null;
        LambdaExpression lambda = null;
        switch (operation)
        {
            //equal ==
            case WhereOperation.Equal:
                condition = Expression.Equal(memberAccess, filter);
                lambda = Expression.Lambda(condition, parameter);
                break;
            //not equal !=
            case WhereOperation.NotEqual:
                condition = Expression.NotEqual(memberAccess, filter);
                lambda = Expression.Lambda(condition, parameter);
                break;
            //string.Contains()
            case WhereOperation.Contains:
                condition = Expression.Call(memberAccess,
                    typeof(string).GetMethod("Contains"),
                    Expression.Constant(value));
                lambda = Expression.Lambda(condition, parameter);
                break;
        }


        MethodCallExpression result = Expression.Call(
               typeof(Queryable), "Where",
               new[] { query.ElementType },
               query.Expression,
               lambda);

        return query.Provider.CreateQuery<T>(result);
    }
}