Example #1
0
        public void StringLike()
        {
            var r = Sql
                    .From(new SqlTable <Cliente>())
                    .Select(x => new
            {
                rafa = x.Nombre.Contains("Rafa"),
            });

            var clause   = r.Clause;
            var actual   = SqlText.SqlSelect.SelectToStringSP(clause);
            var expected = @"
SELECT 
    (""x"".""Nombre"" LIKE '%' || 'Rafa' || '%') AS ""rafa""
FROM ""Cliente"" ""x""
";

            AssertSql.AreEqual(expected, actual);
        }
Example #2
0
        public void StringConcat()
        {
            var r = Sql
                    .From(new SqlTable <Cliente>())
                    .Select(x => new
            {
                nom = x.Nombre + " " + x.Nombre,
            });

            var clause   = r.Clause;
            var actual   = SqlText.SqlSelect.SelectToStringSP(clause);
            var expected = @"
SELECT 
    ((""x"".""Nombre"" || ' ') || ""x"".""Nombre"") AS ""nom""
FROM ""Cliente"" ""x""
";

            AssertSql.AreEqual(expected, actual);
        }
Example #3
0
        public void FromStar()
        {
            var r = Sql
                    .From <Cliente>()
                    .Select(x => Sql.Star(x).Map(new ClienteDTO
            {
                NombreCompleto = x.Nombre + x.Apellido
            }))
            ;

            var actual   = r.ToString();
            var expected = @"
SELECT 
    ""x"".*,
    (""x"".""Nombre"" || ""x"".""Apellido"") AS ""NombreCompleto""  
FROM ""Cliente"" ""x""
";

            AssertSql.AreEqual(expected, actual);
        }
Example #4
0
        public void SqlStartMultiCol()
        {
            var r = Sql
                    .From(new SqlTable <Cliente>())
                    .Select(x => new
            {
                cli = x,
                edo = x.IdEstado
            });

            var clause = r.Clause;
            var actual = SqlText.SqlSelect.SelectToStringSP(clause);

            var expected = @"
SELECT ""x"".*, ""x"".""IdEstado"" AS ""edo""
FROM ""Cliente"" ""x""
";

            AssertSql.AreEqual(expected, actual);
        }
Example #5
0
        public void SelectReadComplexTypes2()
        {
            var r = Sql
                    .From(new SqlTable <Cliente>())
                    .Select(x => new
            {
                tel = x.Dir
            });

            var clause   = r.Clause;
            var actual   = SqlText.SqlSelect.SelectToStringSP(clause);
            var expected = @"
SELECT 
    ""x"".""Dir_Calle"" AS ""tel_Calle"",
    ""x"".""Dir_Personales_Telefono"" AS ""tel_Personales_Telefono""
FROM ""Cliente"" ""x""
";

            AssertSql.AreEqual(expected, actual);
        }
Example #6
0
        public void JoinLateral()
        {
            var r =
                Sql
                .From <Factura>()
                .Left().Lateral(fac =>
                                Sql.From <ConceptoFactura>()
                                .Select(x => new
            {
                Total = Sql.Sum(x.Precio * x.Cantidad)
            })
                                .Where(con => con.IdFactura == fac.IdRegistro)
                                )
                .On(x => true)
                .Alias(x => new
            {
                fac = x.Item1,
                con = x.Item2
            })
                .Select(x => new
            {
                IdFactura = x.fac.IdRegistro,
                Total     = x.con.Total
            });

            var actual   = SqlText.SqlSelect.SelectToStringSP(r.Clause);
            var expected = @"
SELECT 
    ""fac"".""IdRegistro"" AS ""IdFactura"", 
    ""con"".""Total"" AS ""Total""
FROM ""Factura"" ""fac""
LEFT JOIN LATERAL (
    SELECT 
        sum((""x"".""Precio"" * ""x"".""Cantidad"")) AS ""Total""
    FROM ""ConceptoFactura"" ""x""
    WHERE (""x"".""IdFactura"" = ""fac"".""IdRegistro"")
) ""con"" ON True
";

            AssertSql.AreEqual(expected, actual);
        }
Example #7
0
        public void SimpleSelect()
        {
            var r = Sql
                    .From(new SqlTable <Cliente>())
                    .Select(x => new
            {
                nom = x.Nombre,
                edo = x.IdEstado
            });

            var clause   = r.Clause;
            var actual   = SqlText.SqlSelect.SelectToStringSP(clause);
            var expected = @"
SELECT 
    ""x"".""Nombre"" AS ""nom"", 
    ""x"".""IdEstado"" AS ""edo""
FROM ""Cliente"" ""x""
";

            AssertSql.AreEqual(expected, actual);
        }
