Dynamic EF/Linq query comparing Guid/UniqueIdentifier values

Question

I need to generate a where clause against a Guid/UniqueIdentifier column using a greater than statement. My problem is I have to dynamically generate the query. Here's an example of what I want:

myTable.Where(x => Guid.Empty.CompareTo(x.ADifferentGuidColumn) > 0)

Which generates this SQL (I'm using LINQPad to get the SQL here):

-- Region Parameters
DECLARE @p0 UniqueIdentifier = '00000000-0000-0000-0000-000000000000'
-- EndRegion
SELECT <columns...>
FROM [myTable] AS [t0]
WHERE (@p0 > [t0].[ADifferentGuidColumn])

This is what I have. This is in a much larger generic method where the T parameter is the table/domain model object. It compiles, but blows up when creating startPointBody saying The binary operator GreaterThan is not defined for the types 'System.Guid' and 'System.Guid'.

Guid startPoint = Guid.NewGuid(); //actual value in real code...
IQueryable<T> set = context.Set<T>().AsQueryable();
var parameter = Expression.Parameter(typeof(T), "x");
//find primary key
IProperty primaryKey = context.Model.FindEntityType(typeof(T).FullName).FindPrimaryKey().Properties.FirstOrDefault();
//expression for column we're filtering
var startPointMember = Expression.Property(parameter, primaryKey.Name);
ConstantExpression startPointConstant = Expression.Constant(startPoint, startPoint.GetType());
var startPointBody = Expression.GreaterThan(startPointMember, startPointConstant);
var startPointWhereExpression = Expression.Lambda<Func<T, bool>>(startPointBody, parameter);
query = query.Where(startPointWhereExpression);

You can see in the example of what I want that I'm actually using Guid.CompareTo(), but I have no idea how to write that in this dynamic fashion. I was hoping I could cheat since the generated SQL is just doing a simple > comparison, but EF caught me.

I'm struggling with what you're trying to do here. Are you saying that you don't know in advance which table you will be `SELECT`ing against? And that's the reason why a dynamic query is needed? — catfood, Sep 03 '19 at 21:21
Yeah, I don't know what the table is, hence the dynamic stuff. It's been running for a few weeks now. I needed to add this where clause today and ran into this wall since `>` doesn't exist/isn't implemented for Guid types. — Tim Coker, Sep 03 '19 at 21:24
I'm walking out of my apartment to get some food and chew on this, but would it be possible to just implement GreaterThan for Guid types? — Tim Coker, Sep 03 '19 at 21:26
See [C# Expression to use Guid bookmark](https://stackoverflow.com/questions/55355917/c-sharp-expression-to-use-guid-bookmark/55357174#55357174) and [Entity Framework Core: Guid Greater Than for Paging](https://stackoverflow.com/questions/54920200/entity-framework-core-guid-greater-than-for-paging/54929035#54929035) — Ivan Stoev, Sep 05 '19 at 11:53

Tim Coker · Accepted Answer · 2019-09-05T03:57:56.707

Here's a working solution with example data. I put this together in linqpad connected to a local sql server instance in docker.

Here's the table and data (some guids are hard coded in the demo code):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
    [col1] [uniqueidentifier] NOT NULL,
    [col2] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO

insert into test (col1,col2) values ('d3aaaf86-d4e5-4d77-897e-1b36c1100448', '09e0073a-be0e-405a-9564-d34056c8e42a')
insert into test (col1,col2) values ('d3aaaf86-d4e5-4d77-897e-1b36c1100448', 'a7a5aee0-87f4-42bb-90ac-dac7b52bffbf')
insert into test (col1,col2) values ('d3aaaf86-d4e5-4d77-897e-1b36c1100448', '104ab3c0-8521-453f-bf48-552358618a90')
insert into test (col1,col2) values ('d3aaaf86-d4e5-4d77-897e-1b36c1100448', '6128657b-5c84-4c95-afbf-4bfe06c2c7ea')
insert into test (col1,col2) values ('d3aaaf86-d4e5-4d77-897e-1b36c1100448', '96bc5a4f-0baf-44fb-a85c-fbddcca82ff9')
insert into test (col1,col2) values ('ac15b74b-593f-4d28-9f80-46cc84a89e79', 'db4621e0-b6fe-40ad-a4c5-601f875c5bbf')
insert into test (col1,col2) values ('a5071253-3a12-45c8-a495-d96bb56f49c9', 'a4536fa3-922a-43b0-a568-abfcf2daba9f')
insert into test (col1,col2) values ('a3cf9625-787a-4d91-a692-e2db8eb246a2', 'b7b7d1f9-b9cc-4201-bf75-40b0c4c2d301')

And here's the full program you can run in LINQPad:

void Main()
{
    Test<Test>();
}

static void Test<T>() where T : class
{
    PropertyInfo propertyInfo = typeof(T).GetProperty("col1");

    var parameter = Expression.Parameter(typeof(T), "x");

    //create where clause expression
    var orgIdMember = Expression.Property(parameter, "col1");
    ConstantExpression orgIdConstant = Expression.Constant(new Guid("d3aaaf86-d4e5-4d77-897e-1b36c1100448"), typeof(Guid));
    var orgIdBody = Expression.Equal(orgIdMember, orgIdConstant);
    var orgIdWhereExpression = Expression.Lambda<Func<Test, bool>>(orgIdBody, parameter); 

    TypedDataContext d = new TypedDataContext();

    var query = d.Tests.AsQueryable().Where(orgIdWhereExpression);

    //create start point expression 
    Guid startPoint = new Guid("09e0073a-be0e-405a-9564-d34056c8e42a");

    var startPointMember = Expression.Property(parameter, "col2");
    ConstantExpression startPointConstant = Expression.Constant(startPoint, startPoint.GetType());
    var left = Expression.Call(startPointMember, typeof(Guid).GetMethod("CompareTo", new Type[] { typeof(Guid) }), startPointConstant);
    var right = Expression.Constant(1);
    var startPointBody = Expression.Equal(left, right);
    var startPointWhereExpression = Expression.Lambda<Func<Test, bool>>(startPointBody, parameter);
    query = query.Where(startPointWhereExpression);

    MemberExpression keyMember = Expression.Property(parameter, "col1");
    var keyExpression = Expression.Lambda<Func<Test, Guid>>(keyMember, parameter);
    query = query.OrderBy(keyExpression);

    query.ToList().Dump();
}

There's a LOT of extra crap in here, but the actual answer to my question is where I define the left variable and the 4 lines after. I did all of this in LINQPad because of it's SQL feature, which shows this SQL as the output:

-- Region Parameters
DECLARE @p0 UniqueIdentifier = '09e0073a-be0e-405a-9564-d34056c8e42a'
DECLARE @p1 UniqueIdentifier = 'd3aaaf86-d4e5-4d77-897e-1b36c1100448'
-- EndRegion
SELECT [t0].[col1] AS [Col1], [t0].[col2] AS [Col2]
FROM [test] AS [t0]
WHERE ([t0].[col2] > @p0) AND ([t0].[col1] = @p1)
ORDER BY [t0].[col1]

EDIT: it appears theres a bug/deficiency in EF Core's query generator when creating an expression like what I'm trying to do with starPointWhereExpression. In my testing, it seems that when it gets to that expression, it gives up and tries to execute what it has against the server and saves any further expressions for local processing. I say this because after where this code is running, I add Skip() and Take() expressions. When the where expression comparing Guids is in the pipeline, the skip and take statements are NOT in the generated SQL. If I don't add it to the pipeline, those statements do make it into the generated SQL.

EDIT 2: I filed a bug on github about this. This will be addressed in EF Core 3. As of this writing (tested against EF Core 3 preview 9), the generated SQL is definitely odd and could use some work.

Dynamic EF/Linq query comparing Guid/UniqueIdentifier values

1 Answers1