Linq2NHibernate 优化推荐方式
我正在使用 Linq2NHibernate 在多个查询中从表中获取一堆数据。尽管该表包含 10 列，但我在查询中仅使用其中 3 列，之后我不需要其他属性。尽管如此，生成的 SQL 会（如预期）在每个查询上加载所有属性。
优化查询的推荐方法是什么？
制作更轻的实体变体用于优化查询是否有意义？即创建一个仅包含 2 个属性的实体，并将其映射到同一个表？如果实际的实体继承自较轻的实体，那么它不应该造成太多混乱，并且 NHibernate 最终将只获取较小的数据子集。
这是常见的做法，还是有更好的方法来优化查询？我更愿意坚持使用 LINQ，因为现在整个项目都在使用它。
[编辑]
正如 Mike 在下面回答的那样，解决方案非常明显：实际预计的 DTO 将决定要获取的必要属性。因此，我将提供一个示例，只是为了使其更清楚：
换句话说，此查询将加载所有属性：
/* fetch actual entities */
var results = session
.Query<Food>()
.Where(p => p.Proteins < 100 && p.Fats < 50);
/* ... results in: */
SELECT Id, Proteins, Fats, Carbs, Name, Whatever
FROM [Food] WHERE Proteins < 100 AND Fats < 50
而此查询将生成查询以仅获取预计的属性：
/* fetch light DTOs */
var results = session
.Query<Food>()
.Where(p => p.Proteins < 100 && p.Fats < 50)
.Select(p => new
{
Proteins = p.Proteins,
Fats = p.Fats
});
/* generated sql: */
SELECT Proteins, Fats
FROM [Food] WHERE Proteins < 100 AND Fats < 50
I am using Linq2NHibernate to fetch a bunch of data from a table in several queries. Although the table contains 10 columns, I am only using 3 of them in my query, and I don't need other properties afterwards. Generated SQL nevertheless loads (as expected) all properties on each query.
What is the recommended way to optimize the query?
Does it make sense to make lighter entity variants to be used for optimized queries? I.e. to create an entity containing only 2 properties, and map it to the same table? If the actual entity inherits from the lighter entity, it shouldn't make too much mess, and NHibernate would end up fetching only the smaller subset of data.
Is this common practice, or is there a better way to optimize queries? I would prefer to stick to LINQ, since it is used throughout the project right now.
[Edit]
As Mike answered below, the solution is pretty obvious: actual projected DTOs will dictate the necessary properties to fetch. So I will provide an example, just to make it clearer:
In other words, this query will load all properties:
/* fetch actual entities */
var results = session
.Query<Food>()
.Where(p => p.Proteins < 100 && p.Fats < 50);
/* ... results in: */
SELECT Id, Proteins, Fats, Carbs, Name, Whatever
FROM [Food] WHERE Proteins < 100 AND Fats < 50
While this one will generate the query to fetch only the projected properties:
/* fetch light DTOs */
var results = session
.Query<Food>()
.Where(p => p.Proteins < 100 && p.Fats < 50)
.Select(p => new
{
Proteins = p.Proteins,
Fats = p.Fats
});
/* generated sql: */
SELECT Proteins, Fats
FROM [Food] WHERE Proteins < 100 AND Fats < 50
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于您尚未发布实际查询，因此很难看出问题所在。通常，您将仅使用所需的字段填充数据传输对象 (DTO)。这将解决一个问题。至于您的 LINQ 查询（包括域实体中的每个字段），您必须使用投影。投影将告诉 NHibernate 仅获取那些不包括域的其余部分的内容。请注意，我不使用 LINQ 查询 NHibernate，但概念是相同的。
顺便说一句，我确实同意 Matías Fidemraizer 的观点，即您应该考虑使用新的 QueryOver API。
Since you haven't posted your actual query it's difficult to see what the problem is. Typically, you would populate a data transfer object (DTO) with only the fields you require. That will fix one issue. As for your LINQ query including every field in your domain entity you have to use projections. Projections will tell NHibernate to only fetch those excluding the rest of your domain. Note that I don't query NHibernate with LINQ but the concepts are the same.
As an aside I do agree with Matías Fidemraizer that you should consider using the new QueryOver API.