Example #8
0
        public void SimpleAliasJoin()
        {
            var r = Sql
                    .From <Cliente>()
                    .Join <Estado>().On(x => x.Item1.IdEstado == x.Item2.IdRegistro)
                    .Alias(y => new
            {
                cli = y.Item1,
                edo = y.Item2
            })
            ;

            var expected = @"
FROM ""Cliente"" ""cli""
JOIN ""Estado"" ""edo"" ON (""cli"".""IdEstado"" = ""edo"".""IdRegistro"")
";

            var actual = SqlFromList.FromListToStrSP(r.Clause.From, "q", false).Sql;

            AssertSql.AreEqual(expected, actual);
        }
Example #9
0
        public void SimpleSelectFuncExpr()
        {
            var min = new DateTime(2019, 01, 26);
            var max = new DateTime(2019, 01, 30);

            var r = Sql
                    .From(new SqlTable <Cliente>())
                    .Select(x => new
            {
                fecha = SqlExpr.Range <DateTime>().Invoke(min, max, x.Fecha)
            });

            var actual   = r.ToSql(SqlText.ParamMode.EntityFramework).Sql;
            var expected = @"
SELECT 
    ((""x"".""Fecha"" >= @min) AND (""x"".""Fecha"" <= @max)) AS ""fecha""
FROM ""Cliente"" ""x""
";

            AssertSql.AreEqual(expected, actual);
        }
Example #10
0
        public void ExistingWindow()
        {
            var r = Sql
                    .From <Cliente>()
                    .Window(win => new
            {
                win1 =
                    win
                    .PartitionBy(x => x.IdEstado)
            })
                    .Window((win, old) => new
            {
                old.win1,
                win2 = old.win1.Rows().CurrentRow().AndUnboundedFollowing()
            })
                    .Select((x, win) => new
            {
                nom = x.Nombre,
                edo = Sql.Over(Sql.Sum(x.IdEstado), win.win2)
            });

            var clause   = r.Clause;
            var actual   = SqlText.SqlSelect.SelectToStringSP(clause);
            var expected = @"
SELECT
    ""x"".""Nombre"" AS ""nom"", 
    sum(""x"".""IdEstado"") OVER ""win2"" AS ""edo""
FROM ""Cliente"" ""x""
WINDOW 
    ""win1"" AS (
        PARTITION BY ""x"".""IdEstado""
    ), 
    ""win2"" AS (
        win1
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    )
";

            AssertSql.AreEqual(expected, actual);
        }
Example #11
0
        public void SubquerySimpleJoin()
        {
            var r =
                Sql.From(
                    Sql
                    .From <Cliente>()
                    .Join <Estado>()
                    .On(x => x.Item1.IdEstado == x.Item2.IdRegistro)
                    .Alias(x => new
            {
                cli = x.Item1,
                edo = x.Item2,
            })
                    .Select(x => new
            {
                cliNomb = x.cli.Nombre,
                edoId   = x.edo.IdRegistro
            })
                    )
                .Select(subQ => new
            {
                idEdo = subQ.edoId,
                cliN  = subQ.cliNomb
            });

            var clause   = r.Clause;
            var actual   = SqlText.SqlSelect.SelectToStringSP(clause);
            var expected = @"
SELECT 
    ""subQ"".""edoId"" AS ""idEdo"",
    ""subQ"".""cliNomb"" AS ""cliN""
FROM (
    SELECT ""cli"".""Nombre"" AS ""cliNomb"", ""edo"".""IdRegistro"" AS ""edoId""
    FROM ""Cliente"" ""cli""
    JOIN ""Estado"" ""edo"" ON (""cli"".""IdEstado"" = ""edo"".""IdRegistro"")    
) ""subQ""
";

            AssertSql.AreEqual(expected, actual);
        }
Example #12
0
        public void SimplePostUnion()
        {
            var q = Sql
                    .From <Cliente>()
                    .Select(x => new
            {
                name = "first"
            })
                    .OrderBy(x => x.Precio)
                    .Limit(1)
                    .Union(
                Sql
                .From <Cliente>()
                .Select(x => new
            {
                name = "second"
            })
                )
            ;
            var actual = q.ToString();

            var expected = @"
(
    SELECT
        'first' AS ""name""
    FROM ""Cliente"" ""x""
    ORDER BY ""x"".""Precio"" ASC
    LIMIT 1
)
UNION
(
    SELECT
        'second' AS ""name""
    FROM ""Cliente"" ""x""
)
";

            AssertSql.AreEqual(expected, actual);
        }
