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

Did you try inheritance:

public class User
....

public class Member : User
....

?

Any reason why you are joining on on ApplicationId at all? I believe it is in both tables for reference. As UserId is a Guid, it is unique. If you do have the situation where you need to store the same user for two distinct applications, well you can't asp.net membership does not work like that, it would create two distinct users records. The lookup on username/password checks the app id based on the web apps setup (machine key) to enable its uniqueness. The membership table is a red herring.

Answer 2

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.