如何根据 rowversion/timestamp 值查询 Code First 实体?

发布于 2024-12-05 00:38:54 字数 764 浏览 0 评论 0原文

我遇到过这样的情况:在 LINQ to SQL 中工作得相当好的东西在实体框架中似乎非常迟钝(或者可能不可能)。具体来说,我有一个包含 rowversion 属性(用于版本控制和并发控制)的实体。类似于:

public class Foo
{
  [Key]
  [MaxLength(50)]
  public string FooId { get; set; }

  [Timestamp]
  [ConcurrencyCheck]
  public byte[] Version { get; set; }
}

我希望能够将一个实体作为输入,并找到最近更新的所有其他实体。类似于:

Foo lastFoo = GetSomeFoo();
var recent = MyContext.Foos.Where(f => f.Version > lastFoo.Version);

现在,在数据库中这将起作用:两个 rowversion 值可以毫无问题地相互比较。而且我在使用LINQ to SQL之前也做过类似的事情,将rowversion映射到System.Data.Linq.Binary,可以进行比较。 (至少在表达式树可以映射回数据库的范围内。)

但是在 Code First 中,属性的类型必须是 byte[]。并且两个数组无法使用常规比较运算符进行比较。是否有其他方法来编写 LINQ to Entities 能够理解的数组比较?或者将数组强制转换为其他类型以便比较可以通过编译器?

I've run into a case where something that worked fairly well with LINQ to SQL seems to be very obtuse (or maybe impossible) with the Entity Framework. Specifically, I've got an entity that includes a rowversion property (both for versioning and concurrency control). Something like:

public class Foo
{
  [Key]
  [MaxLength(50)]
  public string FooId { get; set; }

  [Timestamp]
  [ConcurrencyCheck]
  public byte[] Version { get; set; }
}

I would like to be able to take a entity as input, and find all of the other entities that are more recently updated. Something like:

Foo lastFoo = GetSomeFoo();
var recent = MyContext.Foos.Where(f => f.Version > lastFoo.Version);

Now, in the database this would work: two rowversion values can be compared to one another without any problems. And I've done a similar thing before using LINQ to SQL, which maps the rowversion to System.Data.Linq.Binary, which can be compared. (At least to the extent that the expression tree can be mapped back to the database.)

But in Code First, the type of the property must be byte[]. And two arrays can't be compared with the regular comparison operators. Is there some other way to write the comparison of the arrays that LINQ to Entities will understand? Or to coerce the arrays into other types so that the comparison can get past the compiler?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(10

小草泠泠 2024-12-12 00:38:54

找到了一个完美的解决方法!在实体框架 6.1.3 上测试。

无法对字节数组使用 < 运算符,因为 C# 类型系统会阻止这种情况(它应该这样做)。但是您可以做的是使用表达式构建完全相同的语法,并且有一个漏洞可以让您实现这一点。

第一步

如果您不需要完整的解释,可以跳至解决方案部分。

如果您不熟悉表达式,请参阅MSDN 速成课程

基本上,当您输入 queryable.Where(obj => obj.Id == 1) 时,编译器实际上会输出与您输入相同的内容:

var objParam = Expression.Parameter(typeof(ObjType));
queryable.Where(Expression.Lambda<Func<ObjType, bool>>(
    Expression.Equal(
        Expression.Property(objParam, "Id"),
        Expression.Constant(1)),
    objParam))

该表达式是数据库提供程序解析的内容创建您的查询。这显然比原来的冗长得多,但它也允许您像进行反射一样进行元编程。冗长是此方法的唯一缺点。与这里的其他答案相比,这是一个更好的缺点,例如必须编写原始 SQL 或无法使用参数。

就我而言,我已经在使用表达式,但在您的情况下,第一步是使用表达式重写您的查询:

Foo lastFoo = GetSomeFoo();
var fooParam = Expression.Parameter(typeof(Foo));
var recent = MyContext.Foos.Where(Expression.Lambda<Func<Foo, bool>>(
    Expression.LessThan(
        Expression.Property(fooParam, nameof(Foo.Version)),
        Expression.Constant(lastFoo.Version)),
    fooParam));

这就是我们如何解决如果我们尝试使用 < 时出现的编译器错误在 byte[] 对象上。现在,我们得到的不再是编译器错误,而是运行时异常,因为 Expression.LessThan 尝试查找 byte[].op_LessThan 但在运行时失败。 这就是漏洞出现的地方。

漏洞

