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

Multi tool use
Multi tool use












8















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.










share|improve this question




















  • 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 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
















8















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.










share|improve this question




















  • 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 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














8












8








8


1






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.










share|improve this 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.







c# sql-server linq nhibernate linq-to-nhibernate






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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 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














  • 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 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








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












1 Answer
1






active

oldest

votes


















0














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






share|improve this answer























    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
    });


    }
    });














    draft saved

    draft discarded


















    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









    0














    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






    share|improve this answer




























      0














      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






      share|improve this answer


























        0












        0








        0







        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






        share|improve this answer













        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







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 31 '18 at 12:14









        YogeeYogee

        1,0611018




        1,0611018
































            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            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





















































            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
            WPjALXjjGZaPY5TjhxIISh GiDK1ttp,ifzP7eHGrhs

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas