NHibernate uses wrong column type for LINQ contains query (varchar to nvarchar)

Question

I'm having an issue with NHibernate 3.3.3 against SQL Server using NVARCHAR as a parameter against a VARCHAR column for LIKE queries created from a string.Contains() function in a LINQ expression. I have Fluent mappings which tell NHibernate that the column is VARCHAR, and a basic "==" comparison does use a VARCHAR parameter.

A possible answer was listed at Lambda string as VARCHAR, but after much typing (can't copy/paste into my development environment) I wasn't able to get anywhere with this.

Here is a table to store the VARCHAR column:

CREATE TABLE Names (
   Id   INT         NOT NULL IDENTITY (1, 1) PRIMARY KEY,
   Name VARCHAR(20) NOT NULL
);

Here are a few code snipets to illustrate what is happening:

public class Names
{
   public virtual int Id { get; set; }
   public virtual string Name { get; set; }
}

public class NamesMap : ClassMap<Names>
{
   Table("Names");
   Id(x => x.Id).Column("Id").GeneratedBy.Identity();
   Map(x => x.Name).Column("Name").CustomType("AnsiString");
}

// _namesRepository is an IQueryable to the name collection
var matchName = "fred";
var name1 = _namesRepository.Where(o => o.Name == matchName).FirstOrDefault();
var name2 = _namesRepository.Where(o => o.Name.Contains(matchName)).FirstOrDefault();

The call to get name1 will generate a parameter as:

DECLARE @p0 varchar(8000) = 'fred';

The call to get name2 will generate a parameter as:

DECLARE @p0 nvarchar(8000) = N'fred';

Has anyone come up with a working solution to make LINQ string function calls map the parameter to the correct type?

EDIT: Have confirmed that this same behavior is still present in the NHibernate 4.0.0.GA release.

Afghanistan

This doesn't answer your question, but side note: Generally NVARCHARs are better for names since they support non-ASCII characters. – Plumlee

Using NVARCHAR (Unicode) is NOT a bug. It's using varchar fields and parameters that causes bugs due to codepage conversions. There are several dozens of questions in SO where people used varchar and found their data mangled – Bynum

Just to understand better, why is this a problem ? Your call to get name1 is working fine and that's important. Your 2nd call is using Contains. This will never be translated in a way the database server could use an index even if the data type was correct. – Wrung

Answer 1

I don't have the project ready so I couldn't verify but please check if you can specify

Map(x => x.Name).Column("Name").SqlType("varchar(20)"); and if it works

Smokeproof