2

I have a requirement to work through several tables that need to be synchronized/backed up. All of these tables' classes implement ITrackModifiedDate:

interface ITrackModifiedDate
{
    DateTime ModifiedDate { get; set; }
}

I need to work through these in batches, which means I need to bookmark where I last got up to. So I can sort by ModifiedDate, and just keep track of my last ModifiedDate. But there's a wrinkle in the plan: it can easily happen that multiple records have the identical ModifiedDate. That means I need a secondary identifier, and the only thing I can generically rely on being there is the key field, which is invariably a Guid.

I got some help in the Expression stuff from here, but when I tried tinkering to add in the "greater than" clause, things broke.

async Task<ICollection<T>> GetModifiedRecords<T>(DateTime modifiedSince, Guid lastId) where T : class, ITrackModifiedDate
{
    var parameterExp = Expression.Parameter(typeof(T), "x");
    var propertyExp = Expression.Property(parameterExp, keyField);
    var target = Expression.Constant(lastId);
    var greaterThanMethod = Expression.GreaterThan(propertyExp, target);
    var lambda = Expression.Lambda<Func<T, bool>>(greaterThanMethod, parameterExp);

    var query = db.Set<T>()
                  .Where(t => t.ModifiedDate > modifiedSince ||
                              t.ModifiedDate == modifiedSince && lambda.Invoke(t));

    var orderByExp = Expression.Lambda(propertyExp, parameterExp);
    var thenByMethodGeneric = typeof(Queryable)
                               .GetMethods()
                               .Single(mi => mi.Name == "ThenBy" && mi.GetParameters().Length == 2);

    var thenByMethod = thenByMethodGeneric.MakeGenericMethod(typeof(T), propertyExp.Type);
    // first order by date, then id
    query = query.OrderBy(t => t.ModifiedDate)
                 .AsQueryable();
    query = (IQueryable<T>)thenByMethod.Invoke(null, new object[] { query, orderByExp });
    return await query.ToListAsync();
}

Attempting to run this query results in:

System.InvalidOperationException: The binary operator GreaterThan is not defined for the types 'System.Guid' and 'System.Guid'.

Oh dear. It seems Guids, like humans, don't like being compared with each other. Either that, or I'm using the wrong comparison expression.

The obvious solution that jumps to mind is to convert the Guid to a string for comparison purposes, but (a) that seems a little inefficient, and (b) I don't know how to write an Expression that converts a Guid to a string.

Is converting to a string the right way to go? If so, what Expression will do the job? If not, what's the correct approach?

Shaul Behr
  • 36,951
  • 69
  • 249
  • 387
  • It's less whether it's possible to convert a Guid to a string -- it's easy, call its ToString method with `Expression.Call` -- but whether the thing that translates your expression to SQL (probably EF) knows what to do with that. – canton7 Mar 26 '19 at 11:23
  • @canton7 The other issue I just thought of with sorting string-wise is that it may not be consistent with the way my database (Postgres, FWIW) handles the order-by clause `order by ModifiedDate, MyTableId`. I have a feeling that the db ordering is not string-wise. – Shaul Behr Mar 26 '19 at 12:15
  • https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/how-to-call-model-defined-functions-in-queries https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/how-to-call-model-defined-functions-as-object-methods - Check these links out – Alexandru Clonțea Mar 26 '19 at 12:18

1 Answers1

3

The general approach is to replace the unsupported Guid operators with Guid.CompareTo(Guid) calls, e.g. instead of

guidA > guidB

use

guidA.CompareTo(guidB) > 0

In your case, replace

 var greaterThanMethod = Expression.GreaterThan(propertyExp, target);

with

var compareTo = Expression.Call(propertyExp, "CompareTo", Type.EmptyTypes, target);
var greaterThanMethod = Expression.GreaterThan(compareTo, Expression.Constant(0));

This works for most of the query providers (LINQ to Objects, LINQ To Entities (EF6)). Unfortunately doesn't work with EF Core 2.x which requires a different approach. If that's the case, register the custom GuidFunctions class as explained in the linked answer, and use this instead:

var greaterThanMethod = Expression.Call(
    typeof(GuidFunctions), "IsGreaterThan", Type.EmptyTypes,
    propertyExp, target);
Shaul Behr
  • 36,951
  • 69
  • 249
  • 387
Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343