NHibernate有关不加载集合以获取该集合计数的问题... [英] NHibernate question about NOT loading a collection to get a count of that collection...

问题描述

好的，

我有以下情况.我有一个可以有0到x个用户的公司.我有一个Firm实体和一个User实体.

我使用ADO.NET手动滚动了自己的存储库层，现在我将其切换到NHibernate，但是我被困在我的Firm实体的一个属性上.

这是一个名为.HasUsers的属性.之前，此属性将根据分配给特定公司的用户数量返回一个布尔值.我通过将子查询嵌套到我的FirmRepostiory中来通过直接SQL做到了这一点.

在NHibernate中，我意识到我可以在Firm上创建一个Users属性，然后映射我的实体以通过默认的延迟加载(这是NHibernate)加载所有Users.

但是我不在乎与公司关联的实际用户，我只是在关心公司的用户数是否超过0.我希望能够仅获取与给定公司关联的用户的数据库方面的计数，而不必为了查看我的用户数是否超过零而懒惰地加载用户集合.

这样做似乎浪费大量资源...如果我有数百名用户该怎么办?然后，我不得不重新创建数百个User对象，以减少集合的数量，以查看Firm.HasUsers是否返回true?

一定有更好的方法.是我需要做的映射还是NHibernate的工作原理，我是否必须在我的Firm表上放一个新列，该列中包含多个Users列，其中包含一个数字?这也似乎是一场等待发生的事故.

有什么想法吗?

谢谢，
Mike

Okay,

I have the following scenario. I have a Firm that can have 0 to x Users. I have a Firm entity and a User entity.

I hand-rolled my own repository layer using ADO.NET, and now I''m switching that over to NHibernate, but I''m stuck on one property of my Firm entity.

It''s a property called .HasUsers. This property before would return me a boolean based on the count of the amount of users that were assigned to a particular Firm. I did this via straight SQL by nesting a subquery into my FirmRepostiory.

In NHibernate, I realize I could create a Users property on Firm, and then map my entity to load all Users via lazy-loading (which is NHibernate) default.

But I don''t care about the actual Users associated with a Firm, I just care if a Firm has more than 0 Users. I''d like to be able to just grab a count on the DB side of Users associated with a given Firm, and NOT have to lazy-load the Users collection just to see if I have more than zero Users.

To do that seems like a huge waste of resources... what if I had hundreds of Users? Then I''d have to new up hundreds of User objects just to take a count off the collection to see if Firm.HasUsers returns true or not?

There has to be a better way. Is it something I need to do in my mapping, or b/c of the way NHibernate works, would I have to put a new column on my Firm table that has a number of Users column, that contained a number? That also too seems like an accident waiting to happen.

Any ideas?

Thanks,
Mike

推荐答案

我发现了这个

I found this Getting Row Count in NHibernate[^], see if it delivers the performance your want.

我找到了我所需要的此处:
http://stackoverflow.com/questions/2300762/how-to-map-count-of-collection-to-entity-with-fluent-nhibernate [ ^ ]

我希望在映射中声明此信息，而我正在使用FluentNHibernate.

它围绕着使用名为.FormulaIs()的东西(在NHibernate 3.x中已更改为.Formula())来获取计数并使其成为实体映射的子查询.
基本上，公式可以使用自定义SQL查询将其附加(或使其成为子查询)，作为实体基本映射的一部分.例如，在我的Firm对象中，我想要一个.HasUsers属性返回一个布尔值，该布尔值表示我检索的Firm是否具有1个或多个用户.

因此我的属性保持不变(除了将其虚拟化以便NHibernate可以代理它)，但这是我的映射指令现在的样子:

I found what I needed here:
http://stackoverflow.com/questions/2300762/how-to-map-count-of-collection-to-entity-with-fluent-nhibernate[^]

I wanted this information to be declared in the mapping, and I''m using FluentNHibernate.

It revolves around using something called .FormulaIs() (which has been changed to .Formula() in NHibernate 3.x) to get a count and make it a sub-query of the entity mapping.

Basically, a Formula can take a custom SQL query append it (or make it a sub-query) as part of the basic mapping for an entity. For example, in my Firm object, I wanted a .HasUsers property to return a boolean as to whether the Firm I retrieve has 1 or more users.

so my property stayed the same (with the exception of making it virtual so NHibernate could proxy it), but here is what my mapping directive looks like now:

Map(x => x.HasUsers).Formula("case when (select count(*) from [user] where firm_id = Id) > 0 then 1 else 0 end").Not.Insert().Not.Update();

请注意，我获得了给定公司的所有用户数，然后通过SQL case语句提供该数以返回布尔值.

您可以通过在Fluent语句的末尾放置.LazyLoad()来选择显式延迟加载公式(默认为eager-load).如果您延迟加载它，您将获得两个单独的SQL查询，其中一个用于常规会话.查询(假设它不是从NHibernate的内置Identity Map中获取的)，以及当您访问该属性时的第二个查询.

另请注意，添加了.Not().Insert().Not().Update()...，以防万一某个进程决定要更改该属性.它应该是只读的.

希望这会有所帮助！

Note that I get the count of all users for a given firm, then feed that through a SQL case statement to return a boolean value.

You can choose to explicitly lazy-load the Formula (default is eager-load) by putting .LazyLoad()at the end of the Fluent statement. If you lazy-load it, you''ll get two separate SQL queries, one for the regular session.Get<firm> query (assuming it''s not taking it from the built-in Identity Map of NHibernate), and a second query when you go to access the property.

Also note the addition of the .Not().Insert().Not().Update()... just in case some process decides it wants to try to change that property. It should be read-only.

Hope this helps!

Mikey

进行count(0)将对数据库进行全表扫描.

最好使用Exists，因为当找到第一个匹配项时它将停止扫描表.

您的代码将成为

Mikey

Doing a count(0) will do a full table scan on the database.

It is better to use Exists, as it will stop scanning the table when it finds a first match.

Your code would become

Map(x => x.HasUsers).Formula("case when exists (select 0 from [user] where firm_id = Id) then 1 else 0 end").Not.Insert().Not.Update();

这篇关于NHibernate有关不加载集合以获取该集合计数的问题...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！