Example #13
0
        public void SimpleSelectIn()
        {
            var nombres = new[]
            {
                "hola",
                "rafa"
            };

            var r = Sql
                    .From(new SqlTable <Cliente>())
                    .Select(x => new
            {
                esRafa = nombres.Contains(x.Nombre)
            });

            var clause   = r.Clause;
            var actual   = SqlText.SqlSelect.SelectToStringSP(clause);
            var expected = @"
SELECT (""x"".""Nombre"" IN ('hola','rafa')) AS ""esRafa"" FROM ""Cliente"" ""x""
";

            AssertSql.AreEqual(expected, actual);
        }
Example #14
0
        public void SimpleJoinLateral()
        {
            var q = Sql.From <Cliente>()
                    .Left().Lateral(c =>
                                    Sql.From <Factura>()
                                    .Select(x => x)
                                    .Where(y => y.IdCliente == c.IdRegistro)

                                    )
                    .On(x => x.Item1.IdRegistro == x.Item2.IdCliente)
                    .Alias(x => new
            {
                cliente = x.Item1,
                factura = x.Item2
            })
                    .Select(w => new
            {
                cliNom = w.cliente.Nombre,
                facFol = w.factura.Folio
            });

            var actual   = SqlText.SqlSelect.SelectToStringSP(q.Clause);
            var expected = @"
SELECT 
    ""cliente"".""Nombre"" AS ""cliNom"", 
    ""factura"".""Folio"" AS ""facFol""
FROM ""Cliente"" ""cliente""
LEFT JOIN LATERAL (
    SELECT
        ""x"".*
    FROM ""Factura"" ""x""
    WHERE (""x"".""IdCliente"" = ""cliente"".""IdRegistro"")
) ""factura"" ON (""cliente"".""IdRegistro"" = ""factura"".""IdCliente"")
";

            AssertSql.AreEqual(expected, actual);
        }
Example #15
0
        public void SimpleSelectMultiWhere()
        {
            var r = Sql
                    .From(new SqlTable <Cliente>())
                    .Select(x => new
            {
                nom = x.Nombre,
                edo = x.IdEstado
            })
                    .Where(x => x.Nombre == "Rafa" && x.IdEstado == 2)
            ;

            var clause   = r.Clause;
            var actual   = SqlText.SqlSelect.SelectToStringSP(clause);
            var expected = @"
SELECT 
    ""x"".""Nombre"" AS ""nom"", 
    ""x"".""IdEstado"" AS ""edo""
FROM ""Cliente"" ""x""
WHERE ((""x"".""Nombre"" = 'Rafa') AND (""x"".""IdEstado"" = 2))
";

            AssertSql.AreEqual(expected, actual);
        }
Example #16
0
        public void SimpleInsertTest()
        {
            Expression <Func <Cliente> > valueExpr = () => new Cliente
            {
                Nombre   = "Rafael",
                Apellido = "Salguero",
            };

            var clause = new InsertClause(
                table: "Cliente",
                value: valueExpr.Body,
                query: null,
                onConflict: null,
                returning: null
                );

            var ret      = SqlInsertConverter.InsertToString(clause, ParamMode.Substitute, new SqlParamDic()).Sql;
            var expected = @"
INSERT INTO ""Cliente"" (""Nombre"", ""Apellido"")
VALUES ('Rafael', 'Salguero')
";

            AssertSql.AreEqual(expected, ret);
        }
Example #17
0
        public void ScalarSubquery()
        {
            var q = Sql
                    .From <Cliente>()
                    .Select(x => new
            {
                idCli = x.IdRegistro,
                fac   = Sql
                        .From <Factura>()
                        .Select(y => y.Folio)
                        .Where(y => y.IdCliente == x.IdRegistro)
                        .Scalar()
            });

            var actual   = SqlText.SqlSelect.SelectToStringSP(q.Clause);
            var expected = @"
SELECT 
    ""x"".""IdRegistro"" AS ""idCli"", 
    (SELECT ""y"".""Folio"" FROM ""Factura"" ""y"" WHERE (""y"".""IdCliente"" = ""x"".""IdRegistro"")) AS ""fac""
FROM ""Cliente"" ""x""
";

            AssertSql.AreEqual(expected, actual);
        }
