9

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.

Community
  • 1
  • 1
StuffOfInterest
  • 518
  • 3
  • 11
  • 4
    This doesn't answer your question, but side note: Generally NVARCHARs are better for names since they support non-ASCII characters. – Jon Adams Jan 09 '15 at 03:39
  • 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 – Panagiotis Kanavos Sep 14 '15 at 12:31
  • 1
    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. – Dennes Torres May 26 '17 at 22:34

1 Answers1

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

Yogee
  • 1,412
  • 14
  • 22