NHibernate Criteria 子查询帮助

发布于 2024-10-08 00:00:25 字数 402 浏览 3 评论 0原文

我正在使用 NHibernate，我的要求是我有 2 个表，用户和票证。我想要用户中但不在票证中的所有记录。 Ticket 表将 UserId 作为 User 表主键 ID 的参考键。下面是我的代码，

 RegNotTickTemplate.Criteria = DetachedCriteria.For(typeof(User));
            RegNotTickTemplate.Criteria.Add(Subqueries.PropertyNotIn("ID",DetachedCriteria.For(typeof(Ticket))
                .SetProjection(Projections.Property("UserID"))));

上面的查询没有返回正确的记录集。

原文

I am using NHibernate and I my requirement is that I have 2 tables, User and Ticket. I want all the records that are in User but not in Ticket. The Ticket table has UserId as reference key to the Primary key ID of User table. Below is my code,

 RegNotTickTemplate.Criteria = DetachedCriteria.For(typeof(User));
            RegNotTickTemplate.Criteria.Add(Subqueries.PropertyNotIn("ID",DetachedCriteria.For(typeof(Ticket))
                .SetProjection(Projections.Property("UserID"))));

The above query doesnt return correct set of records.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

爱*していゐ 2024-10-15 00:00:25

作为替代方案，您可以尝试使用 HQL。我从不使用 Criteria，因为我发现 HQL 更具可读性（它几乎与 SQL 相同，只是你可以根据实体而不是表进行查询）。

第 13 章 HQL：Hibernate 查询语言

IQuery query = Session.CreateQuery(
       "from User where Id not in (select UserId from Ticket)");
       query.List<User>();

如果仍然不行，您随时可以执行 SQL 查询

ISQLQuery query = Session.CreateSQLQuery(sql);

As an alternative, you could try using HQL. I never use Criteria as I find HQL more readable (it is almost the same as SQL except you get to query based on the entities rather than the tables).

Chapter 13. HQL: The Hibernate Query Language

IQuery query = Session.CreateQuery(
       "from User where Id not in (select UserId from Ticket)");
       query.List<User>();

If that is still no good, you can always do an SQL query

ISQLQuery query = Session.CreateSQLQuery(sql);

回复收藏 0 原文

So尛奶瓶 2024-10-15 00:00:25

您缺少的是：该用户的票数应该大于零。以下是实现它的方法：

RegNotTickTemplate.Criteria = DetachedCriteria.For(typeof(User));
RegNotTickTemplate.Criteria.Add(Subqueries.PropertyIn("Id",
          DetachedCriteria.For<Ticket>()
            .SetProjection(Projections.GroupProperty("User"))
            .Add(Restrictions.Eq(Projections.RowCount(), 0))));

如果您不需要行数，可以执行以下操作：

RegNotTickTemplate.Criteria = DetachedCriteria.For(typeof(User),"user");
RegNotTickTemplate.Criteria.Add(Subqueries.Exists(DetachedCriteria.For<Ticket>("ticket")
            .SetProjection(Projections.Property("User"))
            .Add(Restrictions.EqProperty("user.Id", "User"))));

What you are missing is: the count of tickets for this user should be greater than zero. Here is how you can implement it:

RegNotTickTemplate.Criteria = DetachedCriteria.For(typeof(User));
RegNotTickTemplate.Criteria.Add(Subqueries.PropertyIn("Id",
          DetachedCriteria.For<Ticket>()
            .SetProjection(Projections.GroupProperty("User"))
            .Add(Restrictions.Eq(Projections.RowCount(), 0))));

If you don't want the count of rows you can do the following:

RegNotTickTemplate.Criteria = DetachedCriteria.For(typeof(User),"user");
RegNotTickTemplate.Criteria.Add(Subqueries.Exists(DetachedCriteria.For<Ticket>("ticket")
            .SetProjection(Projections.Property("User"))
            .Add(Restrictions.EqProperty("user.Id", "User"))));

回复收藏 0 原文

~没有更多了~