Exemplo n.º 1
0
        public void SelectFromTableWithAlias()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <DeliveryNote>("T100");

            var gen  = b.GetSqlText();
            var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T100]";

            Assert.Equal(spec, gen);
        }
Exemplo n.º 2
0
        public void SelectColumnsWithSubString()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <DeliveryNote>();
            b.Select <DeliveryNote>(i => i.Number.Substring(0, 3));

            var gen  = b.GetSqlText();
            var spec = "SELECT SUBSTRING([T0].[Number],(@SqlParam0) + 1,@SqlParam1) FROM [WH].[DeliveryNote] AS [T0]";

            Assert.Equal(spec, gen);
        }
Exemplo n.º 3
0
        public void SelectSelfTableJoin()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <User>("T11");
            b.Join <User, User>("T22", (c1, c2) => c1.CreatedBy == c2.Id);

            var gen  = b.GetSqlText();
            var spec = "SELECT * FROM [LoB].[SecurityProfile] AS [T11] INNER JOIN [LoB].[SecurityProfile] AS [T22] ON ([T11].[CreatedBy_Id] = [T22].[Id])";

            Assert.Equal(spec, gen);
        }
Exemplo n.º 4
0
        public void SelectJoinTwoTablesWithAliases()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <DeliveryNote>("T11");
            b.Join <DeliveryNote, DeliveryNoteDetail>("T22", (i, id) => i.Id == id.DeliveryNoteId || i.TotalAmount >= id.UnitPrice);

            var gen  = b.GetSqlText();
            var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T11] INNER JOIN [WH].[DeliveryNoteDetail] AS [T22] ON (([T11].[Id] = [T22].[DeliveryNote_Id]) OR ([T11].[TotalAmount] >= [T22].[UnitPrice]))";

            Assert.Equal(spec, gen);
        }
Exemplo n.º 5
0
        public void SelectLeftJoinTwoTables()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <DeliveryNote>();
            b.Join <DeliveryNote, DeliveryNoteDetail>(JoinType.Left, (i, id) => i.Id == id.DeliveryNoteId);

            var gen  = b.GetSqlText();
            var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] LEFT JOIN [WH].[DeliveryNoteDetail] AS [T1] ON ([T0].[Id] = [T1].[DeliveryNote_Id])";

            Assert.Equal(spec, gen);
        }
Exemplo n.º 6
0
        public void SelectColumnsFromTableWithAlias()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <DeliveryNote>("T100");
            b.Select <DeliveryNote>(i => i.Number, i => i.Date);

            var gen  = b.GetSqlText();
            var spec = "SELECT [T100].[Number], [T100].[Date] FROM [WH].[DeliveryNote] AS [T100]";

            Assert.Equal(spec, gen);
        }
Exemplo n.º 7
0
        public void SelectWithWhereWithEndsWith()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <Supplier>();
            b.Where <Supplier>(i => i.Name.EndsWith("Javier"));

            var gen  = b.GetSqlText();
            var spec = "SELECT * FROM [WH].[Supplier] AS [T0] WHERE [T0].[Name] LIKE '%' + @SqlParam0";

            Assert.Equal(spec, gen);
            Assert.Equal(b.Parameters["SqlParam0"], "Javier");
        }
Exemplo n.º 8
0
        public void SelectWithOrderBy()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <DeliveryNote>();
            b.OrderBy <DeliveryNote>(OrderByDirection.Desc, i => i.Date);
            b.OrderBy <DeliveryNote>(i => i.Year, i => i.SupplierId);

            var gen  = b.GetSqlText();
            var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] ORDER BY [T0].[Date] DESC, [T0].[Year] ASC, [T0].[SupplierId] ASC";

            Assert.Equal(spec, gen);
        }
Exemplo n.º 9
0
        public void CanSetModelingByBuilder()
        {
            Modeling.ResetModeling();
            Modeling.DefaultSchema = "KKK";
            Modeling.Model <Supplier>().SetSchema("CUS").SetTable("CUSTOMER");

            var b = new SelectBuilder();

            b.Modeling.Model <Supplier>().SetSchema("dbo").SetTable("MyTable");
            b.From <Supplier>();

            var gen  = b.GetSqlText();
            var spec = "SELECT * FROM [dbo].[MyTable] AS [T0]";

            Assert.Equal(spec, gen);
        }
