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
_______________________________________________
Firebird-net-provider mailing list
Firebird-net-provider@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
