igor-tkachev / bltoolkit

Business Logic Toolkit for .NET
MIT License
297 stars 112 forks source link

Wrong SQL generated with Grouping and Count! #312

Closed jogibear9988 closed 7 years ago

jogibear9988 commented 10 years ago

I've this Expression:

   var query = from m in s.Query<MaterialDTO>()
            join ir in s.Query<InventoryResourceDTO>() on m.Id equals ir.MaterialID into irc
            select new MaterialInfoDTO
            {
                Material = m,
                CountResources = irc.Count(),
                CountInventory = irc.Sum(x => x.Quantity)
            };

this works, I get the COUNT and SUM

but when I try to do a where after the query:

    query = query.Where(x => x.CountResources > 0);

wrong SQL is generated! (Tested with SQLite and MSSQL)

If I write like this, it works:

    var query = from m in s.Query<MaterialDTO>()
            select new MaterialInfoDTO
            {
                Material = m,
                CountResources = s.Query<InventoryResourceDTO>().Count(x => x.MaterialID == m.Id),
                CountInventory = s.Query<InventoryResourceDTO>().Where(x => x.MaterialID == m.Id).Sum(x => x.Quantity)
            };

        query = query.Where(x => x.CountResources > 0);