Exemplo n.º 10
0
        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);
        }
Exemplo n.º 11
0
        public void SelectJoinTablesWithTripleType()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <DeliveryNote>();
            b.Join <DeliveryNote, DeliveryNoteDetail>((i, id) => i.Id == id.DeliveryNoteId);
            b.Join <DeliveryNote, DeliveryNoteDetail, User>(
                (dn, dnd, u) => dn.CreatedBy == u.Id && dnd.CreatedBy == u.Id);

            var gen  = b.GetSqlText();
            var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] INNER JOIN [WH].[DeliveryNoteDetail] AS [T1] ON ([T0].[Id] = [T1].[DeliveryNote_Id]) INNER JOIN [LoB].[SecurityProfile] AS [T2] ON (([T0].[CreatedBy] = [T2].[Id]) AND ([T1].[CreatedBy] = [T2].[Id]))";

            Assert.Equal(spec, gen);
        }
Exemplo n.º 12
0
        public void SelectWithWhere()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <DeliveryNote>();
            b.Where <DeliveryNote>(i => i.Number == null && i.Year == "B" && i.Id > 2 && i.TotalAmount == 0);

            var gen  = b.GetSqlText();
            var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] WHERE (((([T0].[Number] IS NULL) AND ([T0].[Year] = @SqlParam0)) AND ([T0].[Id] > @SqlParam1)) AND ([T0].[TotalAmount] = @SqlParam2))";

            Assert.Equal(spec, gen);
            Assert.Equal(b.Parameters["SqlParam0"], "B");
            Assert.Equal(b.Parameters["SqlParam1"], Convert.ToInt64(2));
            Assert.Equal(b.Parameters["SqlParam2"], (Decimal)0);
        }
Exemplo n.º 13
0
        public void SelectWithWhereWithLikeAndSubString()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <Supplier>();
            b.Where <Supplier>(i => i.Name.Substring(0).Contains("Javier"));

            var gen  = b.GetSqlText();
            var spec = "SELECT * FROM [WH].[Supplier] AS [T0] WHERE SUBSTRING([T0].[Name],(@SqlParam0) + 1,@SqlParam1) LIKE '%' + @SqlParam2 + '%'";

            Assert.Equal(spec, gen);
            Assert.Equal(b.Parameters["SqlParam0"], 0);
            Assert.Equal(b.Parameters["SqlParam1"], int.MaxValue);
            Assert.Equal(b.Parameters["SqlParam2"], "Javier");
        }
Exemplo n.º 14
0
        public void SelectWithWhereWithInvertedLike()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <Supplier>();
            var name = "Javier Ros Moreno";

            b.Where <Supplier>(i => name.Contains(i.Name));

            var gen  = b.GetSqlText();
            var spec = "SELECT * FROM [WH].[Supplier] AS [T0] WHERE @SqlParam0 LIKE '%' + [T0].[Name] + '%'";

            Assert.Equal(spec, gen);
            Assert.Equal(b.Parameters["SqlParam0"], "Javier Ros Moreno");
        }
Exemplo n.º 15
0
        public void SelectWithWhereWithNullScopeConstants()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <DeliveryNote>("T0");
            string  year   = null;
            long?   id     = null;
            decimal?total  = null;
            string  number = null;

            b.Where <DeliveryNote>(i => i.Number == number && i.Year == year && i.Id == id && i.TotalAmount == total);

            var gen  = b.GetSqlText();
            var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] WHERE (((([T0].[Number] IS NULL) AND ([T0].[Year] IS NULL)) AND ([T0].[Id] IS NULL)) AND ([T0].[TotalAmount] IS NULL))";

            Assert.Equal(spec, gen);
            Assert.Equal(b.Parameters.Count, 0);
        }
