仅在使用 Critera 查询时检索特定列? [英] Only retrieve specific columns when using Critera queries?

问题描述

我正在为我们公司正在进行的一个项目推荐 NHibernate，我想知道是否可以优化 NHibernate，以便在使用 Criteria 查询语言时仅检索表中的特定列.

I'm propositioning NHibernate for a project we're doing at my company and I'd like to know if NHibernate can be optimized to only retrieve specific columns on a table when using the Criteria query language.

例如.假设我有一个包含 30 列的表，它使用 NHibernate 映射到一个对象，该对象与该表进行一对一匹配.但是，对于系统的特定功能，我只关心其中的两列.

For example. Let's say I have a table with 30 columns on it and this is mapped to an object using NHibernate that is a 1-for-1 match against the table. However, for a particular function of the system I only care about two of those columns.

现在，我知道我可以使用 HQL 并执行一个 CreateQuery 来实现这一点，但这需要我为我想要有选择地检索的每个字段组合创建一个构造函数.从维护的角度来看，这可能是一个巨大的痛苦，因为我不会在运行时之前捕获丢失的构造函数.

Now, I know I can use HQL and do a CreateQuery that will accomplish this but that requires I create a constructor for each combination of fields I'd like to selectively retrieve. This could be a huge pain from a maintenance standpoint since I won't catch missing constructors till runtime.

我喜欢 Criteria 查询语言，因为它生成参数化 SQL 而不是来自 HQL 的直接 SQL 查询.我看到有一个排除"模型不包含某些列，但在大多数情况下，我会包含比排除更多的列.

I like the Criteria query language since it produces parametrized SQL instead of straight SQL queries from HQL. I see there is an "Exclude" model for not including certain columns but in most cases I will include more columns than exclude.

感谢下面的评论，我研究了预测，但这对我来说仍然不是理想的情况.使用以下内容时:

Thanks to the comment below I looked into projections and this still isn't quite the ideal situation for me. When using the following:

var list = session
    .CreateCriteria(typeof (Task))
    .SetProjection(Projections
                       .ProjectionList()
                       .Add(Projections.Property("Id")))
    .List();

我最终得到的变量 list 只是整数，我希望拥有完整的 Task 对象，但所有字段都设置为其默认值.这甚至可能吗?到目前为止，我所看到的一切都说不.

I end up with the variable list just being ints, I'd prefer to have my full Task object but with all the fields set to their default values. Is this even possible? Everything I see so far says no.

推荐答案

是的，您可以通过使用投影对标准查询执行此操作.只需投影您希望使用的属性，并且只有那些将包含在编译查询的 select 子句中.

Yes you can do this with criteria queries by using projections. Simply project only the properties you wish to use and only those will be included in the select clause of the compiled query.

http://nhibernate.info/doc/nh/en/index.html#querycriteria-projection

更新以进行编辑

有几种方法可以实现这一点，但有一些限制.1)NHibernate方式.

There are several ways to accomplish this, with some limitations however. 1) The NHibernate way.

var list = session.CreateCriteria(typeof (Task))
.SetProjection(Projections.ProjectionList()
                   .Add(Projections.Property("Name"), "Name")
                   .Add(Projections.Property("ID"), "ID")
)
.SetResultTransformer(Transformers.AliasToBean(typeof (Task)))
.List();

只需将属性名称指定为投影的别名，AliasToBean 转换器就会将这些投影映射到实际的类.此方法的限制是您映射的任何属性都必须在 POCO 类中有一个 setter，这可以是受保护的 setter，但它必须有一个 setter.

Simply assign the property name as an alias to your projection and the AliasToBean transformer will map those projections to an actual class. The limitation to this method is that any properties that you map must have a setter in the POCO class, this can be a protected setter but it must have a setter.

您也可以使用 linq 以稍微不同的方式执行此操作

You can also do this with linq as well in an a slightly different fashion

var list = session.CreateCriteria(typeof (Task))
.SetProjection(Projections.ProjectionList()
                   .Add(Projections.Property("Name"))
                   .Add(Projections.Property("ID"))
)
.List<IList>()
.Select(l => new Task() {Name = (string) l[0], ID = (Guid) l[1]});

这只是使用 linq 将输出的索引列表映射到 Task 类的新实例中.与上述相同的限制适用，只是这有点严重，因为映射的所有属性都必须有一个公共设置器，因为这是 linq 用来填充对象的方法.

This is simply using linq to map the indexed list that is ouput into a new instance of the Task class. The same limitation as above applies except that this is a bit more severe in that all the properties mapped must have a public setter because that is what linq uses to do fill in the object.

希望对你有帮助.

这篇关于仅在使用 Critera 查询时检索特定列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！