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

[nhibernate-bot]

Nicolás Sabena created an issue — 4th November 2013, 19:25:46:

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 — 4th November 2013, 20:16:11:

Could you please provide a test case?

---

Nicolás Sabena added a comment — 4th November 2013, 23:13:12:

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 — 8th June 2017, 17:15:17:

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 — 8th June 2017, 18:16:34:

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 — 8th June 2017, 18:51:06:

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 — 8th June 2017, 19:34:17:

-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 — 9th June 2017, 12:29:17:

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 — 9th June 2017, 14:45:57:

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 — 9th June 2017, 14:59:48:

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 — 9th June 2017, 16:25:12:

Yes, thanks for your help Frederic

[bahusoid]

Hm.. All of these methods generate HqlLike node. So parameter detection implemented in

nhibernate-core/src/NHibernate/Hql/Ast/ANTLR/Tree/BinaryLogicOperatorNode.cs

Lines 56 to 66 in 74b97af

	IType lhsType = ExtractDataType( lhs );
	IType rhsType = ExtractDataType( rhs );
	if ( lhsType == null )
	{
	lhsType = rhsType;
	}
	if ( rhsType == null )
	{
	rhsType = lhsType;
	}

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:

nhibernate-core/src/NHibernate/Linq/Visitors/ParameterTypeLocator.cs

Lines 159 to 164 in ec634f5

	// No related MemberExpressions was found, guess the type by value or its type when null.
	// When a numeric parameter is compared to different columns with different types (e.g. Where(o => o.Single >= singleParam || o.Double <= singleParam))
	// do not change the parameter type, but instead cast the parameter when comparing with different column types.
	return constantExpression.Value != null
	? ParameterHelper.TryGuessType(constantExpression.Value, sessionFactory, namedParameter.IsCollection)
	: ParameterHelper.TryGuessType(constantType, sessionFactory, namedParameter.IsCollection);

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

[bahusoid]

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

[bahusoid]

For possible fix see #2793 (comment)