nhibernate connecting to legacy database

Question

I am connecting to a legacy sqlserver database. One of the tables has column name "Primary". The scripts are failing due to that.

Script generated by nhibernate: SELECT locations0_.CustomerID as CustomerID1_, locations0_.LocationID as LocationID1_, locations0_.LocationID as LocationID2_0_, locations0_.Primary as Primary2_0_,locations0_.CustomerID as CustomerID2_0_ FROM dbo.tblLocation locations0_ WHERE locations0_.CustomerID=?

Class:

public class Location 
{
    public virtual int LocationID { get; set; }
    public virtual Customer Customer { get; set; }
    public virtual int? CustomerID { get; set; }
    public virtual string LocationName { get; set; }
    public virtual string Address1 { get; set; }
    public virtual string Address2 { get; set; }
    public virtual string Address3 { get; set; }
    public virtual string City { get; set; }
    public virtual string StateOrProvince { get; set; }
    public virtual string PostalCode { get; set; }
       public virtual datetime? LTimeStamp{ get;set; }
    public virtual bool Primary { get; set; }
}

Map: public class TblLocationMap : ClassMap {

    public TblLocationMap() 
    {
        Table("tblLocation");
        //LazyLoad();
        Id(x => x.LocationID).GeneratedBy.Identity().Column("LocationID");
                    References(x => x.Customer).Column("CustomerID");
        Map(x => x.LocationName).Column("LocationName").Length(50);
        Map(x => x.Address1).Column("Address1").Length(200);
        Map(x => x.Address2).Column("Address2").Length(200);
        Map(x => x.Address3).Column("Address3").Length(200);
        Map(x => x.City).Column("City").Length(100);
        Map(x => x.StateOrProvince).Column("StateOrProvince").Length(100);
        Map(x => x.PostalCode).Column("PostalCode").Length(20);
        //Map(x => x.Primary).Column("Primary").Not.Nullable();
        //Map(x => x.LTimestamp).Column("LTimestamp");
        HasMany(x => x.Contacts).KeyColumn("LocationID");
    }

sql:

CREATE TABLE [dbo].[tblLocation] ( [LocationID] [int] IDENTITY(1,1) NOT NULL, [CustomerID] [int] NULL, [LocationName] nvarchar NULL, [Address1] nvarchar NULL, [Address2] nvarchar NULL, [Address3] nvarchar NULL, [City] nvarchar NULL, [StateOrProvince] nvarchar NULL, [PostalCode] nvarchar NULL, [Primary] [bit] NOT NULL, [RecTimestamp] [timestamp] NULL, ( [LocationID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

GenericADOException: could not initialize a collection: [Domain.Locations#466][SQL: SELECT locations0_.CustomerID as CustomerID1_, locations0_.LocationID as LocationID1_, locations0_.LocationID as LocationID2_0_, locations0_.LocationName as Location2_2_0_, locations0_.Address1 as Address3_2_0_, locations0_.Address2 as Address4_2_0_, locations0_.Address3 as Address5_2_0_, locations0_.City as City2_0_, locations0_.StateOrProvince as StateOrP7_2_0_, locations0_.PostalCode as PostalCode2_0_, locations0_.Primary as Primary2_0_, locations0_.CustomerID as CustomerID2_0_ FROM dbo.tblLocation locations0_ WHERE locations0_.CustomerID=?]

Inner exception: {"Incorrect syntax near the keyword 'Primary'."}

Add the mapping, class and actual database table to this post and we can answer your question. — Cole W
Updated. I removed some columns from table. it does not work when nhibernate creates the sql script with Primary column. But works without it. — sunny
Fogot the mention. LTimestamp column is coming back as byte[] and c# is unable to convert to datetime?. Can you please help with this too? — sunny
did you disable autoquoting when configuring the sessionfactory? — Firo

Rippo Rippo · Accepted Answer · 2012-11-07T11:20:30

I suspect Primary is a reserved word and is not getting escaped properly, therefore try implicitly escaping the column name with back ticks....

e.g.

Map(x => x.Primary).Column("`Primary`").Not.Nullable();

NHibernate will automatically swap your backtick with square braces if you are using MsSql server [Primary]

Strange that the schema is generating square braces but the select SQL isn't.

nhibernate connecting to legacy database

1 Answers