NHIbernate 3.0 - QueryOver,使用具有不同和顺序的相同投影会引发 sql 错误

发布于 2024-12-19 17:00:08 字数 2037 浏览 2 评论 0原文

我有一个NHibernate QueryOver,它生成sql错误:如果指定了SELECT DISTINCT,ORDER BY项目必须出现在选择列表中

该问题是由我用来选择、位置和顺序的sql投影引起的经过。因为投影本身使用了sql函数,所以它有参数(一个常量:空格)。

当使用分配给变量的投影时,NH 将此变量的每次使用唯一地转换为 sql,这意味着每次使用都会获得自己的新 sql 参数。因此Sql认为语句不同。我尝试使用别名进行投影,但没有成功,但似乎没有办法使用 QueryOver 来做到这一点。

除了回归 Criteria API 之外,其他想法都失去了一些。

这是简化的 QueryIOver 代码:

  var projection = ContactOrCompanyName();
  return Session.QueryOver<Contact>()
    .Select(
      Projections.Distinct(
        Projections.ProjectionList()
          .Add(Projections.Property<Contact>(x => x.Id).As("ContactId"))
          .Add(projection)
        )
    )
    .TransformUsing(Transformers.AliasToBean<ContactDto>())
    .OrderBy(projection).Asc;

private IProjection ContactOrCompanyName
    {
      get
      {
        return Projections.SqlFunction(
          "coalesce",
          NHibernateUtil.String,
          Projections.Property<Contact>(c => c.CompanyName),
          Projections.SqlFunction(
            "concat",
            NHibernateUtil.String,
            Projections.Property<Contact>(c => c.FirstName),
            Projections.Constant(" "),
            Projections.Property<Contact>(c => c.LastName)
          )
        );
      }
    }

产生以下 sql:

SELECT distinct   
this_.CONTACT_ID as y0_, 
coalesce(this_.COMPANY_NM, (this_.FIRST_NM+@p0+this_.LAST_NM)) as y1_ 
FROM dbo.ADD_CONTACT this_ 
ORDER BY coalesce(this_.COMPANY_NM, (this_.FIRST_NM+@p1+this_.LAST_NM)) asc

Criteria API 似乎确实支持别名重用,通过此示例:

IList results = session.CreateCriteria(typeof(DomesticCat), "cat")
    .CreateAlias("kittens", "kit")
    .SetProjection( Projections.ProjectionList()
        .Add( Projections.Property("cat.Name"), "catName" )
        .Add( Projections.Property("kit.Name"), "kitName" )
    )
    .AddOrder( Order.Asc("catName") )
    .AddOrder( Order.Asc("kitName") )
    .List();

那么它在 QueryOver 中的哪里?

I have an NHibernate QueryOver which is generating the sql error : ORDER BY items must appear in the select list if SELECT DISTINCT is specified

The problem is caused by a sql projection I am using to select, where and order by. Because the projection itself uses a sql function, it has parameters (a constant: space).

When using the projection assigned to a variable, NH translates each use of this variable uniquely to sql, meaning each gets its own new sql parameter. Sql therefore thinks the statements are different. I have tried to no avail using aliases for the projections, but there seems no way to do this with QueryOver.

Bit lost for ideas other than devolving back to the Criteria API.

This is the QueryIOver code simplified:

  var projection = ContactOrCompanyName();
  return Session.QueryOver<Contact>()
    .Select(
      Projections.Distinct(
        Projections.ProjectionList()
          .Add(Projections.Property<Contact>(x => x.Id).As("ContactId"))
          .Add(projection)
        )
    )
    .TransformUsing(Transformers.AliasToBean<ContactDto>())
    .OrderBy(projection).Asc;

private IProjection ContactOrCompanyName
    {
      get
      {
        return Projections.SqlFunction(
          "coalesce",
          NHibernateUtil.String,
          Projections.Property<Contact>(c => c.CompanyName),
          Projections.SqlFunction(
            "concat",
            NHibernateUtil.String,
            Projections.Property<Contact>(c => c.FirstName),
            Projections.Constant(" "),
            Projections.Property<Contact>(c => c.LastName)
          )
        );
      }
    }

results in the following sql:

SELECT distinct   
this_.CONTACT_ID as y0_, 
coalesce(this_.COMPANY_NM, (this_.FIRST_NM+@p0+this_.LAST_NM)) as y1_ 
FROM dbo.ADD_CONTACT this_ 
ORDER BY coalesce(this_.COMPANY_NM, (this_.FIRST_NM+@p1+this_.LAST_NM)) asc

Criteria API does seem to support alias reuse going by this example:

IList results = session.CreateCriteria(typeof(DomesticCat), "cat")
    .CreateAlias("kittens", "kit")
    .SetProjection( Projections.ProjectionList()
        .Add( Projections.Property("cat.Name"), "catName" )
        .Add( Projections.Property("kit.Name"), "kitName" )
    )
    .AddOrder( Order.Asc("catName") )
    .AddOrder( Order.Asc("kitName") )
    .List();

So where is it in QueryOver?

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

扫码二维码加入Web技术交流群

发布评论

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

