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