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