public void JoinWithMultipleSelect() { ExpressionsTests.SetModeling(); Configuration.SetDialect(new TestDialect()); var b = new SelectFrom <DeliveryNote>() .Join <Supplier>((d, s) => d.SupplierId == s.Id) .Select((d, s) => d.Number + s.Name) .GroupBy((d, s) => d.Number + s.Name) .Join <User>((s, u) => s.CreatedBy == u.Id) .Select((s, u) => new { UserName = u.Name }) .Select((s, u) => DbFunctions.Count(u.Name)) .GroupBy((s, u) => u.Name) .OrderBy((s, u) => u.Name) .Having((s, u) => DbFunctions.Count(u.Name) > 1); var gen = b.GetSqlText(); var spec = "SELECT ([T0].[Number] + [T1].[Name]), [T2].[Name] AS UserName, COUNT([T2].[Name]) FROM [WH].[DeliveryNote] AS [T0] INNER JOIN [WH].[Supplier] AS [T1] ON ([T0].[SupplierId] = [T1].[Id]) INNER JOIN [LoB].[SecurityProfile] AS [T2] ON ([T1].[CreatedBy_Id] = [T2].[Id]) GROUP BY ([T0].[Number] + [T1].[Name]), [T2].[Name] HAVING (COUNT([T2].[Name]) > @SqlParam0) ORDER BY [T2].[Name] ASC"; Assert.Equal(spec, gen); }
public void SelectGroupByHaving() { Configuration.SetDialect(new TestDialect()); SetModeling(); var b = new SelectBuilder(); b.From <DeliveryNote>(); b.Select <DeliveryNote>(dn => dn.SupplierId); b.Select <DeliveryNote>(dn => DbFunctions.Sum(dn.TotalAmount)); b.Select <DeliveryNote>(dn => DbFunctions.Count(dn.TotalAmount)); b.Select <DeliveryNote>(dn => DbFunctions.Min(dn.TotalAmount)); b.Select <DeliveryNote>(dn => DbFunctions.Max(dn.TotalAmount)); b.GroupBy <DeliveryNote>(dn => dn.SupplierId); b.Having <DeliveryNote>(dn => DbFunctions.Sum(dn.TotalAmount) > 10); var gen = b.GetSqlText(); var spec = "SELECT [T0].[SupplierId], SUM([T0].[TotalAmount]), COUNT([T0].[TotalAmount]), MIN([T0].[TotalAmount]), MAX([T0].[TotalAmount]) FROM [WH].[DeliveryNote] AS [T0] GROUP BY [T0].[SupplierId] HAVING (SUM([T0].[TotalAmount]) > @SqlParam0)"; Assert.Equal(spec, gen); Assert.Equal(b.Parameters["SqlParam0"], (decimal)10); }