How to map one-to-one relation as part of composite key in Fluent NHibernate

Question

I have two tables defining a parent-child relationship at the database level. The parent table has a single column primary key, and the child table has a composite primary key where one column refers to the parent table:

application table       tasks table
= task_id (CK) -------> = taskid (PK) 
= user_id (CK)
= transaction_id (CK)

Just for reference, the user_id and transaction_id columns do not refer to other tables.

I'm trying to set up a Fluent NHibernate mapping for both entities in C#:

public class Application
{
    public virtual string UserId { get; set; }
    public virtual string TransactionId { get; set; }
    public virtual string TaskId { get; set; }

    public virtual Task Task { get; set; }
}

public class Task
{
    public string Id { get; set; }
}

One Application has one Task, but one Task has many Applications. It is this relationship I'm having fits with.

internal class ApplicationMap : ClassMap<Application>
{
    public ApplicationMap() : base()
    {
        Schema(...);
        Table(...);

        CompositeId()
            .KeyProperty(app => app.UserId, "user_id")
            .KeyReference(app => app.Task, "task_id")
            .KeyProperty(app => app.TransactionId, "transaction_id");

        // No explicit mapping defined for "task_id"
        // Other columns mapped, but omitted for brevity
    }
}

internal class TaskMap : ClassMap<Task>
{
    public TaskMap()
    {
        Schema(DbSchema.SchemaName);
        Table(DbSchema.TableName);

        Id(task => task.Id, "taskid");

        // Other columns mapped, but omitted for brevity

        // Relations
        HasMany(task => task.Applications);
    }
}

Upon inserting a new Application into the database, I get this exception:

NHibernate.QueryException: could not resolve property: TaskId of: Application.

I tried adding an explicit mapping to ApplicationMap for the TaskId property, but I got the super helpful "Index was out of range. Must be non-negative and less than the size of the collection." exception from NHibernate:

internal class ApplicationMap : ClassMap<Application>
{
    public ApplicationMap() : base()
    {
        Schema(...);
        Table(...);

        CompositeId()
            .KeyProperty(app => app.UserId, "user_id")
            .KeyReference(app => app.Task, "task_id")
            .KeyProperty(app => app.TransactionId, "transaction_id");

        Map(app => app.TaskId, "task_id");

        // Other columns mapped, but omitted for brevity
    }
}

After reading Fluent NHibernate compositeid to mapped class, I'm not sure what else to try. The difference between that question and this one is that the foreign key column on the child table does need to be mapped in the entity (Application.TaskId).

I've been searching the Fluent NHibernate docs for while, and anything referring to composite primary keys has been hard to come by, especially when relationships to other tables are involved.

Why both TaskId and Task are needed

I do need Application.Task once in a while, but not very often. However, the composite key on the application table is used as a composite foreign key reference for all the other tables related to the application table. The TaskId property is going to be access a lot, and I'd like to avoid a JOIN query on the application and tasks table just to get a value already in the application table.

The "Failing" Unit Test

I had written a unit test for this mapping and repository in NHibernate, and it was failing:

var app = new Application(user)
{
    TaskId = "...",
    // More properties being set...
};

db.Web.Applications.Create(app);
db.SaveChanges();

var actual = db.Web.Applications.Find(app.UserId, app.TaskId, app.TransactionId);

// Test was failing here
Assert.IsNotNull(actual.Task, "No task found");

The real problem seems to be that the Task property of the newly inserted record is null, and is not being lazy loaded after retrieving from the same NHibernate session (which after some research is the intended behavior).

I had gone through many iterations of mappings, and actually did have a problem with the mapping initially. I just "kept having problems" because I didn't understand how NHibernate behaves on inserting new records.

Answer 1

I think your mapping for TaskClassMap needs to be the following:

public class TaskClassMap : ClassMap<Task>
{
    public TaskClassMap()
    {
        Table("Task");

        Id(task => task.Id, "taskid");
        HasMany(c => c.Applications)
            .KeyColumn("task_id");
    }
}

If you don't specify a specific column name (.KeyColumn) nhibernate tries to use conventions which would be TaskId in this case.

Also the reason you were getting the infamous error below is because you were trying to map the same column (task_id) twice in the same mapping (ApplicationMap):

Index was out of range. Must be non-negative and less than the size of the collection.

    CompositeId()
        .KeyProperty(app => app.UserId, "user_id")
        .KeyReference(app => app.Task, "task_id")
        .KeyProperty(app => app.TransactionId, "transaction_id");

    Map(app => app.TaskId, "task_id");

The TaskId property is going to be access a lot, and I'd like to avoid a JOIN query on the application and tasks table just to get a value already in the application table.

Also to comment on the above statement made I will say that nhibernate will not query the database if you are only accessing Application.Task.Id. When doing lazy loading nhibernate creates a proxy object for this type of relationship where the only field that is stored in memory is the primary key (Task.Id). So if you were to access this field it's actually not hitting the database. If you access any other field outside of the id it will trigger a query to the database to fetch the remaining values. It's like you say in the comment this value is already stored in the Application table and as such nhibernate won't query the Task table until you try to access a value that is only in that table.

Answer 2

I had gone through your mapping, when you do the mapping with composite keys, it works if you use a key object, something like this,

public class ApplicationId
    {
        public virtual string UserId { get; set; }
        public virtual string TransactionId { get; set; }
        public virtual Task Task { get; set; }

        public override bool Equals(object obj)
        {
            ApplicationId recievedObject = (ApplicationId)obj;

            if ((Task.Id == recievedObject.Task.Id) &&
                (TransactionId == recievedObject.TransactionId) &&
                (UserId == recievedObject.UserId))
            {
                return true;
            }

            return false;
        }

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }
    }

and the mapping is like,

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

    public class ApplicationClassMap : ClassMap<Application>
    {
        public ApplicationClassMap()
        {
            Table("Application");

            CompositeId<ApplicationId>(app => app.Id)
            .KeyProperty(key => key.UserId, "user_id")
            .KeyReference(key => key.Task, "task_id")
            .KeyProperty(key => key.TransactionId, "transaction_id");
        }
    }

and the mapping for the Task should be like,

public class Task
    {
        public virtual string Id { get; set; }

        public virtual IList<Application> Applications { get; set; }
    }

    public class TaskClassMap : ClassMap<Task>
    {
        public TaskClassMap()
        {
            Table("Task");

            Id(task => task.Id, "taskid");
            HasMany<Application>(c => c.Applications);
        }
    }

There are some hints in this question about how to fix the second problem,