java - HQL 中的 Linq ".any"等价物

Question

我有一个 NHibernate Linq 查询，我需要将它转换为 HQL:

var result = Conexion.Session.Query<Person>();
result = result.Where(p => p.addresses.Any(a => a.City.Name == "mycity"));

返回在城市“MyCity”中至少有一个地址的人

如何将“any”子句翻译成“HQL sintax”？

谢谢。

Answer 1

要复制生成的 SQL:

var result = Conexion.Session.Query<Person>();
result = result.Where(p => p.addresses.Any(a => a.City.Name == "mycity"));

我们必须这样做:

var hql = "SELECT p FROM Person p WHERE EXISTS "
          + "(SELECT a FROM Address a "
          + "  LEFT JOIN a.City c "
          + "  WHERE p = a.Person " 
          + "    AND c.Name = 'mycity') ";

mycity 可以是偶数参数:

var hql = "SELECT p FROM Person p WHERE EXISTS "
          + "(SELECT a FROM Address a "
          + "  LEFT JOIN a.City c "
          + "  WHERE p = a.Person " 
          + "    AND c.Name = :mycity) ";

var result = Conexion.Session.CreateQuery(hql)
        .SetParameter("mycity", "...")
        .List<Person()

如果 Address 有对 Person 的反向引用，那将起作用，因为那是条件 WHERE p = a 的一部分。人

如果映射只包含从人到地址的路径，hql 必须是这样的:

var hql = "SELECT p FROM Person p WHERE EXISTS "
          + "(SELECT p2 FROM Person p2 "
          + "  LEFT JOIN p2.addresses a "
          + "  LEFT JOIN a.City c "
          + "  WHERE p = p2 " 
          + "    AND c.Name = :mycity) ";

即使从地址到人的引用丢失，这仍然有效