为了消除该运行时错误,我们将告诉 Expression.LessThan 使用什么方法,这样它就不会尝试查找默认值 (byte[].op_LessThan) 不存在:

var recent = MyContext.Foos.Where(Expression.Lambda<Func<Foo, bool>>(
    Expression.LessThan(
        Expression.Property(fooParam, nameof(Foo.Version)),
        Expression.Constant(lastFoo.Version),
        false,
        someMethodThatWeWrote), // So that Expression.LessThan doesn't try to find the non-existent default operator method
    fooParam));

太棒了!现在我们需要的是从带有签名 bool (byte[], byte[]) 的静态方法创建的 MethodInfo someMethodThatWeWrote ,以便类型在运行时与我们的其他表达式匹配。

解决方案

您需要一个小的 DbFunctionExpressions.cs。这是一个删节版本:

public static class DbFunctionExpressions
{
    private static readonly MethodInfo BinaryDummyMethodInfo = typeof(DbFunctionExpressions).GetMethod(nameof(BinaryDummyMethod), BindingFlags.Static | BindingFlags.NonPublic);
    private static bool BinaryDummyMethod(byte[] left, byte[] right)
    {
        throw new NotImplementedException();
    }

    public static Expression BinaryLessThan(Expression left, Expression right)
    {
        return Expression.LessThan(left, right, false, BinaryDummyMethodInfo);
    }
}

Usage

var recent = MyContext.Foos.Where(Expression.Lambda<Func<Foo, bool>>(
    DbFunctionExpressions.BinaryLessThan(
        Expression.Property(fooParam, nameof(Foo.Version)),
        Expression.Constant(lastFoo.Version)),            
    fooParam));
  • Enjoy。

注释

不适用于 Entity Framework Core 1.0.0,但我打开了一个问题无论如何,无需表达即可获得更全面的支持。 (EF Core 不起作用,因为它经历了一个阶段,它使用 leftright 参数复制 LessThan 表达式,但不复制我们用于漏洞的 MethodInfo 参数。)

Found a workaround that works perfectly! Tested on Entity Framework 6.1.3.

There's no way to use the < operator with byte arrays because the C# type system prevents that (as it should). But what you can do is build the exact same syntax using expressions, and there is a loophole that allows you to pull this off.

First step

If you don't want the full explanation, you can skip to the Solution section.

If you aren't familiar with expressions, here is MSDN's crash course.

Basically, when you type queryable.Where(obj => obj.Id == 1) the compiler really outputs the same thing as if you had typed:

var objParam = Expression.Parameter(typeof(ObjType));
queryable.Where(Expression.Lambda<Func<ObjType, bool>>(
    Expression.Equal(
        Expression.Property(objParam, "Id"),
        Expression.Constant(1)),
    objParam))

And that expression is what the database provider parses to create your query. This is obviously much more verbose than the original, but it also allows you do do meta-programming just like when you do reflection. The verbosity is the only downside to this method. It's a better downside than other answers here, like having to write raw SQL or not being able to use parameters.

In my case, I was already using expressions, but in your case the first step is to rewrite your query using expressions:

Foo lastFoo = GetSomeFoo();
var fooParam = Expression.Parameter(typeof(Foo));
var recent = MyContext.Foos.Where(Expression.Lambda<Func<Foo, bool>>(
    Expression.LessThan(
        Expression.Property(fooParam, nameof(Foo.Version)),
        Expression.Constant(lastFoo.Version)),
    fooParam));

This is how we get around the compiler error we get if we try to use < on byte[] objects. Now instead of a compiler error, we get a runtime exception because Expression.LessThan tries to find byte[].op_LessThan and fails at runtime. This is where the loophole comes in.

Loophole

To get rid of that runtime error, we will tell Expression.LessThan what method to use so that it doesn't try to find the default one (byte[].op_LessThan) which doesn't exist:

var recent = MyContext.Foos.Where(Expression.Lambda<Func<Foo, bool>>(
    Expression.LessThan(
        Expression.Property(fooParam, nameof(Foo.Version)),
        Expression.Constant(lastFoo.Version),
        false,
        someMethodThatWeWrote), // So that Expression.LessThan doesn't try to find the non-existent default operator method
    fooParam));

Great! Now all we need is MethodInfo someMethodThatWeWrote created from a static method with the signature bool (byte[], byte[]) so that the types match at runtime with our other expressions.

Solution

You need a small DbFunctionExpressions.cs. Here's a truncated version:

