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.
