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