C# 使用 Nhibernate Criteria Api 查询集合？

Querying on Collection with Nhibernate Criteria Api?

提问by Barbaros Alp

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

我有一个“Estate”实体，这个实体有一个集合“EstateFeatures”（类型：EstateFeature），EstateFeature 有一个属性“MyFeatureValue”。

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

注意：这些是问题的有限属性。所有实体都有一个 ID 和所有必需品等

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

我正在尝试获取具有给定 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

谢谢

采纳答案by jishi

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

您需要确保为您希望遗产拥有的每个功能加入一次 MyFeatureValue。

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

一种方法是为每次迭代调用 .CreateAlias，给它一个唯一的别名，然后添加表达式“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.

编辑：由于 Criteria API 中的错误限制您使用 CreateAlias 或 CreateCriteria 多次关联集合，因此您需要求助于 HQL。

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

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

(Hibernate suffers from the same issue aswell)

（Hibernate 也有同样的问题）

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

您将需要动态构建 HQL，以便您的别名变得唯一（fv1、fv2、fvX ...）

回答by RKitson

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.

代码看起来像是传入了一个 FeaturesValueIds 列表，并且想要一个包含所有这些功能的 List。如果是这种情况，我会查看正在生成的 SQL，然后针对数据库运行它，看看是否应该返回任何内容。

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.

否则，如果您正在寻找具有您传入的任何功能的列表，您应该使用析取而不是连接。

回答by Frederik Gheysels

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

NHibernate 为您生成了什么查询？您可以使用 show_sql 配置属性进行检查。

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

如果您想检索包含所有指定功能的所有资产，我认为您必须使用析取，以便 NHibernate 检索至少具有这些功能之一的所有资产。然后，在您的客户端代码中，您将检查“客户端代码”中的每个 Estate，以便您最终得到具有所有功能的 Estate。
我不知道是否有一种有效的方法可以让 NHibernate 处理这个......

回答by Barbaros Alp

    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

回答by Barbaros Alp

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

回答by yfeldblum

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.

看起来您想要or( Disjunction) 而不是and( Conjunction)。现在，您正在搜索EstateFeatures 对象，以便每个对象都有多个不同的Ids，这似乎不是您想要的。

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

回答by Remmus

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