Thursday, December 16, 2010

Brownfield system problem on Fluent NHibernate. And solution

If you have these tables:

create table Person
(
Id int identity(1,1) not null primary key,
PersonName varchar(255) not null,
BirthDate Date not null
);

create table Contact
(
OwnerId int not null references Person(Id),
Id int identity(1,1) not null primary key,
Number varchar(50) not null,
Type varchar(20) not null
);

Click to expand mapping
public class Person
    {
        public virtual int Id { get; set; }
        
        public virtual string PersonName { get; set; }
        public virtual DateTime Birthdate { get; set; }        
                    
        public virtual IList<Contact> Contacts { get; set; }
    }
    

    public class Contact
    {
        public virtual Person Owner { get; set; }
                        
        public virtual int Id { get; set; }        
        public virtual string Number { get; set; }        
        public virtual string Type { get; set; }


        
        
    }
    
    public class PersonMap : ClassMap <Person>
    {
        public PersonMap()
        {        
    
            Id(x => x.Id);
            Map(x => x.PersonName).Not.Nullable();
            Map(x => x.Birthdate).Not.Nullable();
            HasMany(x => x.Contacts).Inverse(); 
        }
    }


  
    public class ContactMap : ClassMap <Contact>
    {
        public ContactMap()
        {
            References(x => x.Owner);
            Id(x => x.Id);
            Map(x => x.Number).Not.Nullable();
            Map(x => x.Type).Not.Nullable();
        }
        
    }   

This code...

ISessionFactory sf = 
        Fluently.Configure()
            .Database(MsSqlConfiguration.MsSql2008.ConnectionString(@"Data Source=.\SQLExpress;Initial Catalog=test3;Trusted_Connection=true"))
            .Mappings(m =>
                m.FluentMappings.AddFromAssemblyOf<MainClass>()
                .Conventions.Add(ForeignKey.EndsWith("Id"))                        
                )
            .BuildConfiguration()
            .BuildSessionFactory();

    var px = from p in sf.OpenSession().Query<Person>()
                select p;
         


    foreach (var p in px)
    {
        Console.WriteLine("{0} {1}", p.PersonName, p.Birthdate);

        foreach (var c in p.Contacts)
        {
            Console.WriteLine("{0}", c.Number);
        }
    }

...will produce an incorrect query on foreach(var c in p.Contacts) (note the extraneous PersonId):

SELECT 
contacts0_.Person_id as Person5_1_, 
contacts0_.Id as Id1_, 
contacts0_.Id as Id2_0_, 
contacts0_.Number as Number2_0_, 
contacts0_.Type as Type2_0_, 
contacts0_.OwnerId as OwnerId2_0_ 
FROM [Contact] contacts0_ 
WHERE contacts0_.PersonId=?

Even if you add this:

  
.Conventions.Add(
    ConventionBuilder.Reference
      .Always(x => x.Column(x.Name+ "Id" )))
  


The only way to rectify this problem is to set up the joining key(e.g. OwnerId) on both ends. That is, we must set it up on .HasMany.Always and .References.Always conventions

But first, we must design a nice API solution around this problem, this is what I came up with:
.Conventions.AddBrownfieldConventions(referenceSuffix: "Id");


And that works! :-) When your run the Linq code above, it will work now. Expand to check the code:

using System;

using System.Collections.Generic;
using System.Text.RegularExpressions;

using NHibernate;
using NHibernate.Dialect;

using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Conventions;
using FluentNHibernate.Conventions.Helpers;
using FluentNHibernate.Mapping;
using System.Reflection;
using System.Collections;
using FluentNHibernate.Mapping.Providers;


using System.Linq;
using FluentNHibernate.BrownfieldSystem;
using FluentNHibernate.Conventions.Instances;

namespace FluentNHibernate.BrownfieldSystem
{
    public class ClassMapExt<T> : ClassMap<T>
    {
        public IList<IManyToOneMappingProvider> ExtReferences { get { return this.references; } }
    }

    public static class BrownfieldSystemHelper
    {


        public static T AddBrownfieldConventions<T>(this SetupConventionFinder<T> fluentMappingsContainer, string referenceSuffix, params IConvention[] otherConventions)
        {
            return fluentMappingsContainer.AddBrownfieldConventions(referenceSuffix, false, otherConventions);
        }

        public static T AddBrownfieldConventions<T>(this SetupConventionFinder<T> fluentMappingsContainer, string referenceSuffix, bool toLowercase, params IConvention[] otherConventions)
        {

            IList<IConvention> brown =
                new IConvention[]
                {
                    Table.Is(x => x.EntityType.Name.ToLowercaseNamingConvention(toLowercase))
                    ,ConventionBuilder.Property.Always(x => x.Column(x.Name.ToLowercaseNamingConvention(toLowercase)))
                    ,ConventionBuilder.Id.Always( x => x.Column(x.Name.ToLowercaseNamingConvention(toLowercase)) )        
                    ,ConventionBuilder.HasMany.Always(x => x.Key.Column( x.NormalizeReference().ToLowercaseNamingConvention(toLowercase) + referenceSuffix )  )
                
                    // Instead of this...
                    // ,ForeignKey.EndsWith(referenceSuffix)                                
                    // ... we do this, so we have direct control on Reference name's casing:
                    ,ConventionBuilder.Reference.Always(x => x.Column( x.Name.ToLowercaseNamingConvention(toLowercase) + referenceSuffix ) )
                
                };

            foreach (IConvention c in brown)
                fluentMappingsContainer.Add(c);

            return fluentMappingsContainer.Add(otherConventions);
        }


