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

Unparliamentary

Answer 1

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 ...)

Actinium

I got an error saying "duplicate association path: MyFeatureValue" – Unparliamentary

Right, this is somewhat a bug in the Criteria API. You will have to resort to HQL because I don't think they have fixed it. – Actinium

Thanks jishi, i am going to try it know. get back when i am finished with it. – Unparliamentary

Answer 2

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>();

Hernardo

yes, kind of... I am doing this already, but i have an id collection.Thanks for your answer – Unparliamentary

Answer 3

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 ...

Reo

Doesnt my query return the Estates which have the all features(not only at least one of those features) ? – Unparliamentary

Could you explain this more detailed ? "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." – Unparliamentary

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.

Vinaya

When i use Disjunctions, some values return from the query but they the result which have any of these Features. I want to get Results which have only this Features ? so doesnt my query right ? – Unparliamentary

{DetachableCriteria(MyFeatureValue.Id = 7 and MyFeatureValue.Id = 8 and MyFeatureValue.Id = 9 and MyFeatureValue.Id = 10 and MyFeatureValue.Id = 11 and MyFeatureValue.Id = 12 and MyFeatureValue.Id = 16)} This is the query(DetachedCriteria) – Unparliamentary

No this is the Detached Criteria, sql is on the way – Unparliamentary

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

Unparliamentary

Well that's pretty. Give me a minute. – Vinaya

I don't see in the Criteria where you are asking for Estates where CategoryID = 3, that could have something to do with it. – Vinaya

isnt it the "where CategoryID=3" WHERE this_.CategoryId = @p0 – Unparliamentary

Right, but where are asking for that in your Criteria? – Vinaya

ohhh, i see. It is on the upper side of the code. That's why i didnt mention about it. also i try it without category= 3 , the result is the same. Do you recommend something else or i am on the wrong way ? – Unparliamentary

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>();

Chiropractic

I think i want conjuction becuase i need Estates which has only the featureIdcollection. It must contain just them, not less not more. Isnt it conjuction(and) ? – Unparliamentary

I have tried it before, ok i get Estates but one Estate comes more than once. I use query .SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer()); but it doesnt work very well with my paging. SetMaxResults and SetFirstResult – Unparliamentary

It only Distinct the values in each page not in whole result set, i know this is another topic but do you have any idea about it – Unparliamentary

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));

Unparliamentary