The new Linq provider generates LIKE statements in a form that is not supported by some databases
Description
Environment
Activity
Diego Mijelshon July 27, 2010 at 2:33 PM
I understand that, when using other projections, the new construct is useful (and I'm fine with that being unsupported in some DBs)
However, I don't see why we can't change this:
return treeBuilder.Like(
visitor.Visit(targetObject).AsExpression(),
treeBuilder.Concat(
visitor.Visit(arguments[0]).AsExpression(),
treeBuilder.Constant("%")));
...to check if a client-side expression is being used, and generate it as a single argument.
If you reopen this issue, I'll try to provide a patch.
Fabio Maulo July 27, 2010 at 2:11 PM
But the problem is not in NH.
In LINQ we are supporting more complicated scenarios than done by criteria.
For example: StartWith(myPrefix + toFind)
When the parameter of StartWith is an expression, including or not a fetched property (a column in the select) we can't simply transform it to a constant.
Please add the issue to the Informix issue tracker.
Thanks.
Details
Details
Assignee
Reporter
Components
Affects versions
Priority
Major
For a query like db.Users.Where(user => user.Name.StartsWith("A")), the old provider generates:
SELECT
this_.id as id0_0_,
this_.Name as Name0_0_
FROM
[User] this_
WHERE
this_.Name like @p0;
@p0 = 'X%' [Type: String (4000)]
...which is consistent with
session.CreateCriteria<User>().Add(Restrictions.Like("Name", "A", MatchMode.Start));
The new provider generates the following:
select
user0_.id as id0_,
user0_.Name as Name0_
from
[User] user0_
where
user0_.Name like (@p0+'%');
@p0 = 'X' [Type: String (4000)]
(note the parameter concatenation)
This breaks in DBs like Informix, which do not support string concatenation with a parameter.