        public static string ToLowercaseNamingConvention(this string s)
        {
            return s.ToLowercaseNamingConvention(false);
        }

        public static string ToLowercaseNamingConvention(this string s, bool toLowercase)
        {
            if (toLowercase)
            {
                var r = new Regex(@"
                (?<=[A-Z])(?=[A-Z][a-z]) |
                 (?<=[^A-Z])(?=[A-Z]) |
                 (?<=[A-Za-z])(?=[^A-Za-z])", RegexOptions.IgnorePatternWhitespace);

                return r.Replace(s, "_").ToLower();
            }
            else
                return s;
        }


        public static string NormalizeReference(this IOneToManyCollectionInstance x)
        {
            foreach (Type t in x.ChildType.Assembly.GetTypes())
            {
                if (t.BaseType.IsGenericType)
                    if (t.BaseType.GetGenericTypeDefinition() == typeof(ClassMapExt<>))
                        if (t.BaseType.GetGenericArguments()[0] == x.ChildType) // e.g.Contact
                        {

                            var pz = Activator.CreateInstance(t); // ContactMapExt                                        
                            var extRef = (IList<IManyToOneMappingProvider>)t.InvokeMember("ExtReferences", BindingFlags.GetProperty, null, pz, null);

                            if (extRef.Count > 1) 
                                throw new Exception("Ambiguous collection found. Do explicit column mapping on both entity and value table");
                            foreach (IManyToOneMappingProvider imt1 in extRef)
                                return imt1.GetManyToOneMapping().Member.Name;
                        }
            }

            return "";

        }//Normalize


    }// class BrownfieldSystemHelper
}// namespace FluentNHibernate.BrownfieldSystem

What you need to set up in your program is to change all brownfield tables' ClassMap to ClassMapExt. ClassMapExt exposes the this.references(which by James Gregory's design is protected on ClassMap, the references contain the data structure for ManyToOne mapping), so we can use it in ConventionBuilder.HasMany.Always

To verify that our code is indeed working, this Fluently configuration(sans Brownfield) will not output the correct DDL...
string sx = string.Join(";" + Environment.NewLine,
  Fluently.Configure()
  .Database(MsSqlConfiguration.MsSql2008)
  .Mappings(m => 
   m.FluentMappings
   .AddFromAssemblyOf<MainClass>()
   .Conventions.Add(ForeignKey.EndsWith("Id"))
   )
  .BuildConfiguration()
  .GenerateSchemaCreationScript(new MsSql2008Dialect())
  );


Console.WriteLine("Copy this: \n\n{0}", sx);


..., note the extraneous PersonId :

create table [Contact] 
(
Id INT IDENTITY NOT NULL, 
Number NVARCHAR(255) not null, 
Type NVARCHAR(255) not null, 
OwnerId INT null, 
PersonId INT null, 
primary key (Id)
);

create table [Person] 
(
Id INT IDENTITY NOT NULL, 
PersonName NVARCHAR(255) not null, 
Birthdate DATETIME not null, 
primary key (Id)
);

alter table [Contact] add constraint FK4FF8F4B2F8EC9C3E 
foreign key (OwnerId) references [Person];

alter table [Contact] add constraint FK4FF8F4B2141D8C21 
foreign key (PersonId) references [Person]


While this one with adding of Brownfield conventions...
string sx = string.Join(";" + Environment.NewLine,
        Fluently.Configure()
        .Database(MsSqlConfiguration.MsSql2008)
        .Mappings(m => 
            m.FluentMappings
            .AddFromAssemblyOf<MainClass>()
            .Conventions.AddBrownfieldConventions(referenceSuffix: "Id")                        
            )
        .BuildConfiguration()
        .GenerateSchemaCreationScript(new MsSql2008Dialect()));


Console.WriteLine("Copy this: \n\n{0}", sx);

...yields correct DDL (note that there's no more extraneous PersonId):

create table Contact 
(
Id INT IDENTITY NOT NULL, 
Number NVARCHAR(255) not null, 
Type NVARCHAR(255) not null, 
OwnerId INT null, 
primary key (Id)
);

create table Person 
(
Id INT IDENTITY NOT NULL, 
PersonName NVARCHAR(255) not null, 
Birthdate DATETIME not null, 
primary key (Id)
);

alter table Contact add constraint FK4FF8F4B2F8EC9C3E 
foreign key (OwnerId) references Person
 

And the correct query(note that it's using OwnerId now, and no more extraneous Person_id):

SELECT 
contacts0_.OwnerId as OwnerId1_, 
contacts0_.Id as Id1_, 
contacts0_.Id as Id3_0_, 
contacts0_.Number as Number3_0_, 
contacts0_.Type as Type3_0_, 
contacts0_.OwnerId as OwnerId3_0_ 
FROM Contact contacts0_ 
WHERE contacts0_.OwnerId=?

Happy Fluenting! :-)

EDIT: 2010-12-18

Program update on flaw found by Stefan Steinegger on multiple references: http://www.ienablemuch.com/2010/12/intelligent-brownfield-mapping-system.html

No comments:

Post a Comment