CODEWITHSUNDEEP
nhibernatefluent-nhibernatesql-server-ce
Dafydd Giddins
Dafydd Giddins

Reputation: 2316

Fluent Nhibernate - how do i specify table schemas when auto generating tables in SQL CE 4

I am using SQL CE as a database for running local and CI integration tests (normally our site runs on normal SQL server). We are using Fluent Nhibernate for our mapping and having it create our schema from our Mapclasses. There are only two classes with a one to many relationship between them. In our real database we use a non dbo schema. The code would not work with this real database at first until i added schema names to the Table() methods. However doing this broke the unit tests with the error...

System.Data.SqlServerCe.SqlCeException : There was an error parsing the query. [ Token line number = 1,Token line offset = 26,Token in error = User ]

These are the classes and associatad MapClasses (simplified of course)

    public class AffiliateApplicationRecord
{
    public virtual int Id { get; private set; }
    public virtual string CompanyName { get; set; }
    public virtual UserRecord KeyContact { get; private set; }

    public AffiliateApplicationRecord()
    {
        DateReceived = DateTime.Now;
    }

    public virtual void AddKeyContact(UserRecord keyContactUser)
    {
        keyContactUser.Affilates.Add(this);
        KeyContact = keyContactUser;
    }
}

public class AffiliateApplicationRecordMap : ClassMap<AffiliateApplicationRecord>
{
    public AffiliateApplicationRecordMap()
    {
        Schema("myschema");
        Table("Partner");
        Id(x => x.Id).GeneratedBy.Identity();
        Map(x => x.CompanyName, "Name");
                    References(x => x.KeyContact)
            .Cascade.All()
            .LazyLoad(Laziness.False)
            .Column("UserID");       
    }
}

public class UserRecord
{
    public UserRecord()
    {
        Affilates = new List<AffiliateApplicationRecord>();
    }

    public virtual int Id { get; private set; }
    public virtual string Forename { get; set; }
    public virtual IList<AffiliateApplicationRecord> Affilates { get; set; }
}

public class UserRecordMap : ClassMap<UserRecord>
{
    public UserRecordMap()
    {
        Schema("myschema");
        Table("[User]");//Square brackets required as user is a reserved word
        Id(x => x.Id).GeneratedBy.Identity();
        Map(x => x.Forename);

        HasMany(x => x.Affilates);

    }
}

And here is the fluent configuraton i am using ....

      public static ISessionFactory CreateSessionFactory()
    {
        return Fluently.Configure()
            .Database(
                MsSqlCeConfiguration.Standard
                    .Dialect<MsSqlCe40Dialect>()
                    .ConnectionString(ConnectionString)
                    .DefaultSchema("myschema"))
            .Mappings(m => m.FluentMappings.AddFromAssembly(typeof(AffiliateApplicationRecord).Assembly))
            .ExposeConfiguration(config => new SchemaExport(config).Create(false, true))
            .ExposeConfiguration(x => x.SetProperty("connection.release_mode", "on_close")) //This is included to deal with a SQLCE issue http://stackoverflow.com/questions/2361730/assertionfailure-null-identifier-fluentnh-sqlserverce
            .BuildSessionFactory();

    }

The documentation on this aspect of fluent is pretty weak so any help would be appreciated

Upvotes: 2

Views: 5963

Answers (2)

Dafydd Giddins
Dafydd Giddins

Reputation: 2316

As usual, 10 minutes after posting i answer my own questions. The trick was to not declare the schema in the the ClassMaps. Instead i used the DefaultSchema method in the fluent configuration. So my actual 'live' configuration looks like this :

var configuration = Fluently.Configure()
            .Database(MsSqlConfiguration.MsSql2008.ConnectionString(c => c.FromConnectionStringWithKey("connectionStringKey"))
            .DefaultSchema("myschema"))
            .Mappings(m => m.FluentMappings.AddFromAssemblyOf<AffiliateApplicationRecord>());

        return configuration;

And my integration tests look like this:

return Fluently.Configure()
            .Database(
                MsSqlCeConfiguration.Standard
                    .Dialect<MsSqlCe40Dialect>()
                    .ConnectionString(ConnectionString))
            .Mappings(m => m.FluentMappings.AddFromAssembly(typeof(AffiliateApplicationRecord).Assembly))
            .ExposeConfiguration(config => new SchemaExport(config).Create(false, true))
            .ExposeConfiguration(x => x.SetProperty("connection.release_mode", "on_close")) //This is included to deal with a SQLCE issue http://stackoverflow.com/questions/2361730/assertionfailure-null-identifier-fluentnh-sqlserverce
            .BuildSessionFactory();

Hopefully someone else will get somevalue out of this...

Upvotes: 7

Nathan Fisher
Nathan Fisher

Reputation: 7941

I think this has more to do with SQL Server CE than nhibernate. I am pretty sure that Sql Server CE will not accept schemas at all. its not a supported feature.

See the Create Table Documentation on MSDN

Upvotes: 0

Related Questions