search

nhibernate / nhibernate-core

NHibernate Object Relational Mapper
https://nhibernate.info
GNU Lesser General Public License v2.1
2.13k stars 927 forks source link

NH-3565 - StartsWith / EndsWith / Contains don't use correct AnsiString type #1166

Open nhibernate-bot opened 7 years ago

nhibernate-bot commented 7 years ago

Nicolás Sabena created an issue — :

If I have a mapping with a property of type AnsiString:

<class Name="Person"... >
    <property name="LastName" type="AnsiString">

and I query:

sesión.Query<Person>.Where(x => x.Name.StartsWith("Something"));

NH translates this into

SELECT ...
FROM Person WHERE name like (@p0 + '%')

but @p0 is defined as string(nvarchar) instead of AnsiString(varchar), which gives terrible performance in the DB Engine as the types don't match.

Equality and other operators mantain correct type. Doing this query with QueryOver also allows correct type usage.

I see in code that the StartsWithGenerator, as well as the EndsWithGenerator and ContainsGenerator, uses Concat, that seems to be the cause of the problem, but I'm a bit lost here...

Alexander Zaytsev added a comment — : Could you please provide a test case?

Nicolás Sabena added a comment — : Run the test called 'StartsWithUsesRightParameterType' and check the generated SQL output. You'll see that the first query (equality) defines the parameter as AnsiString, while the second one ("StartsWith") defines the parameter as String.

Thanks a lot, Nicolas

paul added a comment — : Any news/workaround on that bug? Querying a big table (>3Millons) using linq contains is taking about 40seconds (because nvarchar(4000)). Changing manually to nvarchar(100) and the query takes 3seconds.

Frédéric Delaporte added a comment — : I do not think any progress has been made on that subject. PR welcome of course.

A workaround could be to use NHibernate.Linq.SqlMethods.Like extension method instead. Please drop a note if you test it.

sesión.Query<Person>.Where(x => x.Name.Like("Something%"));

Maybe using MappedAs extension method on the string parameter could help too, within the string methods or the Like extension.

sesión.Query<Person>.Where(x => x.Name.StartsWith("Something".MappedAs(NHibernateUtil.AnsiString)));

paul added a comment — : Hey Frederic,

I tried with the MappedAs extension and instead of a nvarchar(4000), I got a varchar(8000). Its a good start but how can I set length now ? It does not use my mapping definition at all.

Thanks!

Frédéric Delaporte added a comment — : -MappedAs does not currently allow to specify the type length/precision/scale. Maybe this could be a new feature, which would add some MappedAs overload for this.-

MappedAs is for adjusting the type of what is converted to a query parameter: it does not try to infer anything from "nearby" entities. So those entities mappings are not taken into account for setting the parameter type characteristics, and this is by design.

paul added a comment — : Thanks again... Do you known any way to set type/length in a Linq query Contains? I am willing to modify the source to do that, but I dont known where I should look,

Frédéric Delaporte added a comment — : First, does the parameter length causes any performance issue as the parameter type do? Maybe is it not worth it to adjust it.

Then, parameters in Linq queries are automatically extracted from literals found in the lambda. There are no places to adjust the resulting DbParameter, excepted with the MappedAs extension. So if you want some way to set a parameter length, the best place is currently to check how MappedAs works -then add to it an overload taking the length and do required changes for having it working-. MappedAs is processed in an expression tree visitor which detect it by reflection, so to find that in NHibernate sources, search it by name, not just by references.

If you want to add this functionality, please add a new Jira issue, since it would not be a fix but a new functionality giving a workaround. And check contributing guidelines.

Frédéric Delaporte added a comment — : As written by Alexander on this PR upon your request, MappedAs can already specify the length.

x.MappedAs(NHibernate.Type.TypeFactory.Basic("AnsiString(200)"))

paul added a comment — : Yes, thanks for your help Frederic

bahusoid commented 3 years ago

Hm.. All of these methods generate HqlLike node. So parameter detection implemented in https://github.com/nhibernate/nhibernate-core/blob/74b97af3474d42311a46dd6ec832a11ddf7ae689/src/NHibernate/Hql/Ast/ANTLR/Tree/BinaryLogicOperatorNode.cs#L56-L66

should handle and apply proper parameter type (where lhs is mapped property with known type, and rhs - constant parameter)

But this functionality is broken because parameter is guessed and specified for constant as StringType when LINQ query is processed: https://github.com/nhibernate/nhibernate-core/blob/ec634f50ff2a36ce1f5705a361269d47a9ea1de8/src/NHibernate/Linq/Visitors/ParameterTypeLocator.cs#L159-L164

If this guessing is removed - parameter is properly applied as AnsiString.

bahusoid commented 3 years ago

Oups.. Without guessing parameter detection only properly works for Like. It's still broken for StartsWith and other methods.

bahusoid commented 3 years ago

For possible fix see https://github.com/nhibernate/nhibernate-core/pull/2793#issuecomment-851673251