NHibernate Many To Many

Question

I have a many to many relationship between

Portfolio and PortfolioTags

A portfolio Item can have many PortfolioTags

I am looking at the best way of saving tags to a portfolio item. My Nhibnerate maps are like so:

 public class PortfolioMap : ClassMap<Portfolio> {

        public PortfolioMap() {
            Table("Portfolio");
            LazyLoad();
            Id(x => x.Id).GeneratedBy.Identity().Column("Id");
            Map(x => x.AliasTitle).Column("AliasTitle").Not.Nullable();
            Map(x => x.MetaDescription).Column("MetaDescription").Not.Nullable();
            Map(x => x.Title).Column("Title").Not.Nullable();
            Map(x => x.Client).Column("Client").Not.Nullable();
            Map(x => x.Summary).Column("Summary").Not.Nullable();
            Map(x => x.Url).Column("Url");
            Map(x => x.MainImage).Column("MainImage");
            Map(x => x.TitleAlt).Column("TitleAlt");
            Map(x => x.Description).Column("Description").Not.Nullable();
            HasMany(x => x.PortfolioImage).KeyColumn("PortfolioId").Inverse();
            HasMany(x => x.PortfolioTag).KeyColumn("PortfolioId").Cascade.All().Table("PortfolioTag").Inverse();
        }
    }

public class PortfoliotagMap : ClassMap<Portfoliotag> {

    public PortfoliotagMap() {
        Table("PortfolioTag");
        LazyLoad();
        Id(x => x.Id).GeneratedBy.Identity().Column("Id");
        References(x => x.Portfolio).Column("PortfolioId");
        References(x => x.Tag).Column("TagId");
    }
}



 public class TagMap : ClassMap<Tag>
{

      public TagMap() {
        Table("Tag");
        LazyLoad();
        Id(x => x.TagId).GeneratedBy.Identity().Column("TagId");
        Map(x => x.TagVal).Column("Tag").Not.Nullable();
        HasManyToMany(x => x.Portfolio).Table("PortfolioTag").ParentKeyColumn("TagId").ChildKeyColumn("PortfolioId").Inverse();
    }
    }

In my portfolio controller I am first trying to save my tags that do not exist. I tried using SaveOrUpdate on a tag repository. However as the ids are different multiple save of tags occurs at this point.

I thought about the following steps but it seems long winded:

1) getting all tags: var tags = _tagRepository.GetAll();

2) Iterating over the tags from the item to save and seeing if they exist in the database. If so I would need to get the tag and associate with the portfolio item. If not i would need to save the tag one by one and then associate with the portfolio item.

        var tags = _tagRepository.GetAll();

        foreach (var tagInPortfolio in StringUtilities.SplitToList(model.Tags, new[] { ',' }))
        {
            // tag does not exist so save it
            if (tags.Any(i => i.TagVal == tagInPortfolio))
            {
                _tagRepository.SaveOrUpdate(new Tag {TagVal = tagInPortfolio});
            }
        }

3) I then need to delete any relationships i.e. tags to portfolio items that dont exist.

4) Finally need to add the tag to to the portfolioTag. I would need to get all the tags again and then associate:

  portfolio.PortfolioTag.Add(new Portfoliotag {Portfolio = portfolio, Tag = tag});

 _portfolioRepository.UpdateCommit(portfolio);

This seems to long winded. Can anyone explain the most simplest way of doing this please.

I have looked at saveandcommit on tags but i get multiple inserts because of ids being different. Do I need to delete all existing tag relationships also as this seems to much logic for something simple.

Create now works with a commit -

public void CreateCommit(T entity)
    {
        using (ITransaction transaction = Session.BeginTransaction())
        {
            Session.Save(entity); 
            transaction.Commit();
        }

    }

However using the below and the above maps still meant duplicates where occurring in the tag table. So if one portfolio record added a tag like abc and another portfolio record added a tag abc i need the join table to reference the same record in the tag and not create another instance of abc. Do i need to do a lookup on the tag table to avoid this

public void UpdateCommit(T entity)
        {
            using (ITransaction transaction = Session.BeginTransaction())
            {
                Session.Update(entity);
                transaction.Commit();
            }
        } 

Answer 1

If I understood correctly, I think you misunderstood the many-to-many mapping. If you really have a relationship like this between the Portifolio and the Tag classes, you should not map the PortfolioTag table.

In a simple many-to-many relationship the table used to connect the other two main tables should have only the foreign keys from the two tables (that would also be a composite key of this intermediate table). In this case, the PortfolioTag table would have only two columns: PortfolioId and TagId, that would be not only foreign keys for the Portfolio and Tag tables, but also the primary key of this intermediate table.

In this case, your Portfolio class should have a list of Tags instead of a list of PortfolioTag, and the Tag class a list of Portfolios. And you should map the Portfolio and the Tag like this (with no need of mapping the intermediate table):

public class PortfolioMap : ClassMap<Portfolio> {
    public PortfolioMap() {
        Table("Portfolio");
        LazyLoad();
        Id(x => x.Id).GeneratedBy.Identity().Column("Id");
        //
        // Code intentionally omitted for clarity
        //
        HasManyToMany(x => x.Tags)
         .Table("PortfolioTag")
         .ParentKeyColumn("PortfolioId")
         .ChildKeyColumn("TagId")
         .LazyLoad()
         .Cascade.SaveUpdate();
    }
}

public class TagMap : ClassMap<Tag> {
    public TagMap() {
        Table("Tag");
        LazyLoad();
        Id(x => x.Id).GeneratedBy.Identity().Column("Id");
        Map(x => x.TagVal).Column("Tag").Not.Nullable();
        HasManyToMany(x => x.Portfolios)
         .Table("PortfolioTag")
         .ParentKeyColumn("TagId")
         .ChildKeyColumn("PortfolioId")
         .Inverse();
    }
}

You will also have to save the Portfolio inside the context of a transaction for the intermediate table to be populated, like bellow:

using (var trans = Session.BeginTransaction()) {
    try {
        Session.SaveOrUpdate(obj);
        trans.Commit();
    } catch {
        trans.Rollback();
        throw;
    }
}

//or like this (with TransactionScope)

using (var trans = new TransactionScope()) {
    Session.SaveOrUpdate(obj);
    trans.Complete();
}

With this approach, you would also be excused of the need to iterate through the Tags list to save each one. You would be able to just save the Portfolio and everything should be fine.

P.S.: I tested this code with FluentNHibernate v1.4.0.0 and NHibernate v3.3.1.4000.