How to query Code First entities based on rowversi

问题:

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?

回答1:

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

回答2:

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.)

回答3:

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

回答4:

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.

回答5:

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

回答6:

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

回答7:

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

回答8:

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 &lt; 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 &lt;= 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 &gt; 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 &gt;= 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));

            }
        }
    }
}

回答9:

I extended jmn2s 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;
    }



}