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