public void SelectWithSubQuery() { Configuration.SetDialect(new TestDialect()); SetModeling(); var b = new SelectBuilder(); var f = false; var subQuery = new SelectBuilder().From <Supplier>().Where <Supplier>(c => c.IsDeleted == f).Select <Supplier>(c => c.Id); b.From <DeliveryNote>(); b.Where <DeliveryNote>(i => subQuery.Contains(i.SupplierId) && i.Number == "hola"); var gen = b.GetSqlText(); var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] WHERE ([T0].[SupplierId] IN (SELECT [T0].[Id] FROM [WH].[Supplier] AS [T0] WHERE ([T0].[IsDeleted] = @SqlParam0)) AND ([T0].[Number] = @SqlParam1))"; Assert.Equal(spec, gen); }
public void SelectWithMultipleSubQueryAndParameters() { Configuration.SetDialect(new TestDialect()); SetModeling(); var b = new SelectBuilder(); var const1 = false; var const2 = false; var subQuery1 = new SelectBuilder().From <Supplier>().Where <Supplier>(c => c.IsDeleted == const1).Select <Supplier>(c => c.Id); var subQuery2 = new SelectBuilder().From <Supplier>().Where <Supplier>(c => c.IsDeleted == const2).Where <Supplier>(c => subQuery1.Contains(c.Id)).Select <Supplier>(c => c.Id); b.From <DeliveryNote>(); b.Where <DeliveryNote>(i => subQuery1.Contains(i.SupplierId) && i.Number == "hola1"); b.Where <DeliveryNote>(i => subQuery2.Contains(i.SupplierId) && i.Number == "hola2"); var gen = b.GetSqlText(); var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] WHERE ([T0].[SupplierId] IN (SELECT [T0].[Id] FROM [WH].[Supplier] AS [T0] WHERE ([T0].[IsDeleted] = @SqlParam0)) AND ([T0].[Number] = @SqlParam1)) AND ([T0].[SupplierId] IN (SELECT [T0].[Id] FROM [WH].[Supplier] AS [T0] WHERE ([T0].[IsDeleted] = @SqlParam2) AND [T0].[Id] IN (SELECT [T0].[Id] FROM [WH].[Supplier] AS [T0] WHERE ([T0].[IsDeleted] = @SqlParam3))) AND ([T0].[Number] = @SqlParam4))"; Assert.Equal(spec, gen); Assert.Equal(b.Parameters["SqlParam0"], const1); Assert.Equal(b.Parameters["SqlParam1"], "hola1"); Assert.Equal(b.Parameters["SqlParam2"], const2); Assert.Equal(b.Parameters["SqlParam3"], const1); Assert.Equal(b.Parameters["SqlParam4"], "hola2"); }