public static class DbFunctionExpressions
{
    private static readonly MethodInfo BinaryDummyMethodInfo = typeof(DbFunctionExpressions).GetMethod(nameof(BinaryDummyMethod), BindingFlags.Static | BindingFlags.NonPublic);
    private static bool BinaryDummyMethod(byte[] left, byte[] right)
    {
        throw new NotImplementedException();
    }

    public static Expression BinaryLessThan(Expression left, Expression right)
    {
        return Expression.LessThan(left, right, false, BinaryDummyMethodInfo);
    }
}

Usage

var recent = MyContext.Foos.Where(Expression.Lambda<Func<Foo, bool>>(
    DbFunctionExpressions.BinaryLessThan(
        Expression.Property(fooParam, nameof(Foo.Version)),
        Expression.Constant(lastFoo.Version)),            
    fooParam));
  • Enjoy.

Notes

Does not work on Entity Framework Core 1.0.0, but I opened an issue there for fuller support without the need for expressions anyway. (EF Core doesn't work because it goes through a stage where it copies the LessThan expression with the left and right parameters but doesn't copy the MethodInfo parameter we use for the loophole.)

几味少女 2024-12-12 00:38:54

您可以使用 SqlQuery 编写原始 SQL,而不是生成它。

MyContext.Foos.SqlQuery("SELECT * FROM Foos WHERE Version > @ver", new SqlParameter("ver", lastFoo.Version));

You can use SqlQuery to write the raw SQL instead of having it generated.

MyContext.Foos.SqlQuery("SELECT * FROM Foos WHERE Version > @ver", new SqlParameter("ver", lastFoo.Version));
流云如水 2024-12-12 00:38:54

您可以通过将 C# 函数映射到数据库函数,在 EF 6 代码优先中完成此操作。它进行了一些调整,并没有产生最有效的 SQL,但它完成了工作。

首先,在数据库中创建一个函数来测试较新的行版本。我的是

CREATE FUNCTION [common].[IsNewerThan]
(
    @CurrVersion varbinary(8),
    @BaseVersion varbinary(8)
) ...

在构建 EF 上下文时,您必须在存储模型中手动定义该函数,如下所示:

private static DbCompiledModel GetModel()
{
    var builder = new DbModelBuilder();
    ... // your context configuration
    var model = builder.Build(...); 
    EdmModel store = model.GetStoreModel();
    store.AddItem(GetRowVersionFunctionDef(model));
    DbCompiledModel compiled = model.Compile();
    return compiled;
}

private static EdmFunction GetRowVersionFunctionDef(DbModel model)
{
    EdmFunctionPayload payload = new EdmFunctionPayload();
    payload.IsComposable = true;
    payload.Schema = "common";
    payload.StoreFunctionName = "IsNewerThan";
    payload.ReturnParameters = new FunctionParameter[]
    {
        FunctionParameter.Create("ReturnValue", 
            GetStorePrimitiveType(model, PrimitiveTypeKind.Boolean), ParameterMode.ReturnValue)
    };
    payload.Parameters = new FunctionParameter[]
    {
        FunctionParameter.Create("CurrVersion",  GetRowVersionType(model), ParameterMode.In),
        FunctionParameter.Create("BaseVersion",  GetRowVersionType(model), ParameterMode.In)
    };
    EdmFunction function = EdmFunction.Create("IsRowVersionNewer", "EFModel",
        DataSpace.SSpace, payload, null);
    return function;
}

private static EdmType GetStorePrimitiveType(DbModel model, PrimitiveTypeKind typeKind)
{
    return model.ProviderManifest.GetStoreType(TypeUsage.CreateDefaultTypeUsage(
        PrimitiveType.GetEdmPrimitiveType(typeKind))).EdmType;
}

private static EdmType GetRowVersionType(DbModel model)
{
    // get 8-byte array type
    var byteType = PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Binary);
    var usage = TypeUsage.CreateBinaryTypeUsage(byteType, true, 8);

    // get the db store type
    return model.ProviderManifest.GetStoreType(usage).EdmType;
}

通过使用 DbFunction 属性装饰静态方法来创建该方法的代理。 EF 使用此方法将方法与存储模型中的命名方法关联起来。使其成为一种扩展方法可以产生更简洁的 LINQ。

[DbFunction("EFModel", "IsRowVersionNewer")]
public static bool IsNewerThan(this byte[] baseVersion, byte[] compareVersion)
{
    throw new NotImplementedException("You can only call this method as part of a LINQ expression");
}

示例

