String concat - db index [DNET1008] #922
Description
Submitted by: Rand Random (rand.random)
Is it possible to disable in EF provider to parse eg. StartsWith method like this
WHERE "f"."ITEM1" IS NOT NULL AND (("f"."ITEM1" LIKE _UTF8'HelloWorld' || _UTF8'%') AND (LEFT("f"."ITEM1", CHARACTER_LENGTH(_UTF8'HelloWorld')) = _UTF8'HelloWorld'))
but instead to not concat the '%' character eg.
WHERE "f"."ITEM1" IS NOT NULL AND (("f"."ITEM1" LIKE _UTF8'HelloWorld%') AND (LEFT("f"."ITEM1", CHARACTER_LENGTH(_UTF8'HelloWorld')) = _UTF8'HelloWorld'))
The first query won't use the index of the database, which is a performance lose.
I know there are the options
WithExplicitStringLiteralTypes
WithExplicitParameterTypes
which I have marked as false, so I was wondering if there maybe something similar for my case somewhere hidden.
If there isn't a configuration available already, I would like to ask if someone could point me in the right direction to make the necessary changes in the EF provider.
I need to query a rather big table, and not using the index isn't an option, since the performance drops drastically.
I know I could manually write the "optimized" query myself and use
var foos = context.Foos.FromSqlRaw("SELECT * FROM Foos where Item like 'HelloWorld%'").ToList();
insead of
var foos = context.Foos.Where(x => x.Items.StartsWith("HelloWorld")).ToList();
But I am - currently - not writing those queries my self but rather rely on the filter mechanism of controls eg. DataGrid, FilterEditor.
Even if there is a solution that "intercepts" the query before sending it to the database, where I foolishly could write
sqlQuery = sqlQuery.("' || _UTF8'%'", "%'");
would also already be enough for my case.