Fluent NHibernate - How to map a non nullable foreign key that exists in two joined tables

Question

I'm mapping a set of membership classes for my application using Fluent NHibernate. I'm mapping the classes to the asp.net membership database structure. The database schema relevant to the problem looks like this:

ASPNET_USERS
UserId        PK
ApplicationId FK NOT NULL
other user columns ...

ASPNET_MEMBERSHIP
UserId        PK,FK
ApplicationID FK NOT NULL
other membership columns...

There is a one to one relationship between these two tables. I'm attempting to join the two tables together and map data from both tables to a single 'User' entity which looks like this:

public class User
{
    public virtual Guid Id { get; set; }
    public virtual Guid ApplicationId { get; set; }

    // other properties to be mapped from aspnetuser/membership tables ...

My mapping file is as follows:

public class UserMap : ClassMap<User>
{
    public UserMap()
    {
        Table("aspnet_Users");
        Id(user => user.Id).Column("UserId").GeneratedBy.GuidComb();
        Map(user => user.ApplicationId);
        // other user mappings

        Join("aspnet_Membership", join => {
            join.KeyColumn("UserId");
            join.Map(user => user.ApplicationId);
            // Map other things from membership to 'User' class
        }
    }
}

If I try to run with the code above I get a FluentConfiguration exception

Tried to add property 'ApplicationId' when already added.

If I remove the line "Map(user => user.ApplicationId);" or change it to "Map(user => user.ApplicationId).Not.Update().Not.Insert();" then the application runs but I get the following exception when trying to insert a new user:

Cannot insert the value NULL into column 'ApplicationId', table 'ASPNETUsers_Dev.dbo.aspnet_Users'; column does not allow nulls. INSERT fails. The statement has been terminated.

And if I leave the .Map(user => user.ApplicationId) as it originally was and make either of those changes to the join.Map(user => user.ApplicationId) then I get the same exception above except of course the exception is related to an insert into the aspnet_Membership table

So... how do I do this kind of mapping assuming I can't change my database schema?

Answer 1

I think I got something that works.

  public class Application
    {
        public virtual Guid ApplicationId { get; set; }


        /* Scalar Properties of an Application  */
        public virtual string ApplicationName { get; set; }
        public virtual string Description { get; set; }

        public virtual string LoweredApplicationName 
        {
            get
            {
                return this.ApplicationName.ToLower();
            }
            set
            {
                if (String.IsNullOrEmpty(this.ApplicationName))
                {
                    this.ApplicationName = value;
                }
            } 
        }

        public virtual IList<Membership> TheManyMemberships { get; protected set; }

    }


    public class User
    {
        public virtual Guid Id { get; set; }
        public virtual Application TheApplication { get; set; }

        public virtual Membership TheMembership { get; set; }

        /* Scalar Properties of a User  */
        public virtual string UserName { get; set; }
    }


    public class Membership
    {
        private Guid UserId { get; set; }
        private User _theUser { get; set; }

        protected Membership() { }

        public Membership(User theUser)
        {
            _theUser = theUser;
        }

        public virtual Application TheApplication { get; set; }

        /* Scalar Properties of a Membership  */
        public virtual string Password { get; set; }
}





    public class ApplicationMap : ClassMap<Application>
    {
        public ApplicationMap()
        {
            Table("aspnet_Applications");
            Id(app => app.ApplicationId).Column("ApplicationId").GeneratedBy.GuidComb();
            Map(x => x.ApplicationName );
            Map(x => x.LoweredApplicationName);
            Map(x => x.Description );

            HasMany<Membership>(x => x.TheManyMemberships)
                .Inverse()
                .AsBag();
        }
    }


    public class UserMap : ClassMap<User>
    {
        public UserMap()
        {
            Table("aspnet_Users");
            Id(user => user.Id).Column("UserId").GeneratedBy.GuidComb();
            References(x => x.TheApplication, "ApplicationId")
                  .Not.Nullable();

            HasOne(x => x.TheMembership)
            .Cascade.All();//
            //.Constrained();

            Map(x => x.UserName).Not.Nullable();

        }
    }


   public class MembershipMap : ClassMap<Membership>
    {
        public MembershipMap()
        {
            Table("aspnet_Membership");

            Id(Reveal.Member<Membership>("UserId"))
                .GeneratedBy.Foreign("_theUser");
            HasOne(
              Reveal.Member<Membership, User>("_theUser"))
                    .Constrained()
                    .ForeignKey();

            References<Application>(x => x.TheApplication, "ApplicationId")
            .Not.Nullable();

            Map(x => x.Password);

        }
    }

Forgive some of the naming conventions, when prototyping, I use un-ambiguous names over proper-convention to avoid confusion.

The DDL I have (from the above code) and the DDL from the output of the asp.net (4.0) (using aspnet_regsql.exe to build the DDL) seem consistent (between the two versions).

I need to thank this post: http://brunoreis.com/tech/fluent-nhibernate-hasone-how-implement-one-to-one-relationship/

If you make any tweaks, then please post them.

But I was able to save an Application, User and Membership.

However, I think I may be slightly off with the User:Membership relationship. The microsoft scenario seems to be "Have a user, but allow that user to have a different password for each application", which makes sense. But sometimes when using the MembershipProvider code (the MS code, nothing to do with NHibernate, I "feel" like sometimes it assumes a single application.

I feel like the MS DDL should have a unique constraint on dbo.Membership (UserId, ApplicationId), but I don't see it in their DDL.

Regardless, this should provide some food for thought.