How to query Code First entities based on rowversion/timestamp value?
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));
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.)
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;
}
}
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