最后,在标准表达式中调用 LINQ toEntity 的方法。

    using (var db = new OrganizationContext(session))
    {
        byte[] maxRowVersion = db.Users.Max(u => u.RowVersion);
        var newer = db.Users.Where(u => u.RowVersion.IsNewerThan(maxRowVersion)).ToList();
    }

这会使用您定义的上下文和实体集生成 T-SQL 来实现您想要的目的。

WHERE ([common].[IsNewerThan]([Extent1].[RowVersion], @p__linq__0)) = 1',N'@p__linq__0 varbinary(8000)',@p__linq__0=0x000000000001DB7B

You can accomplish this in EF 6 code-first by mapping a C# function to a database function. It took some tweaking and doesn't produce the most efficient SQL, but it gets the job done.

First, create a function in the database to test for a newer rowversion. Mine is

CREATE FUNCTION [common].[IsNewerThan]
(
    @CurrVersion varbinary(8),
    @BaseVersion varbinary(8)
) ...

When constructing your EF context, you'll have to manually define the function in the store model, like this:

private static DbCompiledModel GetModel()
{
    var builder = new DbModelBuilder();
    ... // your context configuration
    var model = builder.Build(...); 
    EdmModel store = model.GetStoreModel();
    store.AddItem(GetRowVersionFunctionDef(model));
    DbCompiledModel compiled = model.Compile();
    return compiled;
}

private static EdmFunction GetRowVersionFunctionDef(DbModel model)
{
    EdmFunctionPayload payload = new EdmFunctionPayload();
    payload.IsComposable = true;
    payload.Schema = "common";
    payload.StoreFunctionName = "IsNewerThan";
    payload.ReturnParameters = new FunctionParameter[]
    {
        FunctionParameter.Create("ReturnValue", 
            GetStorePrimitiveType(model, PrimitiveTypeKind.Boolean), ParameterMode.ReturnValue)
    };
    payload.Parameters = new FunctionParameter[]
    {
        FunctionParameter.Create("CurrVersion",  GetRowVersionType(model), ParameterMode.In),
        FunctionParameter.Create("BaseVersion",  GetRowVersionType(model), ParameterMode.In)
    };
    EdmFunction function = EdmFunction.Create("IsRowVersionNewer", "EFModel",
        DataSpace.SSpace, payload, null);
    return function;
}

private static EdmType GetStorePrimitiveType(DbModel model, PrimitiveTypeKind typeKind)
{
    return model.ProviderManifest.GetStoreType(TypeUsage.CreateDefaultTypeUsage(
        PrimitiveType.GetEdmPrimitiveType(typeKind))).EdmType;
}

private static EdmType GetRowVersionType(DbModel model)
{
    // get 8-byte array type
    var byteType = PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Binary);
    var usage = TypeUsage.CreateBinaryTypeUsage(byteType, true, 8);

    // get the db store type
    return model.ProviderManifest.GetStoreType(usage).EdmType;
}

Create a proxy for the method by decorating a static method with the DbFunction attribute. EF uses this to associate the method with the named method in the store model. Making it an extension method produces cleaner LINQ.

[DbFunction("EFModel", "IsRowVersionNewer")]
public static bool IsNewerThan(this byte[] baseVersion, byte[] compareVersion)
{
    throw new NotImplementedException("You can only call this method as part of a LINQ expression");
}

Example

Finally, call the method from LINQ to entities in a standard expression.

    using (var db = new OrganizationContext(session))
    {
        byte[] maxRowVersion = db.Users.Max(u => u.RowVersion);
        var newer = db.Users.Where(u => u.RowVersion.IsNewerThan(maxRowVersion)).ToList();
    }

This generates the T-SQL to achieve what you want, using the context and entity sets you have defined.

WHERE ([common].[IsNewerThan]([Extent1].[RowVersion], @p__linq__0)) = 1',N'@p__linq__0 varbinary(8000)',@p__linq__0=0x000000000001DB7B
青春如此纠结 2024-12-12 00:38:54

我扩展了jnm2的答案以隐藏扩展方法中丑陋的表达式代码

用法:

ctx.Foos.WhereVersionGreaterThan(r => r.RowVersion, myVersion);

扩展方法:

public static class RowVersionEfExtensions
{


    private static readonly MethodInfo BinaryGreaterThanMethodInfo = typeof(RowVersionEfExtensions).GetMethod(nameof(BinaryGreaterThanMethod), BindingFlags.Static | BindingFlags.NonPublic);
    private static bool BinaryGreaterThanMethod(byte[] left, byte[] right)
    {
        throw new NotImplementedException();
    }

