CODEWITHSUNDEEP
nhibernatefluent-nhibernate
Dafydd Giddins
Dafydd Giddins

Reputation: 2316

Fluent Nhibernate - search for an item based on value of a many to many relationship

Hopefully the title of this question makes sense, if not, here is my elaboration.

With two entities, Brand and Affiliate and a many-to-may relationship between them i would like to be able to use a query to find the Affiliates where the BrandName is a variable value.

Here is the Affiliate class and Affiliate MapClass (simplified of course)

public class Affiliate
{
    public virtual int Id { get; private set; }
    public virtual DateTime DateReceived { get; set; }

    public virtual IList<Brand> Brands { get; set; }

    public Affiliate()
    {
        Brands = new List<Brand>();
    }
}

public class AffiliateApplicationRecordMap : ClassMap<Affiliate>
{
    public AffiliateApplicationRecordMap()
    {
        Id(x => x.Id).GeneratedBy.Identity();
        Map(x => x.DateReceived, "TimeStampCreated");

        HasManyToMany(x => x.Brands)
            .Cascade.All()
            .ParentKeyColumn("AffiliateID")
            .ChildKeyColumn("BrandID")
            .Table("AffiliateBrand");
    }
}

There is a mapping table called AffiliateBrand which provides the many to many mapping.

Here is the Brand class and ClassMap

public class Brand
{
    public virtual int ID { get; private set; }
    public virtual string Name { get; set; }

    public virtual IList<Affiliate> Affiliates{ get; set; }

    public Brand()
    {
        Affiliates = new List<Affiliate>();
    }

    public virtual void AddAffiliateApplication(Affiliate affiliate)
    {
        affiliate.Brands.Add(this);
        Brands.Add(affiliate);
    }
}

public class BrandMap : ClassMap<Brand>
{
    public BrandMap()
    {
        Id(x => x.ID).GeneratedBy.Identity();
        Map(x => x.Name);

        HasManyToMany(x => x.Affiliates)
            .Cascade.All()
            .Inverse()
            .ParentKeyColumn("BrandID")
            .ChildKeyColumn("PartnerID")
            .Table("AffiliateBrand");
    }
}

Now i'm tyring to write this query with NHibernate:

var result = session
                .CreateCriteria(typeof(Partner))
                .AddOrder(Order.Asc("DateReceived"))
                .Add(Restrictions.Eq("Brands.Name", brandName))
                .SetMaxResults(10)
                .List<Partner>();

Now clearly this isn't working and i didn't really think it would. What i'm trying to do is get all Affiliates back where the Brand has a specific name. How do i write this query?

Upvotes: 0

Views: 473

Answers (1)

Vadim
Vadim

Reputation: 17957

You need to add a join to your criteria using CreateAlias

var result = session
                .CreateCriteria(typeof(Partner))
                .AddOrder(Order.Asc("DateReceived"))
                .CreateAlias("Brands", "brand")
                .Add(Restrictions.Eq("brand.Name", brandName))
                .SetMaxResults(10)
                .List<Partner>();

Upvotes: 4

Related Questions