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

Multi tool use
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.
c#

add a comment |
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.
c#

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 9 '15 at 3:39
Using NVARCHAR (Unicode) is NOT a bug. It's usingvarchar
fields and parameters that causes bugs due to codepage conversions. There are several dozens of questions in SO where people usedvarchar
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
add a comment |
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.
c#

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.
c#

c#

edited May 23 '17 at 10:34
Community♦
11
11
asked Aug 27 '14 at 11:19


StuffOfInterestStuffOfInterest
37628
37628
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 9 '15 at 3:39
Using NVARCHAR (Unicode) is NOT a bug. It's usingvarchar
fields and parameters that causes bugs due to codepage conversions. There are several dozens of questions in SO where people usedvarchar
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
add a comment |
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 9 '15 at 3:39
Using NVARCHAR (Unicode) is NOT a bug. It's usingvarchar
fields and parameters that causes bugs due to codepage conversions. There are several dozens of questions in SO where people usedvarchar
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
4
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 9 '15 at 3:39
This doesn't answer your question, but side note: Generally NVARCHARs are better for names since they support non-ASCII characters.
– Jon Adams
Jan 9 '15 at 3: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
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
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
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
add a comment |
1 Answer
1
active
oldest
votes
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
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f25525810%2fnhibernate-uses-wrong-column-type-for-linq-contains-query-varchar-to-nvarchar%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
add a comment |
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
add a comment |
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
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
answered Dec 31 '18 at 12:14
YogeeYogee
1,0611018
1,0611018
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f25525810%2fnhibernate-uses-wrong-column-type-for-linq-contains-query-varchar-to-nvarchar%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Z,xWiyE
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 9 '15 at 3: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 usedvarchar
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