    private static readonly MethodInfo BinaryLessThanMethodInfo = typeof(RowVersionEfExtensions).GetMethod(nameof(BinaryLessThanMethod), BindingFlags.Static | BindingFlags.NonPublic);
    private static bool BinaryLessThanMethod(byte[] left, byte[] right)
    {
        throw new NotImplementedException();
    }

    /// <summary>
    /// Filter the query to return only rows where the RowVersion is greater than the version specified
    /// </summary>
    /// <param name="query">The query to filter</param>
    /// <param name="propertySelector">Specifies the property of the row that contains the RowVersion</param>
    /// <param name="version">The row version to compare against</param>
    /// <returns>Rows where the RowVersion is greater than the version specified</returns>
    public static IQueryable<T> WhereVersionGreaterThan<T>(this IQueryable<T> query, Expression<Func<T, byte[]>> propertySelector, byte[] version)
    {
        var memberExpression = propertySelector.Body as MemberExpression;
        if (memberExpression == null) { throw new ArgumentException("Expression should be of form r=>r.RowVersion"); }
        var propName = memberExpression.Member.Name;

        var fooParam = Expression.Parameter(typeof(T));
        var recent = query.Where(Expression.Lambda<Func<T, bool>>(
            Expression.GreaterThan(
                Expression.Property(fooParam, propName),
                Expression.Constant(version),
                false,
                BinaryGreaterThanMethodInfo),
            fooParam));
        return recent;
    }


    /// <summary>
    /// Filter the query to return only rows where the RowVersion is less than the version specified
    /// </summary>
    /// <param name="query">The query to filter</param>
    /// <param name="propertySelector">Specifies the property of the row that contains the RowVersion</param>
    /// <param name="version">The row version to compare against</param>
    /// <returns>Rows where the RowVersion is less than the version specified</returns>
    public static IQueryable<T> WhereVersionLessThan<T>(this IQueryable<T> query, Expression<Func<T, byte[]>> propertySelector, byte[] version)
    {
        var memberExpression = propertySelector.Body as MemberExpression;
        if (memberExpression == null) { throw new ArgumentException("Expression should be of form r=>r.RowVersion"); }
        var propName = memberExpression.Member.Name;

        var fooParam = Expression.Parameter(typeof(T));
        var recent = query.Where(Expression.Lambda<Func<T, bool>>(
            Expression.LessThan(
                Expression.Property(fooParam, propName),
                Expression.Constant(version),
                false,
                BinaryLessThanMethodInfo),
            fooParam));
        return recent;
    }



}

I extended jnm2’s answer to hide the ugly expression code in a extension method

Usage:

ctx.Foos.WhereVersionGreaterThan(r => r.RowVersion, myVersion);

Extension Method:

public static class RowVersionEfExtensions
{


    private static readonly MethodInfo BinaryGreaterThanMethodInfo = typeof(RowVersionEfExtensions).GetMethod(nameof(BinaryGreaterThanMethod), BindingFlags.Static | BindingFlags.NonPublic);
    private static bool BinaryGreaterThanMethod(byte[] left, byte[] right)
    {
        throw new NotImplementedException();
    }

    private static readonly MethodInfo BinaryLessThanMethodInfo = typeof(RowVersionEfExtensions).GetMethod(nameof(BinaryLessThanMethod), BindingFlags.Static | BindingFlags.NonPublic);
    private static bool BinaryLessThanMethod(byte[] left, byte[] right)
    {
        throw new NotImplementedException();
    }

    /// <summary>
    /// Filter the query to return only rows where the RowVersion is greater than the version specified
    /// </summary>
    /// <param name="query">The query to filter</param>
    /// <param name="propertySelector">Specifies the property of the row that contains the RowVersion</param>
    /// <param name="version">The row version to compare against</param>
    /// <returns>Rows where the RowVersion is greater than the version specified</returns>
    public static IQueryable<T> WhereVersionGreaterThan<T>(this IQueryable<T> query, Expression<Func<T, byte[]>> propertySelector, byte[] version)
    {
        var memberExpression = propertySelector.Body as MemberExpression;
        if (memberExpression == null) { throw new ArgumentException("Expression should be of form r=>r.RowVersion"); }
        var propName = memberExpression.Member.Name;

        var fooParam = Expression.Parameter(typeof(T));
        var recent = query.Where(Expression.Lambda<Func<T, bool>>(
            Expression.GreaterThan(
                Expression.Property(fooParam, propName),
                Expression.Constant(version),
                false,
                BinaryGreaterThanMethodInfo),
            fooParam));
        return recent;
    }