Exemplo n.º 16
0
        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);
        }
Exemplo n.º 17
0
        public void SelectProjection()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <DeliveryNote>();
            b.Join <DeliveryNote, Supplier>((dn, s) => dn.SupplierId == s.Id);
            b.Select <DeliveryNote, Supplier>((dn, s) => new DeliveryNoteDto
            {
                Number       = dn.Number,
                TotalAmount  = dn.TotalAmount,
                Date         = dn.Date,
                SupplierName = s.Name
            });

            var gen  = b.GetSqlText();
            var spec = "SELECT [T0].[Number] AS Number, [T0].[TotalAmount] AS TotalAmount, [T0].[Date] AS Date, [T1].[Name] AS SupplierName FROM [WH].[DeliveryNote] AS [T0] INNER JOIN [WH].[Supplier] AS [T1] ON ([T0].[SupplierId] = [T1].[Id])";

            Assert.Equal(spec, gen);
        }
Exemplo n.º 18
0
        public void SelectWithWhereWithScopeConstants()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <DeliveryNote>();
            var    year   = "B";
            var    id     = 2;
            var    total  = 0;
            string number = "23";

            b.Where <DeliveryNote>(i => i.Number == number && i.Year == year && i.Id > id && i.TotalAmount == total);

            var gen  = b.GetSqlText();
            var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] WHERE (((([T0].[Number] = @SqlParam0) AND ([T0].[Year] = @SqlParam1)) AND ([T0].[Id] > @SqlParam2)) AND ([T0].[TotalAmount] = @SqlParam3))";

            Assert.Equal(spec, gen);
            Assert.Equal(b.Parameters["SqlParam0"], "23");
            Assert.Equal(b.Parameters["SqlParam1"], "B");
            Assert.Equal(b.Parameters["SqlParam2"], Convert.ToInt32(2));
            Assert.Equal(b.Parameters["SqlParam3"], (Int32)0);
        }
Exemplo n.º 19
0
        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");
        }
Exemplo n.º 20
0
        public void SelectColumnsJoinsAndWhere()
        {
            Configuration.SetDialect(new TestDialect());
            SetModeling();
            var b = new SelectBuilder();

            b.From <DeliveryNote>();
            b.Join <DeliveryNote, DeliveryNoteDetail>((i, id) => i.Id == id.DeliveryNoteId);
            b.Join <DeliveryNote, Supplier>((i, c) => i.SupplierId == c.Id);
            b.Select <DeliveryNote, Supplier>((i, c) => i.Number, (i, c) => c.Name, (i, c) => i.Date, (i, c) => i.TotalAmount);
            b.Select <DeliveryNoteDetail>((id) => id.Size);
            b.Where <Supplier>(c => c.IsDeleted);
            b.Where <DeliveryNote, DeliveryNoteDetail>((i, id) => i.Year == "B" && id.IsDeleted == false);
            b.Where <DeliveryNote, DeliveryNoteDetail>(WhereOperator.Or, (i, id) => i.Year == "Z");

            var gen  = b.GetSqlText();
            var spec = "SELECT [T0].[Number], [T2].[Name], [T0].[Date], [T0].[TotalAmount], [T1].[Size] FROM [WH].[DeliveryNote] AS [T0] INNER JOIN [WH].[DeliveryNoteDetail] AS [T1] ON ([T0].[Id] = [T1].[DeliveryNote_Id]) INNER JOIN [WH].[Supplier] AS [T2] ON ([T0].[SupplierId] = [T2].[Id]) WHERE ([T2].[IsDeleted] = @SqlParam0) AND (([T0].[Year] = @SqlParam1) AND ([T1].[IsDeleted] = @SqlParam2)) OR ([T0].[Year] = @SqlParam3)";

            Assert.Equal(spec, gen);
            Assert.Equal(b.Parameters["SqlParam0"], true);
            Assert.Equal(b.Parameters["SqlParam1"], "B");
            Assert.Equal(b.Parameters["SqlParam2"], false);
            Assert.Equal(b.Parameters["SqlParam3"], "Z");
        }