I've found a hacky workaround. If you use Like instead of Eq in your expression, then sql will be forced to compare the items as strings even if nhibernate does not wrap the argument in quotes. It will use indexes as well. As long as you do not include any wildcards it should have the same behavior as Eq.
just for fyi, if the string begins with 0 nhibernate will use quotes. and if any of the characters in the string are non digits, it will also use quotes. It only seems to omit quotes if the argument is a full number. If anyone knows a non hacky way to get around this, I'd still love to know. thanks! Ralph On May 19, 2:01 pm, Ralph Balck <[email protected]> wrote: > I've got an SSN column (nvarchar) in the db and a string property > called SSN in my code. All is well except we started getting very > slow performance when doing a query on SSN even though there is an > index. It turns out that because the SSNs are all digits, nhibernate > is sending a query that looks like this to the server > > select * from Census where SSN=265145847 > > and this is causing the sql server to call CONVERT_IMPLICIT on each > value in the database to convert it to an integer to do the > comparison. > > what i want is a query that looks like this: > > select * from Census where SSN='265145847' > > which sql will be able to query normally and quickly. The only > difference being the single quotes. > > I've tried explicitly setting the type in the mapping file and in the > query, but nothing seems to force quotes for a string if it is all > digits. > > anyone got ideas? > thanks! > Ralph -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
