仅在使用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将该表映射到一个对象，该对象与该表是1比1的匹配.但是，对于系统的特定功能，我只关心其中的两列.

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类中具有设置器，它可以是受保护的设置器，但必须具有设置器.

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类的新实例中.上面有同样的限制，除了稍微更严格一点，因为所有映射的属性都必须有一个公共的setter，因为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屋！