Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

String concat - db index [DNET1008] #922

Closed
firebird-automations opened this issue Jan 15, 2021 · 4 comments
Closed

String concat - db index [DNET1008] #922

firebird-automations opened this issue Jan 15, 2021 · 4 comments

Comments

@firebird-automations
Copy link

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.

@firebird-automations
Copy link
Author

Commented by: Kjell Rilbe (kjellrilbe)

Maybe I'm missing something, but is there any particular reason to not simply generate this?

WHERE "f"."ITEM1" STARTING WITH _UTF8'HelloWorld'

Any fringe cases where that would cause incorrect logic perhaps?

@firebird-automations
Copy link
Author

Modified by: Rand Random (rand.random)

Component: Entity Framework Core [ 10160 ]

Component: Entity Framework [ 10110 ] =>

@cincuranet
Copy link
Member

@krilbe (hope I found the correct person) It's simply because the LIKE must be handled anyway and the code path is already there.

@Rand-Random Yeah, for constant patterns it's doable. Will implement it shortly.

@cincuranet
Copy link
Member

Done in bb2957c.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants