NHibernate: Setting datatypes and lengths explicitly for query parameters

Question

I am using NHibernate mapping by code and sql server 2012. I am also using the Sql2008ClientDriver as the driver in the session factory. I have been trying to figure out how to map the exact sql type and length to the C# objects representing the table. The problem I am getting is around the data types specified in the sql generated, specifically strings.

For example:

public virtual string SomeProperty { get; set; }

This translates to a nvarchar(4000) parameter when the query is generated, but this column is a char(6) in the database. Is it possible to specify this some how in the mapping?

I believe there is a performance loss, as sql server is doing a conversion before executing the query.

I've tried this (and it does not work) : http://notherdev.blogspot.com.au/2012/01/mapping-by-code-property.html

Any ideas?

Diego Mijelshon · Answer 1 · 2013-03-26T20:57:14.290

1

Here's a loquacious mapping that results in a working char(6) field.

Property(x => x.SomeProperty,
         pm =>
         {
             pm.Type(NHibernateUtil.AnsiString);
             pm.Length(6);
             pm.Column(cm => cm.SqlType("char(6)"));
         });

Out of the box support for this could be better.

edited Mar 26 '13 at 20:57

answered Mar 24 '13 at 23:24

Diego Mijelshon

52,548
16
116
154

Hey @Diego this got flagged for low quality for some reason, I'm sure you know to add some explanation :) only suggestion I can give – Christian Stewart Mar 24 '13 at 23:43
Hey @Diego, I've tried this, and im now getting varchar(8000). I'm not sure if this helps, but I'm using NHibernate's sql2008clientdriver as my driver against a 2012 db, as well as the mssql2012dialect. – Prabu Mar 24 '13 at 23:48
@ChristianStewart I could add a title, I guess. But since it was a "how do I do this" type of question, I just answered with that... – Diego Mijelshon Mar 25 '13 at 00:02
@pwee167 where are you getting varchar(8000)? What is your NH version? I have just ran it, and I definitely creates a char(6), although it uses 8000 when passing a query parameter (not very important) – Diego Mijelshon Mar 25 '13 at 00:06
@DiegoMijelshon I'm using NH 3.3.1.4. I am actually talking about the query parameter. Is there a reason why this is not important? I've run the same query via SSMS with the correct parameter types/lengths, and it seems to be a significant performance gain (20ms => 4ms). – Prabu Mar 25 '13 at 02:27
You might have a valid point. This seems to be the default behavior (see NH-3130 and others). It might be worth asking about this in the dev list. – Diego Mijelshon Mar 25 '13 at 14:28
I'm tempted to flag for low quality also. Unsure WTF mapping for NHibernate that is. – Phill Mar 26 '13 at 02:30
@DiegoMijelshon ohhh thats the new mapping in NH3, think I'll stick with FNH myself :D, cheers. – Phill Mar 27 '13 at 00:36
@Phil Does this issue not occur with FNH? I'm thought the cause of the issue lies in the SQL Client driver. – Prabu Mar 27 '13 at 04:02
@pwee167 FNH is just another mapping framework for NHibernate. If the bug is in the SQL Client Driver then using FNH wouldn't have any effect. My original comment was that I didn't realise the mapping was actually the new Code Mapping in NHibernate 3.2 or 3.3 (not sure when it was introduced) – Phill Mar 27 '13 at 07:27
@Phil, I'm aware of what FNH is. I was just wondering if you noticed the datatypes/lengths issue I'm describing in your experience with FNH. I suspected before (and verified) the sql client driver is the culprit. I believe mapping-by-code was introduced in 3.3. They took a lot of inspiration from FNH. – Prabu Mar 27 '13 at 22:20

score 0 · Answer 2 · answered Mar 24 '13 at 23:01

0

In your mapping file you can specify the sql-type:

<property name="SomeProperty" type="String">
   <column name="ColumnName" sql-type="char(6)" />
</property>

answered Mar 24 '13 at 23:01

Darren

68,902
24
138
144

NHibernate: Setting datatypes and lengths explicitly for query parameters

2 Answers2