[SOLVED] Fluent NHibernate duplicate row on one-to-many reference

Fluent NHibernate duplicate row on one-to-many reference

in my project I have a problem with a one-to-many reference.

Actually I have three dto referenced to create a many-to-many reference:

User one-to-many RoleGroup
Role one-to-many RoleGroup

A user could have more than one role and so a role could by assigned to more users.

THE PROBLEM: When I try to retrieve a User by a criteria (search by Example in Fluent NHibernate) I found duplicate rows (actually I have 1 user in my database for test with 2 roles and when I search for this user I see 2 identical rows).

But if I try to retrieve a User by its key I found a single row (this is correctly).

Below you can read the mappings class of User and RoleGroup and then the method used for the search... Could you help me to find the problem and gave me some advice?

Thank you so much to everyone! Nak

MAPPINGS:

//USER MAPPING
public class UserMap : ClassMap<User>
{
    public UserMap
    {
        this.Schema("[dbo]");
        this.Table("[users]");

        this.CompositeId(x => x.Key)
            .KeyProperty(x => x.ApplicationId)
            .KeyProperty(x => x.Id);

        this.Map(x => x.Email);
        this.Map(x => x.Password);
        this.Map(x => x.Pin);
        this.Map(x => x.FirstName);
        this.Map(x => x.SecondName);
        this.Map(x => x.IsActive);
        this.Map(x => x.Expiring);
        this.Map(x => x.DateOfBirth);
        this.Map(x => x.Phone);

        this.HasMany(x => x.RoleGroups)
            .Not.LazyLoad()
            .Cascade.AllDeleteOrphan()
            .Fetch.Join()
            .Inverse()
            .KeyColumns.Add("applicationId", "userId");
    }
}

//ROLE GROUP MAPPING
public class RoleGroupMap: ClassMap<RoleGroup>
{
    public RoleGroupMap
    {
        this.Schema("[dbo]");
        this.Table("[role_groups]");

        this.CompositeId(x => x.Key)
            .KeyProperty(x => x.ApplicationId)
            .KeyProperty(x => x.Id);

        this.Map(x => x.UserId);
        this.Map(x => x.RoleId);

        this.References(x => x.User)
            .Not.LazyLoad()
            .Not.Nullable()
            .Cascade.SaveUpdate()
            .Columns("applicationId", "userId");

        this.References(x => x.Role)
            .Not.LazyLoad()
            .Not.Nullable()
            .Cascade.SaveUpdate()
            .Columns("applicationId", "roleId");
    }
}



//FLUENT NHIBERNATE REPO
public class Repository<TIdentifier> : IRepository<TIdentifier> where TIdentifie : class, new()
{

    ...

    //method used to search by criteria, in this particular case Item is type of User and it is used as criteria
    public virtual IEnumerable<TIdentifier> LoadByCriteria(TIdentifier Item, int? RecordStartIndex = null, int? TotalRecords = null)
    {
        IList<Tidentifier> itemList;

        using(ISession session = NHibernateConfiguration<TIdentifier>.OpenSession())
        {
            ICriteria criteria = session.CreateCriteria<TIdentifier>();

            if(RecordStartIndex != null)
            {
                criteria.SetFirstResult((int)RecordStartIndex);
            }

            if(TotalRecords != null)
            {
                criteria.SetMaxResults((int)TotalRecords);
            }

            itemList = criteria.Add(Example.Create(Item))
                .List<TIdentifier>();
        }

        if(itemList == null)
            itemList = new List<TIdentifier>();

        return itemList.ToList();
    }
}

Solution

Avoid .Fetch.Join(). It creates joins in SQL that leads to a multiplication of rows. To avoid the N+1 problem, use batch fetching instead.