Querying on Collection with Nhibernate Criteria Api?

Question

I have an "Estate" entity, and this entity has a collection "EstateFeatures"(type:EstateFeature) and EstateFeature has a property "MyFeatureValue".

Note: These are the limited properties for the question. All Entities has an Id and all necesarry etc

Estate

IList<EstateFeature> EstateFeatures;

EstateFeature

FeatureValue MyFeatureValue;

FeatureValue

public virtual long Id;

I am trying to get Real Estates which have the given FeatureValue.Id

DetachedCriteria query = DetachedCriteria.For<Estate>();
Conjunction and = new Conjuction();
foreach (var id in idCollection)
   and.Add(Expression.Eq("MyFeatureValue.Id",id);

query
     .CreateCriteria("EstateFeatures")
     .Add(and);
IList<Estate> estates = query.GetExecutableCriteria(session).List<Estate>();

Nothing returned from this query, am i doing something wrong ?

Thanks

Answer 1

If I understand correctly I think something like this might work

CreateCriteria(typeof(Estate))
     .CreateAlias("EstateFeatures", "estatefeature")
     .Add(Restrictions.In("estatefeature.MyFeatureValue.Id", ids))
     .List<Estate>();

Answer 2

What query did NHibernate generate for you ? You can check this by using the show_sql config property.

As I see your query, you're trying to get all Estates that have a given set of features. I think, this will generate a query which looks like

SELECT ....
FROM Estates
INNER JOIN Features
WHERE Feature.Id = 1 AND Feature.Id = 2 ...

If you want to retrieve all estates that contain all specified features, I think you'll have to use a Disjunction, so that NHibernate retrieves all Estates that have at least one of those features. Then, in your client-code, you'll have inspect every Estate in your 'client code', so that you eventually just end up with Estates that have all features.
I don't know if there is an efficient way of letting NHibernate handle this ...

Answer 3

You will need to make sure that you join MyFeatureValue one time for each feature that you want your Estate to have.

One way is to call .CreateAlias for each iteration, give it a unique alias then add expression "aliasX.Id"

foreach (var id in idCollection)
{
   query = query.CreateAlias("MyFeatureValue", "feature" + id)
                .Add(Expression.Eq("feature" + id + ".Id",id);


}

Doesnt really recall how the syntax goes, wrote this out of my head, not sure if you need to redeclare query either :)

However, I think this will get you started.

EDIT: Since a bug in the Criteria API restrain you from associating a collection multiple times using CreateAlias or CreateCriteria, you need to resort to HQL.

http://derek-says.blogspot.com/2008/06/duplicate-association-path-bug-in.html

(Hibernate suffers from the same issue aswell)

select e   
FROM Estate AS e
INNER JOIN e.MyFeatureValue AS fv1
INNER JOIN e.MyFeatureValue AS fv2
WHERE fv1.Id = 3
   AND fv2.Id = 13

you will need to build the HQL dynamically so that your aliases becomes unique (fv1, fv2, fvX ...)

Answer 4

The code looks like you are passing in a list of FeaturesValueIds and want a List that has all of those features. If that's the case, I'd take a look at the SQL that is being generated, and run it against the database to see if you should be getting back anything.

Otherwise, if you are looking for a List that has any of the Features you are passing in, you should use a Disjunction rather than a Conjunction.

Answer 5

    exec sp_executesql N'SELECT TOP 3 id11_1_, Address11_1_, Title11_1_, Descript4_11_1_, 
    Price11_1_, Discount11_1_, ForBankL7_11_1_, AddDate11_1_, LastUpdate11_1_, 
IsVisible11_1_, ViewCount11_1_, SaleOrRent11_1_, LocationId11_1_, StaffId11_1_, 
CategoryId11_1_, id27_0_, EstateId27_0_, FeatureV3_27_0_ FROM (SELECT ROW_NUMBER() 
OVER(ORDER BY __hibernate_sort_expr_0__) as row, query.id11_1_, query.Address11_1_, 
query.Title11_1_, query.Descript4_11_1_, query.Price11_1_, query.Discount11_1_, 
query.ForBankL7_11_1_, query.AddDate11_1_, query.LastUpdate11_1_, query.IsVisible11_1_, 
query.ViewCount11_1_, query.SaleOrRent11_1_, query.LocationId11_1_, query.StaffId11_1_, 
query.CategoryId11_1_, query.id27_0_, query.EstateId27_0_, query.FeatureV3_27_0_, 
query.__hibernate_sort_expr_0__ FROM (SELECT this_.id as id11_1_, this_.Address as 
Address11_1_, this_.Title as Title11_1_, this_.Description as Descript4_11_1_, this_.Price 
as Price11_1_, this_.Discount as Discount11_1_, this_.ForBankLoan as ForBankL7_11_1_, 
this_.AddDate as AddDate11_1_, this_.LastUpdate as LastUpdate11_1_, this_.IsVisible as 
IsVisible11_1_, this_.ViewCount as ViewCount11_1_, this_.SaleOrRent as SaleOrRent11_1_, 
this_.LocationId as LocationId11_1_, this_.StaffId as StaffId11_1_, this_.CategoryId as 
CategoryId11_1_, estatefeat1_.id as id27_0_, estatefeat1_.EstateId as EstateId27_0_, 
estatefeat1_.FeatureValueId as FeatureV3_27_0_, CURRENT_TIMESTAMP as 
__hibernate_sort_expr_0__ FROM Estate this_ inner join EstateFeature estatefeat1_ on 
this_.id=estatefeat1_.EstateId WHERE this_.CategoryId = @p0 and 
(estatefeat1_.FeatureValueId = @p1 and estatefeat1_.FeatureValueId = @p2 and 
estatefeat1_.FeatureValueId = @p3 and estatefeat1_.FeatureValueId = @p4 and 
estatefeat1_.FeatureValueId = @p5 and estatefeat1_.FeatureValueId = @p6 and 
estatefeat1_.FeatureValueId = @p7)) query ) page WHERE page.row > 0 ORDER BY 
__hibernate_sort_expr_0__',N'@p0 bigint,@p1 bigint,@p2 bigint,@p3 bigint,@p4 bigint,@p5 
bigint,@p6 bigint,@p7 bigint',@p0=3,@p1=7,@p2=8,@p3=9,@p4=10,@p5=11,@p6=12,@p7=16

Answer 6

It looks like you want or (Disjunction) instead of and (Conjunction). Right now, you are searching for EstateFeatures objects such that each object has multiple different Ids, which seems not to be what you want.

var or = new Disjunction();
foreach(var id in idCollection)
    or.Add(Expression.Eq("MyFeatureValue.Id", id);

var query = DetachedCriteria.For<Estate>();
query
    .CreateCriteria("EstateFeatures")
    .Add(and);
var estates = query.GetExecutableCriteria(session).List<Estate>();

Answer 7

I also tried this, but the result is the same:

DetachedCriteria features = DetachedCriteria.For<FeatureValue>();
features.SetProjection(Projections.Property("Id"));
features.Add(Property.ForName("Id").EqProperty("value.Id"));

var and = new Conjunction();

foreach (var l in FeatureIdCollection)
    and.Add(Expression.Eq("Id", l));

features.Add(and);

query.CreateCriteria("EstateFeatures")
     .CreateCriteria("MyFeatureValue","value")
     .Add(Subqueries.Exists(features));