CODEWITHSUNDEEP
c#sqlsql-servernhibernatefluent-nhibernate
mobill
mobill

Reputation: 599

Fluent NHibernate mapping to a SQL Server CHAR(10) ID column

New to both NHibernate and Fluent NHibernate and I'm trying to resolve a performance problem in some inherited code caused by a conversion of a CHAR(10) column to NVARCHAR.

From SQL Profiler:

exec sp_executesql N'select mytestclas0_.LinkId as LinkId45_, 
   mytestclas0_.Href as Href45_, 
   mytestclas0_.LinkActive as LinkActive45_ 
from MessageLinks mytestclas0_ 
where mytestclas0_.LinkId=@p0',N'@p0 nvarchar(4000)',@p0=N'BzE2T48HMF'

You can see the ID coming in from NHibernate is cast as a NVARCHAR.

Table definition:

CREATE TABLE [dbo].[MyTable](
    [ID] [int] NULL,
    [Href] [nvarchar](1000) NULL,
    [LinkActive] [bit] NOT NULL,
    [LinkId] [char](10) NOT NULL
 )

The class file:

public class MyTestClass {
    public MyTestClass() {}

    public virtual string LinkId{ get; set; }
    public virtual string Href{ get; set; }
    public virtual bool LinkActive { get; set; }
}

The mapping file:

  public class MyTestClassMapping : ClassMap<MyTestClass> {
    public MyTestClassMapping() {
      Table("MyTable");

      Id(x => x.LinkId).Length(10);
      Map(x => x.LinkId);
      Map(x => x.Href);
      Map(x => x.LinkActive);
    }
  }

I have tried a number of different things with the datatype of the LinkId and the mapping file, including these mappings:

    Id(x => x.LinkId).CustomSqlType("char(10)");
    Id(x => x.LinkId).Length(10).CustomSqlType("char");
    Id(x => x.LinkId).CustomSqlType("char");

I'm looking for a pointer to an example or documentation that explains how to get the ID passed in by NHibernate cast to a CHAR(10).

Thanks in advance for any help.

Upvotes: 4

Views: 2969

Answers (3)

MichaC
MichaC

Reputation: 13381

NHibernate MsSql2000Dialect and following versions define AnsiString correctly, also supporting length etc.

But, the current implementation of the SqlDriver got a change last year, see https://nhibernate.jira.com/browse/NH-3036 for details of the fix.

The code does now ignore the specified length and always uses the default. Default for AnsiString and some others is varchar(8000)

RegisterColumnType(DbType.AnsiString, SqlClientDriver.MaxSizeForLengthLimitedAnsiString, "VARCHAR($l)");

code from SqlDriver: setting default: 

protected static void SetDefaultParameterSize(IDbDataParameter dbParam, SqlType sqlType)
{
    switch (dbParam.DbType)
    {
        case DbType.AnsiString:
        case DbType.AnsiStringFixedLength:
            dbParam.Size = MaxSizeForLengthLimitedAnsiString;
            break;

Bugfix:

    // Used from SqlServerCeDriver as well
    public static void SetVariableLengthParameterSize(IDbDataParameter dbParam, SqlType sqlType)
    {
        SetDefaultParameterSize(dbParam, sqlType);

        // no longer override the defaults using data from SqlType, since LIKE expressions needs larger columns
        // https://nhibernate.jira.com/browse/NH-3036
        //if (sqlType.LengthDefined && !IsText(dbParam, sqlType) && !IsBlob(dbParam, sqlType))
        //{
        //  dbParam.Size = sqlType.Length;
        //}

        if (sqlType.PrecisionDefined)
        {
            dbParam.Precision = sqlType.Precision;
            dbParam.Scale = sqlType.Scale;
        }
    }

This of cause means no matter what you specify, it will not matter at all for the parameters. One can argue if this change is a good thing or not, I guess it is not ;)

Upvotes: 5

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

The mapping should be like this (see the documentation 5.2.2. Basic value types):

Id(x => x.LinkId)      
  .CustomType("AnsiString")
  ...
  ;

NHibernate type for char (non unicode string) is type="AnsiString" for xml mapping. the above is the way how to do that in fluent.

See similar story here: NHibernate Performance (Ansi String vs. Unicode String)

Side Note: I've never managed to specify length ... always it is generated by NHibernate varchar(8000), using MS SQL 2008 dialect...

Upvotes: 5

granadaCoder
granadaCoder

Reputation: 27852

This is a guess, as I've used CustomSqlType, but not personally with char.

  Id(x => x.LinkId).Length(10).CustomSqlType("char(10)");

or you may have to drop the Length.

  Id(x => x.LinkId).CustomSqlType("char(10)");

Here is a simiar:

Possible to use CustomSqlType with CompositeId?

Upvotes: 0

Related Questions