NHibernate search on some properties on many-to-many relationship

Question

I have two classes Product and ProductDetail (see below)

I'd like make a search, in the same query, on Code in Product object and on Name in ProductDetail. The result should be a Product list.

I tried this :

var search = "stringToSearch";
var list = _session.QueryOver<Product>()
    .Fetch(x => x.ProductDetails).Eager.Future<Product>()
    .Where(
        x => x.Code.StartsWith(search) || 
        x.ProductDetails.First().Name.StartsWith(search))
    .ToList();

The query give me the right result when search match with Code property and with the Name of the first record of ProductDetail, but I'd like do a search on all records of ProductDetails.

How can I do this ?

Thanks,

public class Product
{
    public virtual int Id { get; set; }
    public virtual string Code { get; set; }
    public virtual IList<ProductDetail> ProductDetails { get; set; }
}

public class ProductDetail
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string Description { get; set; }
    public virtual IList<Product> ProductDetails { get; set; }
}

The mapping :
public class ProductMap : ClassMap<Product>
{
    public ProductMap()
    {
        Table("Product");
        Id(x => x.Id).GeneratedBy.Native();
        Map(x => x.Code).Length(20).Unique().Not.Nullable();
        HasManyToMany(x => x.ProductDetails)
           .Cascade.All()
           .Table("ProductsProductsDetails");
    }
}
public class ProductDetailMap : ClassMap<ProductDetail>
{
    public ProductDetailMap()
    {
        Table("ProductDetail");
        Id(x => x.Id).GeneratedBy.Native();
        Map(x => x.Name).Not.Nullable().Length(50);
        Map(x => x.Description).Length(200);
        HasManyToMany(x => x.Products)
            .Cascade.All()
            .Inverse()
            .Table("ProductsProductsDetails");
    }
}

Answer 1

To query both entities, you can simply join them with aliases and then use those aliases to query the properties

// alias definition
Product productAlias = null;
ProductDetail detailAlias = null;

var list = session.QueryOver<Product>(() => productAlias)
    .JoinAlias(() => productAlias.ProductDetails, () => detailAlias)
    .Where(() => productAlias.Code.StartsWith(search))
    .And(() => detailAlias.Name.StartsWith(search))
    .List();

Unfortunately nHibernate doesn't allow string.StartsWith in this syntax so you'll have to refine the query to use .WhereRestrictionOn and IsLike like this:

var list = session.QueryOver<Product>(() => productAlias)
    .JoinAlias(() => productAlias.ProductDetails, () => detailAlias)
    .WhereRestrictionOn(() => productAlias.Code).IsLike(search, MatchMode.Start)
    .AndRestrictionOn(() => detailAlias.Name).IsLike(search, MatchMode.Start)
    .List();

:Edit: Just found that you want an OR query, to do this we have to combine to restrictions within the Where statement like:

var listWithOr = session.QueryOver<Product>(() => productAlias)
   .JoinAlias(() => productAlias.ProductDetails, () => detailAlias)
   .Where(Restrictions.On(() => productAlias.Code).IsLike(search, MatchMode.Start)
        || Restrictions.On(() => detailAlias.Name).IsLike(search, MatchMode.Start))
   .List();

Hope this helps

:Edit2: The above query will not give you a distinct result, some products can occur in the list multiple times, you'll have to make it a distinct result if needed...

The same query (with already distinct result) can be achieved with a simple NHibernate.Linq statement:

var list2 = session.Query<Product>()
            .Where(prod => prod.Code.StartsWith(search) ||
                prod.ProductDetails.Any(detail => detail.Name.StartsWith(search))
            );