    /// <summary>
    /// Filter the query to return only rows where the RowVersion is less than the version specified
    /// </summary>
    /// <param name="query">The query to filter</param>
    /// <param name="propertySelector">Specifies the property of the row that contains the RowVersion</param>
    /// <param name="version">The row version to compare against</param>
    /// <returns>Rows where the RowVersion is less than the version specified</returns>
    public static IQueryable<T> WhereVersionLessThan<T>(this IQueryable<T> query, Expression<Func<T, byte[]>> propertySelector, byte[] version)
    {
        var memberExpression = propertySelector.Body as MemberExpression;
        if (memberExpression == null) { throw new ArgumentException("Expression should be of form r=>r.RowVersion"); }
        var propName = memberExpression.Member.Name;

        var fooParam = Expression.Parameter(typeof(T));
        var recent = query.Where(Expression.Lambda<Func<T, bool>>(
            Expression.LessThan(
                Expression.Property(fooParam, propName),
                Expression.Constant(version),
                false,
                BinaryLessThanMethodInfo),
            fooParam));
        return recent;
    }



}
寄意 2024-12-12 00:38:54

这种方法对我来说很有效,并且可以避免篡改原始 SQL:

var recent = MyContext.Foos.Where(c => BitConverter.ToUInt64(c.RowVersion.Reverse().ToArray(), 0) > fromRowVersion);

不过我猜原始 SQL 会更有效。

This method works for me and avoids tampering with the raw SQL:

var recent = MyContext.Foos.Where(c => BitConverter.ToUInt64(c.RowVersion.Reverse().ToArray(), 0) > fromRowVersion);

I would guess however raw SQL would be more efficient.

秋意浓 2024-12-12 00:38:54

我发现这个解决方法很有用:

byte[] rowversion = BitConverter.GetBytes(revision);

var dbset = (DbSet<TEntity>)context.Set<TEntity>();

string query = dbset.Where(x => x.Revision != rowversion).ToString()
    .Replace("[Revision] <> @p__linq__0", "[Revision] > @rowversion");

return dbset.SqlQuery(query, new SqlParameter("rowversion", rowversion)).ToArray();

I found this workaround usefull:

byte[] rowversion = BitConverter.GetBytes(revision);

var dbset = (DbSet<TEntity>)context.Set<TEntity>();

string query = dbset.Where(x => x.Revision != rowversion).ToString()
    .Replace("[Revision] <> @p__linq__0", "[Revision] > @rowversion");

return dbset.SqlQuery(query, new SqlParameter("rowversion", rowversion)).ToArray();
风吹雪碎 2024-12-12 00:38:54

我最终执行了一个原始查询:
ctx.Database.SqlQuery("SELECT * FROM [TABLENAME] WHERE(CONVERT(bigint,@@DBTS) >" + X)).ToList();

I ended up executing a raw query:
ctx.Database.SqlQuery("SELECT * FROM [TABLENAME] WHERE(CONVERT(bigint,@@DBTS) >" + X)).ToList();

