[FB-Tracker] Created: (DNET-1008) String concat - db index

[Rand Random (JIRA)]

String concat - db index
------------------------

Key: DNET-1008
URL: http://tracker.firebirdsql.org/browse/DNET-1008
Project: .NET Data provider
Issue Type: Bug
Components: Entity Framework
Affects Versions: 7.10.1.0
Reporter: Rand Random
Assignee: Jiri Cincura


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.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira