C# Fluent NHibernate queryover how to query records from Link table in many to many relation

Question

I am new to Fluent NHibernate.

I have the following mappings for many to many relation.

How to write a query using queryover so that bring back records if the product doesn't exist in product table but available in ProductLink table? For some reason the data is not proper as it is legacy system. So doesnt want to ignore records which is not Product table.

Can anyone please help me how to achieve this?

Product productAlias = null;
Asset assetAlias = null;

var query = Session.QueryOver(() => assetAlias);

//Condn1: must be linked to a Product number
query.Inner.JoinAlias(() => assetAlias.Products, () => productAlias);           

var result = query
                .Skip(pageIndex * pageSize)
                .Take(pageSize)
                .Future<Asset>();

Asset

public AssetMap()
{
    Table("ASSET_INFO");

    Id(x => x.Id).GeneratedBy.Assigned().Column("MAT_ID");

    Map(x => x.Title);

    HasManyToMany(x => x.Products)
        .Table("PROD_LINK")
        .AsBag()
        .ParentKeyColumn("MAT_ID")
        .ChildKeyColumn("PROD_NO")
        .Cascade.All()
        .LazyLoad();
}

Product

public ProductMap()
{
    Table("PROD_INFO");

    Id(x => x.Id).GeneratedBy.Assigned().Column("PROD_NO");

    Map(x => x.Name).Column("PROD_NM");

    HasManyToMany(x => x.Assets)
        .Table("PROD_LINK")
        .ParentKeyColumn("PROD_NO")
        .ChildKeyColumn("MAT_ID")
        .Inverse()
        .LazyLoad();
}

ProductLink

public class ProductLinkMatMap : ClassMap<ProductLinkMat>
{
    public ProductLinkMatMap()
    {
        Table("PROD_LINK");

        CompositeId().KeyReference(x => x.Asset, "MAT_ID")
                     .KeyReference(x => x.Product, "PROD_NO");
    }
}

score 0 · Answer 1 · answered Aug 22 '16 at 12:57

As you pointed out yourself, a product should exist and furthermore PROD_NO on PROD_LINK should be a fk in your DB, your problem would not exists..

In your case, you can simply query the linktable and join the asset:

return Session.QueryOver<ProductLinkMat>(() => productLinkAlias)
                .JoinQueryOver(pl => pl.Asset, () => assetAlias, JoinType.InnerJoin)
                .Skip(pageIndex*pageSize)
                .Take(pageSize)
                .Future<Asset>();

C# Fluent NHibernate queryover how to query records from Link table in many to many relation

1 Answers1