Example #18
0
        public void WithSimpleAlias()
        {
            var with =
                Sql.With(
                    Sql.From <Cliente>()
                    .Select(x => x)
                    ).With(clie =>
                           Sql
                           .From <Factura>()
                           .Inner().Join(clie).On(y => y.Item1.IdCliente == y.Item2.IdRegistro)
                           .Select(z => new
            {
                z.Item1.IdCliente,
                z.Item2.Nombre,
            })
                           ).Map((a, b) => new
            {
                cliente  = a,
                facturas = b
            })
                .With(w => Sql
                      .From <ConceptoFactura>()
                      .Inner().Join(w.facturas).On(x => true)
                      .Select(x => x.Item1)
                      )
                .Map((a, b) => new
            {
                cli = a.cliente,
                fac = a.facturas,
                con = b
            }).Query(w =>

                     Sql.From(w.cli)
                     .Select(x => x)
                     );

            var ret      = SqlWith.WithToSql(with.With.With, with.With.Param, ParamMode.EntityFramework, new SqlParamDic());
            var expected = @"
WITH ""cli"" AS (
    SELECT 
        ""x"".*
    FROM ""Cliente"" ""x""
)
, 
""fac"" AS (
    SELECT 
        ""Item1"".""IdCliente"" AS ""IdCliente"", 
        ""Item2"".""Nombre"" AS ""Nombre""
    FROM ""Factura"" ""Item1""
    JOIN ""cli"" ""Item2"" ON (""Item1"".""IdCliente"" = ""Item2"".""IdRegistro"")
)
, 
""con"" AS (
    SELECT 
        ""Item1"".*
    FROM ""ConceptoFactura"" ""Item1""
    JOIN ""fac"" ""Item2"" ON True
)
";

            AssertSql.AreEqual(expected, ret);

            var selectActual   = with.ToSql().Sql;
            var selectExpected = @"
WITH ""cli"" AS (
    SELECT 
        ""x"".*
    FROM ""Cliente"" ""x""
), ""fac"" AS (
    SELECT 
        ""Item1"".""IdCliente"" AS ""IdCliente"", 
        ""Item2"".""Nombre"" AS ""Nombre""
    FROM ""Factura"" ""Item1""
    JOIN ""cli"" ""Item2"" ON (""Item1"".""IdCliente"" = ""Item2"".""IdRegistro"")
), ""con"" AS (
    SELECT 
        ""Item1"".*
    FROM ""ConceptoFactura"" ""Item1""
    JOIN ""fac"" ""Item2"" ON True
)

SELECT 
    ""x"".*
FROM ""cli"" ""x""
";

            AssertSql.AreEqual(selectExpected, selectActual);
        }
Example #19
0
        public void WithSyntax()
        {
            var with = Sql.With(
                Sql.From <Cliente>()
                .Select(x => x)
                ).With(cli =>
                       Sql
                       .From <Factura>()
                       .Inner().Join(cli).On(y => y.Item1.IdCliente == y.Item2.IdRegistro)
                       .Select(z => new
            {
                z.Item1.IdCliente,
                z.Item2.Nombre,
            })
                       ).Map((a, b) => new
            {
                cliente  = a,
                facturas = b
            })
                       .WithRecursive(c =>
                                      Sql
                                      .From <ConceptoFactura>()
                                      .Inner().Join(c.facturas).On(d => d.Item1.IdFactura == d.Item2.IdCliente)
                                      .Select(e => e.Item1)
                                      ).UnionAll((w, conceptos) =>
                                                 Sql.From(conceptos)
                                                 .Select(f => f)
                                                 ).Map((g, h) => new
            {
                cli  = g.cliente,
                fact = g.facturas,
                conc = h
            })
                       .Query(w =>
                              Sql.From(w.conc).Select(x => x)
                              );

            var actual   = with.ToSql().Sql;
            var expected = @"
WITH RECURSIVE ""cli"" AS (
    SELECT 
        ""x"".*
    FROM ""Cliente"" ""x""
)
, ""fact"" AS (
    SELECT 
        ""Item1"".""IdCliente"" AS ""IdCliente"", 
        ""Item2"".""Nombre"" AS ""Nombre""
    FROM ""Factura"" ""Item1""
    JOIN ""cli"" ""Item2"" ON (""Item1"".""IdCliente"" = ""Item2"".""IdRegistro"")
)
, ""conc"" AS (
    SELECT 
        ""Item1"".*
    FROM ""ConceptoFactura"" ""Item1""
    JOIN ""fact"" ""Item2"" ON (""Item1"".""IdFactura"" = ""Item2"".""IdCliente"")

    UNION ALL

    SELECT 
        ""f"".*
    FROM ""conc"" ""f""
)
SELECT 
    ""x"".*
FROM ""conc"" ""x""
";

            AssertSql.AreEqual(expected, actual);
        }