评论(2

一枫情书 2024-12-26 17:00:08

这有点 hacky,但它确实有效。通过创建两个投影,一个用于比较,一个用于选择,我们可以阻止 SQL 抱怨投影顺序未包含在选择列表中:

      CompanyDirectorDto dtoAlias = null;
      var contactsQuery = Session.QueryOver<Contact>()
        .Select(
          Projections.Distinct(
            Projections.ProjectionList()
              .Add(Projections.Property<Contact>(x => x.Id).WithAlias(() => dtoAlias.ContactId))
              .Add(ContactOrCompanyNameComparer)
              .Add(ContactOrCompanyNameSelector.WithAlias(() => dtoAlias.ContactDisplayName))
            )
        )
        .TransformUsing(Transformers.AliasToBean<CompanyDirectorDto>())
        .OrderBy(ContactOrCompanyNameComparer).Asc;

private IProjection ContactOrCompanyNameSelector
{
  get
  {
    return Projections.SqlFunction(
      "coalesce",
      NHibernateUtil.String,
      Projections.Property<Contact>(c => c.CompanyName),
      Projections.SqlFunction(
        "concat",
        NHibernateUtil.String,
        Projections.Property<Contact>(c => c.FirstName),
        Projections.Constant(" "),
        Projections.Property<Contact>(c => c.LastName)
      )
    );
  }
}

private IProjection ContactOrCompanyNameComparer
{
  get
  {
    return Projections.SqlFunction(
      "coalesce",
      NHibernateUtil.String,
      Projections.Property<Contact>(c => c.CompanyName),
      Projections.SqlFunction(
        "concat",
        NHibernateUtil.String,
        Projections.Property<Contact>(c => c.FirstName),
        Projections.Property<Contact>(c => c.LastName)
      )
    );
  }
}

This is a bit hacky, but it works. By creating two projections one for comparison and one for selection, we can stop SQL complaining about the order by projection not being included in the select list:

      CompanyDirectorDto dtoAlias = null;
      var contactsQuery = Session.QueryOver<Contact>()
        .Select(
          Projections.Distinct(
            Projections.ProjectionList()
              .Add(Projections.Property<Contact>(x => x.Id).WithAlias(() => dtoAlias.ContactId))
              .Add(ContactOrCompanyNameComparer)
              .Add(ContactOrCompanyNameSelector.WithAlias(() => dtoAlias.ContactDisplayName))
            )
        )
        .TransformUsing(Transformers.AliasToBean<CompanyDirectorDto>())
        .OrderBy(ContactOrCompanyNameComparer).Asc;

private IProjection ContactOrCompanyNameSelector
{
  get
  {
    return Projections.SqlFunction(
      "coalesce",
      NHibernateUtil.String,
      Projections.Property<Contact>(c => c.CompanyName),
      Projections.SqlFunction(
        "concat",
        NHibernateUtil.String,
        Projections.Property<Contact>(c => c.FirstName),
        Projections.Constant(" "),
        Projections.Property<Contact>(c => c.LastName)
      )
    );
  }
}

private IProjection ContactOrCompanyNameComparer
{
  get
  {
    return Projections.SqlFunction(
      "coalesce",
      NHibernateUtil.String,
      Projections.Property<Contact>(c => c.CompanyName),
      Projections.SqlFunction(
        "concat",
        NHibernateUtil.String,
        Projections.Property<Contact>(c => c.FirstName),
        Projections.Property<Contact>(c => c.LastName)
      )
    );
  }
}
星星的轨迹 2024-12-26 17:00:08

我遇到了同样的问题,NHibernate 为 selectorder by 的常量投影生成不同的参数:

select distinct 
    (case
        when this_."SomeColumn" is null then :p1
        else this_."SomeColumn"
    end) as y3_
from
...
order by
    (case
        when this_."SomeColumn" is null then :p3
        else this_."SomeColumn"
    end) desc

请注意参数 :p1 和<代码>:p3。 Postgres 给了我错误42P10:对于 SELECT DISTINCT,ORDER BY 表达式必须出现在选择列表中。我能够使用 OrderByAlias 修复它。对于您的情况,修复如下所示:

      CompanyDirectorDto dtoAlias = null;
      var contactsQuery = Session.QueryOver<Contact>()
        .Select(
          Projections.Distinct(
            Projections.ProjectionList()
              .Add(Projections.Property<Contact>(x => x.Id).WithAlias(() => dtoAlias.ContactId))
              .Add(ContactOrCompanyNameSelector.WithAlias(() => dtoAlias.ContactDisplayName))
            )
        )
        .TransformUsing(Transformers.AliasToBean<CompanyDirectorDto>())
        .OrderByAlias(() => dtoAlias.ContactDisplayName).Asc;

不需要 ContactOrCompanyNameComparer 。这适用于 postgres,但不确定是否适用于其他数据库。

I had the same issue with NHibernate generating different parameter for a constant projection for the select and for the order by:

select distinct 
    (case
        when this_."SomeColumn" is null then :p1
        else this_."SomeColumn"
    end) as y3_
from
...
order by
    (case
        when this_."SomeColumn" is null then :p3
        else this_."SomeColumn"
    end) desc

Please note parameters :p1 and :p3. Postgres gave me the error 42P10: for SELECT DISTINCT, ORDER BY expressions must appear in select list. I was able to fix it using OrderByAlias. For your case, the fix would look like this:

      CompanyDirectorDto dtoAlias = null;
      var contactsQuery = Session.QueryOver<Contact>()
        .Select(
          Projections.Distinct(
            Projections.ProjectionList()
              .Add(Projections.Property<Contact>(x => x.Id).WithAlias(() => dtoAlias.ContactId))
              .Add(ContactOrCompanyNameSelector.WithAlias(() => dtoAlias.ContactDisplayName))
            )
        )
        .TransformUsing(Transformers.AliasToBean<CompanyDirectorDto>())
        .OrderByAlias(() => dtoAlias.ContactDisplayName).Asc;

ContactOrCompanyNameComparer is not needed. This works for postgres, not sure for other DBs.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文