放手` 2024-12-12 00:38:54

这是最好的解决方案,但存在性能问题。参数@ver将被强制转换。 where 子句中的强制转换列对数据库有害。

表达式中的类型转换可能会影响查询计划选择中的“SeekPlan”

MyContext.Foos.SqlQuery("SELECT * FROM Foos WHERE Version > @ver", new SqlParameter("ver", lastFoo.Version));

没有演员。 MyContext.Foos.SqlQuery("SELECT * FROM Foos WHERE Version > @ver", new SqlParameter("ver", lastFoo.Version).SqlDbType = SqlDbType.Timestamp);

That is the best solution, but have a performance issue. The parameter @ver will be cast. Cast columns in where clause are bad to the database.

Type conversion in expression may affect "SeekPlan" in query plan choice

MyContext.Foos.SqlQuery("SELECT * FROM Foos WHERE Version > @ver", new SqlParameter("ver", lastFoo.Version));

Without cast. MyContext.Foos.SqlQuery("SELECT * FROM Foos WHERE Version > @ver", new SqlParameter("ver", lastFoo.Version).SqlDbType = SqlDbType.Timestamp);

烂柯人 2024-12-12 00:38:54

这是 EF 6.x 可用的另一种解决方法,它不需要在数据库中创建函数,而是使用模型定义的函数。

函数定义(这位于 CSDL 文件的部分内,如果您使用 EDMX 文件,则位于部分内):

<Function Name="IsLessThan" ReturnType="Edm.Boolean" >
  <Parameter Name="source" Type="Edm.Binary" MaxLength="8" />
  <Parameter Name="target" Type="Edm.Binary" MaxLength="8" />
  <DefiningExpression>source < target</DefiningExpression>
</Function>
<Function Name="IsLessThanOrEqualTo" ReturnType="Edm.Boolean" >
  <Parameter Name="source" Type="Edm.Binary" MaxLength="8" />
  <Parameter Name="target" Type="Edm.Binary" MaxLength="8" />
  <DefiningExpression>source <= target</DefiningExpression>
</Function>
<Function Name="IsGreaterThan" ReturnType="Edm.Boolean" >
  <Parameter Name="source" Type="Edm.Binary" MaxLength="8" />
  <Parameter Name="target" Type="Edm.Binary" MaxLength="8" />
  <DefiningExpression>source > target</DefiningExpression>
</Function>
<Function Name="IsGreaterThanOrEqualTo" ReturnType="Edm.Boolean" >
  <Parameter Name="source" Type="Edm.Binary" MaxLength="8" />
  <Parameter Name="target" Type="Edm.Binary" MaxLength="8" />
  <DefiningExpression>source >= target</DefiningExpression>
</Function>

请注意,我尚未编写使用 Code First 中提供的 API 来创建函数的代码,但类似于以下代码Drew 提出的建议或我前段时间为 UDF 编写的模型约定 https://github.com/divega/UdfCodeFirstSample,应该可以工作

方法定义(这在您的 C# 源代码中):

using System.Collections;
using System.Data.Objects.DataClasses;

namespace TimestampComparers
{
    public static class TimestampComparers
    {

        [EdmFunction("TimestampComparers", "IsLessThan")]
        public static bool IsLessThan(this byte[] source, byte[] target)
        {
            return StructuralComparisons.StructuralComparer.Compare(source, target) == -1;
        }

        [EdmFunction("TimestampComparers", "IsGreaterThan")]
        public static bool IsGreaterThan(this byte[] source, byte[] target)
        {
            return StructuralComparisons.StructuralComparer.Compare(source, target) == 1;
        }

        [EdmFunction("TimestampComparers", "IsLessThanOrEqualTo")]
        public static bool IsLessThanOrEqualTo(this byte[] source, byte[] target)
        {
            return StructuralComparisons.StructuralComparer.Compare(source, target) < 1;
        }

        [EdmFunction("TimestampComparers", "IsGreaterThanOrEqualTo")]
        public static bool IsGreaterThanOrEqualTo(this byte[] source, byte[] target)
        {
            return StructuralComparisons.StructuralComparer.Compare(source, target) > -1;
        }
    }
}

另请注意,我已将这些方法定义为 byte[ 上的扩展方法] ],尽管这不是必要的。我还提供了这些方法的实现,以便在您在查询之外评估它们时它们可以工作,但您也可以选择抛出 NotImplementedException。当您在 LINQ to Entities 查询中使用这些方法时,我们永远不会真正调用它们。
另外,我也没有为 EdmFunctionAttribute“TimestampComparers”设置第一个参数。这必须与概念模型部分中指定的命名空间相匹配。

用法:

using System.Linq;

namespace TimestampComparers
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new OrdersContext())
            {
                var stamp = new byte[] { 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, };

                var lt = context.OrderLines.FirstOrDefault(l => l.TimeStamp.IsLessThan(stamp));
                var lte = context.OrderLines.FirstOrDefault(l => l.TimeStamp.IsLessThanOrEqualTo(stamp));
                var gt = context.OrderLines.FirstOrDefault(l => l.TimeStamp.IsGreaterThan(stamp));
                var gte = context.OrderLines.FirstOrDefault(l => l.TimeStamp.IsGreaterThanOrEqualTo(stamp));

            }
        }
    }
}

Here is yet another workaround available to EF 6.x that doesn't require creating functions in the database but uses model defined functions instead.

Function definitions (this goes inside the section in your CSDL file, or inside section if you are using EDMX files):

<Function Name="IsLessThan" ReturnType="Edm.Boolean" >
  <Parameter Name="source" Type="Edm.Binary" MaxLength="8" />
  <Parameter Name="target" Type="Edm.Binary" MaxLength="8" />
  <DefiningExpression>source < target</DefiningExpression>
</Function>
<Function Name="IsLessThanOrEqualTo" ReturnType="Edm.Boolean" >
  <Parameter Name="source" Type="Edm.Binary" MaxLength="8" />
  <Parameter Name="target" Type="Edm.Binary" MaxLength="8" />
  <DefiningExpression>source <= target</DefiningExpression>
</Function>
<Function Name="IsGreaterThan" ReturnType="Edm.Boolean" >
  <Parameter Name="source" Type="Edm.Binary" MaxLength="8" />
  <Parameter Name="target" Type="Edm.Binary" MaxLength="8" />
  <DefiningExpression>source > target</DefiningExpression>
</Function>
<Function Name="IsGreaterThanOrEqualTo" ReturnType="Edm.Boolean" >
  <Parameter Name="source" Type="Edm.Binary" MaxLength="8" />
  <Parameter Name="target" Type="Edm.Binary" MaxLength="8" />
  <DefiningExpression>source >= target</DefiningExpression>
</Function>

Note that I haven't written the code to create the functions using the APIs available in Code First, but similar to code to what Drew proposed or the model conventions I wrote some time ago for UDFs https://github.com/divega/UdfCodeFirstSample, should work

Method definition (this goes in your C# source code):

using System.Collections;
using System.Data.Objects.DataClasses;

namespace TimestampComparers
{
    public static class TimestampComparers
    {

        [EdmFunction("TimestampComparers", "IsLessThan")]
        public static bool IsLessThan(this byte[] source, byte[] target)
        {
            return StructuralComparisons.StructuralComparer.Compare(source, target) == -1;
        }

        [EdmFunction("TimestampComparers", "IsGreaterThan")]
        public static bool IsGreaterThan(this byte[] source, byte[] target)
        {
            return StructuralComparisons.StructuralComparer.Compare(source, target) == 1;
        }

        [EdmFunction("TimestampComparers", "IsLessThanOrEqualTo")]
        public static bool IsLessThanOrEqualTo(this byte[] source, byte[] target)
        {
            return StructuralComparisons.StructuralComparer.Compare(source, target) < 1;
        }

        [EdmFunction("TimestampComparers", "IsGreaterThanOrEqualTo")]
        public static bool IsGreaterThanOrEqualTo(this byte[] source, byte[] target)
        {
            return StructuralComparisons.StructuralComparer.Compare(source, target) > -1;
        }
    }
}

Note also that I have defined the methods as extension methods over byte[], although this is not necessary. I also provided implementations for the methods so that they work if you evaluate them outside queries, but you can choose as well to throw NotImplementedException. When you use these methods in LINQ to Entities queries, we will never really invoke them.
Also not that I have made the first argument for EdmFunctionAttribute “TimestampComparers”. This has to match the namespace specified in the section of your conceptual model.

Usage:

using System.Linq;

namespace TimestampComparers
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new OrdersContext())
            {
                var stamp = new byte[] { 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, };

                var lt = context.OrderLines.FirstOrDefault(l => l.TimeStamp.IsLessThan(stamp));
                var lte = context.OrderLines.FirstOrDefault(l => l.TimeStamp.IsLessThanOrEqualTo(stamp));
                var gt = context.OrderLines.FirstOrDefault(l => l.TimeStamp.IsGreaterThan(stamp));
                var gte = context.OrderLines.FirstOrDefault(l => l.TimeStamp.IsGreaterThanOrEqualTo(stamp));

            }
        }
    }
}
风向决定发型 2024-12-12 00:38:54

(Damon Warren 的以下回答是从此处复制过来的):

这是我们所做的解决此问题:

使用如下所示的比较扩展:

public static class EntityFrameworkHelper
    {
        public static int Compare(this byte[] b1, byte[] b2)
        {
            throw new Exception("This method can only be used in EF LINQ Context");
        }
    }

然后您可以执行

byte[] rowversion = .....somevalue;
_context.Set<T>().Where(item => item.RowVersion.Compare(rowversion) > 0);

此操作无需 C# 实现即可工作的原因是因为从未实际调用比较扩展方法,并且 EF LINQ 简化了 x.compare(y) > 0 下降到 x > y

(The following answer by Damon Warren is copied over from here):

Here is what we did to solve this:

Use a compare extension like this:

public static class EntityFrameworkHelper
    {
        public static int Compare(this byte[] b1, byte[] b2)
        {
            throw new Exception("This method can only be used in EF LINQ Context");
        }
    }

Then you can do

byte[] rowversion = .....somevalue;
_context.Set<T>().Where(item => item.RowVersion.Compare(rowversion) > 0);

The reason this works without a C# implementation is because the compare extension method is never actually called, and EF LINQ simplifies x.compare(y) > 0 down to x > y

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文