public class Census
{
public virtual int CensusID { get; set; }
public virtual string SSN { get; set; }
}
I use fluent nh which outputs the below hbm file
public CensusMap()
{
Id(x => x.CensusID);
Map(x => x.SSN);
}
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-
access="property" auto-import="true" default-cascade="none" default-
lazy="true">
<class xmlns="urn:nhibernate-mapping-2.2" mutable="true"
name="Domain.Census, Domain, Version=0.0.0.0, Culture=neutral,
PublicKeyToken=eb0a7665f4ab3c54" table="`Census`">
<id name="CensusID" type="System.Int32, mscorlib, Version=4.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089">
<column name="CensusID" />
<generator class="identity" />
</id>
<property name="SSN" type="System.String, mscorlib,
Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<column name="SSN" />
</property>
</class>
</hibernate-mapping>
using (var tx = session.BeginTransaction())
{
census = session.CreateCriteria(typeof(Census))
.Add(Restrictions.Eq("SSN", "123456789"))
.UniqueResult<Census>();
tx.Commit();
}
And then I use NHProf to show the execution plan. This shows the
CONVERT_IMPLICIT
If you try different strings in this query you will notice the
omission of quotes if the string happens to be a number without
leading zeroes, and this directly correlates to the sql server using a
conversion or not.
If you copy the query from NHProf and run it in sql, you can see the
execution plan there as well. Run the same query with and without
quotes, and you'll get a conversion or not depending on which you use.
Thanks
Ralph
On May 19, 6:01 pm, José F. Romaniello <[email protected]> wrote:
> You don't need to do anything about *quotes* if that is your case.
> Send a test case with a simple example, class + mapping + test.
>
> 2011/5/19 Ralph Balck <[email protected]>
>
>
>
>
>
>
>
> > string in .net
> > nvarchar(9) in the database
>
> > On May 19, 5:16 pm, José F. Romaniello <[email protected]> wrote:
> > > What is the type of the SSN property in .net?
>
> > > 2011/5/19, Ralph Balck <[email protected]>:
>
> > > > we use CreateCriteria mostly
>
> > > > using (var tx = session.BeginTransaction())
> > > > {
> > > > census = session.CreateCriteria(typeof(Census))
> > > > .Add(Restrictions.Eq("SSN", "123456789"))
> > > > .UniqueResult<Census>();
> > > > tx.Commit();
> > > > }
>
> > > > we've also tried usign HQL.
> > > > and we've tried specifying the data type as string.
>
> > > > On May 19, 4:10 pm, José F. Romaniello <[email protected]> wrote:
> > > >> How are you writing the query in nhibernate?
>
> > > >> 2011/5/19, Ralph Balck <[email protected]>:
>
> > > >> > 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.
>
> > > >> --
> > > >> Enviado desde mi dispositivo móvil
>
> > > > --
> > > > 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.
>
> > > --
> > > Enviado desde mi dispositivo móvil
>
> > --
> > 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.
--
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.
