SQL to nhibernate inner join with sub query and grouping

Question

If someone could please help me convert the following SQL to nhibernate linq to QueryOver API, I would be greatly appreciative.

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

This is the only thing that I have so far that compiles and runs.

        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) });

Thanks in advance.

have you made any attempts using the QueryOver API? we will help you if you try but less likely to help if you put forth little effort. — Clay
– Clay, Commented Jan 3, 2016 at 1:24
Post what you have tried so we can see your mistakes. We need to see your code to help. — Clay
– Clay, Commented Jan 3, 2016 at 2:51
You can't join to arbitrary selects with QueryOver, just mapped tables. You'll have to use HQL or raw SQL. — Andrew Whitaker
– Andrew Whitaker, Commented Jan 3, 2016 at 3:52

Jason Bowers · Accepted Answer · 2016-01-03 04:34:40Z

Ok,

Figured out the answer with a lot of credit going to 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));
    }
}

Collectives™ on Stack Overflow

SQL to nhibernate inner join with sub query and grouping

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related