为什么这两个 NHibernate 查询会产生不同的结果？
这是 M:N 关系，集合在 NHibernate 中被映射为 Set。
我们之前使用的条件查询“有效”，但它没有正确填充技能集合，因为即使员工拥有多种技能，也只有第一个/正在寻找的技能被删除。
我将其更改为 LINQ 查询，它解决了问题，正确获取了该员工的所有技能。
Dim sId = 1 ' Just to have one for example
Dim lstEmployees = Session.CreateCriteria(Of Employee)() _
.CreateAlias("Skills", "s", NHibernate.SqlCommand.JoinType.LeftOuterJoin) _
.Add(Expression.Or(Expression.Eq("PrimarySkillId", sId),
Expression.Eq("s.Id", sId))) _
.SetResultTransformer(New DistinctRootEntityResultTransformer()) _
.List(Of Employee)()
' Returns correct employees but only their first skill in the Skills collection, even if they have more than one
Dim lstEmployees = (From e In Session.Query(Of Employee)()
Where e.PrimarySkillId =sId OrElse e.Skills.Any(Function(s) s.Id = sId)
Select e Distinct).Fetch(Function(e) e.Skills).ToList()
' Returns correct employees and their Skills collection contains all of their skills
有谁明白这两个看似相同的查询有什么不同？
This is for a M:N relationship, with the collection being mapped in NHibernate as a Set.
The criteria query we were using previously "worked" but it did not populate the Skills collection properly, in that only the first/looking-for skill was brought down, even if the employee had multiple skills.
I changed it to a LINQ query and it fixes the issue, properly fetching all the skills of that employee.
Dim sId = 1 ' Just to have one for example
Dim lstEmployees = Session.CreateCriteria(Of Employee)() _
.CreateAlias("Skills", "s", NHibernate.SqlCommand.JoinType.LeftOuterJoin) _
.Add(Expression.Or(Expression.Eq("PrimarySkillId", sId),
Expression.Eq("s.Id", sId))) _
.SetResultTransformer(New DistinctRootEntityResultTransformer()) _
.List(Of Employee)()
' Returns correct employees but only their first skill in the Skills collection, even if they have more than one
Dim lstEmployees = (From e In Session.Query(Of Employee)()
Where e.PrimarySkillId =sId OrElse e.Skills.Any(Function(s) s.Id = sId)
Select e Distinct).Fetch(Function(e) e.Skills).ToList()
' Returns correct employees and their Skills collection contains all of their skills
Does anyone understand what's different about the two seemingly-equivalent queries?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
首先，第一个查询在检索所有行后执行不同的分离，而第二个查询实际上执行
select unique ...。可能发生的情况是，它仅使用检索到的一个 Skill 来水合 Employee 模型。要使第一个查询实际上执行
select unique ...，您将需要使用投影。类似的东西
可能会起作用。或者，您可以尝试使用 DistinctRootEntityResultTransformer 设置获取模式
Off the bat, the first query is doing the distinct separation after retrieving all rows, while the second query actually does a
select distinct .... What's likely happening is that it hydrates the Employee model with just the one Skill retrievedTo make the first query actually do a
select distinct ...you will need to use projections.Something like
may work. Alternatively you can try to just set the fetch mode with the DistinctRootEntityResultTransformer