使用子查询和分组的Nhibernate内部联接的SQL

Jason Bowers 发表于 Dev

杰森·鲍尔斯（Jason Bowers）

如果有人可以帮助我将以下SQL转换为NHLibernate linq并转换为QueryOver API，我将不胜感激。

SELECT p.ManufacturerName as Name, sum(ps.QtyAvail) as QuantityAvailable 
from Product p
inner join (select ProductId, QtyAvail 
from ProductStats ps
where ps.QtyAvail > 0) ps on p.ProductId = ps.ProductId
where ltrim(rtrim(isnull(p.ManufacturerName, ''))) <> ''
group by p.ManufacturerName
order by Name

到目前为止，这是我唯一可以编译和运行的东西。

        var o = Session
            .Query<Product>()
            .Where(p => p.ManufacturerName != null && p.ManufacturerName.Trim() != string.Empty)
            .Join(Session.Query<ProductStats>().Where(ps => ps.QtyAvail > 0), product => product.ProductId, stats => stats.ProductStatId,
                (product, stats) => new { Name = product.ManufacturerName, QuantityAvailable = stats.QtyAvail })
            .GroupBy(q => q.Name)
            .Select(g => new { Name = g.Key, QuantityAvailable = g.Sum(v => v.QuantityAvailable) });

提前致谢。

杰森·鲍尔斯（Jason Bowers）

好的，

通过安德鲁·惠特克（Andrew Whitaker）的大量贡献找出了答案，网址为http://blog.andrewawhitaker.com/queryover-series/

    [TestMethod]
    public void CanGetManufacturersWithOnHandQuantities()
    {
        ProductStats statsAlias = null;
        ManufacturersInStock manufacturersInStock = null;

        var o = Session
            .QueryOver<Product>()
            .OrderBy(p => p.ManufacturerName).Asc
            .WhereStringIsNotNullOrEmpty(p => p.ManufacturerName)
            .JoinQueryOver(p => p.Stats, () => statsAlias)
            .Where(ps => ps.QtyAvail > 0)
            .SelectList(l => l.SelectGroup(p => p.ManufacturerName).WithAlias(() => manufacturersInStock.Name)
                              .SelectSum(() => statsAlias.QtyAvail).WithAlias(() => manufacturersInStock.InStockCount))
            .TransformUsing(Transformers.AliasToBean<ManufacturersInStock>());


        var results = o.List<ManufacturersInStock>();
        Assert.IsTrue(o.RowCount() > 0);
    }

public class ManufacturersInStock
{
    public string Name { get; set; }

    public int InStockCount { get; set; }
}

public static class QueriesExtentions
{
    public static IQueryOver<E, F> WhereStringIsNotNullOrEmpty<E, F>(this IQueryOver<E, F> query, Expression<Func<E, object>> propExpression)
    {
        var prop = Projections.Property(propExpression);
        var criteria = Restrictions.Or(Restrictions.IsNull(prop), Restrictions.Eq(Projections.SqlFunction("trim", NHibernateUtil.String, prop), ""));
        return query.Where(Restrictions.Not(criteria));
    }
}