Setting string to be sql type of "varchar" instead of "nvarchar"

Question

I have the following mapping:

public class LogEntryMap
{
    public LogEntryMap()
    {
        Map.Id(x => x.Id).GeneratedBy.Identity();
        Map(x => x.Context).CustomSqlType("varchar").Length(512);
    }
}

However, using SchemaExport to generate the database in SQL Server 2008, the script generated ignores the length so in effect it ends up being a varchar with length of 1:

create table OV_SAC.dbo.[LogEntry] (
    Id BIGINT IDENTITY NOT NULL,
   Context varchar null,
   primary key (Id)
)

.CustomSqlType("varchar 512") throws an exception. And without defining the CustomSqlType, strings are mapped to nvarchar (which does respect the Length property).

Any suggestions?

score 23 · Accepted Answer · edited Jan 08 '16 at 17:18

23

Use .CustomType("AnsiString") instead of default "String" and NHibernate will use varchar instead of nvarchar.

edited Jan 08 '16 at 17:18

BartoszKP

34,786
15
102
130

answered Apr 21 '10 at 07:23

Szymon Pobiega

3,358
17
18

score 17 · Answer 2 · answered Sep 27 '11 at 07:17

If you wanted all of your strings to be mapped to varchar instead of nvarchar you could consider using a convention:

/// <summary>
/// Ensures that all of our strings are stored as varchar instead of nvarchar.
/// </summary>
public class OurStringPropertyConvention : IPropertyConvention
{
    public void Apply(IPropertyInstance instance)
    {
        if (instance.Property.PropertyType == typeof (string))
            instance.CustomType("AnsiString");
    }
}

You mappings could then go back to a simple mapping:

Map(x => x.Context);

Just make sure you remember to tell Fluent NH to use the convention:

        var configuration = new Configuration();
        configuration.Configure();
        Fluently
            .Configure(configuration)
            .Mappings(m => m.FluentMappings
                .AddFromAssemblyOf<Widget>()
                .Conventions.Add<OurStringPropertyConvention>()
                )
            .BuildSessionFactory();

Great!! Now, what if I want ALL my properties as varchar, except one? Can I override the CustomType for that entity in the mapping without being overwritten by the convention? — Gerardo Grignoli, Oct 27 '17 at 13:16
@GerardoGrignoli: yes you can. Conventions only set the defaults. If you use ansi-string convention, and then make a specific column mapping to have i.e. `.CustomType`, it will work just like you'd expect. — quetzalcoatl, Nov 06 '17 at 10:35

score 8 · Answer 3 · answered Feb 26 '10 at 20:51

8

Doh.

Map(x => x.Context).CustomSqlType("varchar (512)");

create table OV_SAC.dbo.[LogEntry] (
    Id BIGINT IDENTITY NOT NULL,
   Context varchar (512) null,
   primary key (Id)
)

answered Feb 26 '10 at 20:51

Ted

7,122
9
50
76

score 0 · Answer 4 · answered Mar 02 '15 at 23:06

0

We found using the "CustomType("AnsiString")" option does prevent it from using the nvarchar, however, it sets the field length of 8000 for a column that is specified as varchar(30). The 8000 varchar is much faster than 4000 nvarchar, but it is still causing huge problems with sql server overhead.

answered Mar 02 '15 at 23:06

Joel WZ

100
8

2

So have you tried: Map(x => x.Context).CustomSqlType("varchar (30)"); – Ted Mar 03 '15 at 18:18

Setting string to be sql type of "varchar" instead of "nvarchar"

4 Answers4

Linked