How to query Code First entities based on rowversion/timestamp value?

Question

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?

Answer 1

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

Answer 2

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

Answer 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

Answer 4

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



}