public void NullParamTest() { Cliente filtro = null; var r = Sql .From(new SqlTable <Cliente>()) .Select(x => new { nom = x.Nombre, edo = x.IdEstado }) .Where(x => x.Fecha == filtro.Fecha) ; var actual = r.ToSql().Sql; var expected = @" SELECT ""x"".""Nombre"" AS ""nom"", ""x"".""IdEstado"" AS ""edo"" FROM ""Cliente"" ""x"" WHERE (""x"".""Fecha"" = @Fecha) "; AssertSql.AreEqual(expected, actual); }
public void NameCollisionJoin() { var r = Sql .From <Cliente>() .Join <Estado>().On(x => x.Item1.IdEstado == x.Item2.IdRegistro) .Join <Factura>().On(x => x.Item2.IdRegistro == x.Item3.IdRegistro) .Join <ConceptoFactura>().On(x => x.Item3.IdCliente == x.Item4.IdFactura) .Alias(x => new { a2 = x.Item1, a1 = x.Item2, a = x.Item3, b = x.Item4 }) ; var expected = @" FROM ""Cliente"" ""a2"" JOIN ""Estado"" ""a1"" ON (""a2"".""IdEstado"" = ""a1"".""IdRegistro"") JOIN ""Factura"" ""a"" ON (""a1"".""IdRegistro"" = ""a"".""IdRegistro"") JOIN ""ConceptoFactura"" ""b"" ON (""a"".""IdCliente"" = ""b"".""IdFactura"") "; var actual = SqlFromList.FromListToStrSP(r.Clause.From, "q", false).Sql; AssertSql.AreEqual(expected, actual); }
public void SqlMultiStar() { var r = 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 { cli = x.cli, edo = x.edo, nom = x.cli.Nombre, idEdo = x.edo.IdRegistro }); var clause = r.Clause; var actual = SqlText.SqlSelect.SelectToStringSP(clause); var expected = @" SELECT ""cli"".*, ""edo"".*, ""cli"".""Nombre"" AS ""nom"", ""edo"".""IdRegistro"" AS ""idEdo"" FROM ""Cliente"" ""cli"" JOIN ""Estado"" ""edo"" ON (""cli"".""IdEstado"" = ""edo"".""IdRegistro"") "; AssertSql.AreEqual(expected, actual); }
public void EliminacionBooleanaParam() { int?idRegistro = null; int?idEstado = 123; var r = Sql .From(new SqlTable <Cliente>()) .Select(x => new { nom = x.Nombre, edo = x.IdEstado }) .Where(x => SqlExpr.EqualsNullable.Invoke(x.IdRegistro, idRegistro) && SqlExpr.EqualsNullable.Invoke(x.IdEstado, idEstado) ) ; var actual = r.ToSql(SqlText.ParamMode.Substitute).Sql; var expected = @" SELECT ""x"".""Nombre"" AS ""nom"", ""x"".""IdEstado"" AS ""edo"" FROM ""Cliente"" ""x"" WHERE (""x"".""IdEstado"" = 123) "; AssertSql.AreEqual(expected, actual); }
public void ExprInvokeSelect() { Expression <Func <int, bool> > es10 = x => x == 10; var r = Sql .From(new SqlTable <Cliente>()) .Select(x => new { nom = x.Nombre, edo = x.IdEstado }) .Where(x => es10.Invoke(x.IdRegistro)) ; 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"".""IdRegistro"" = 10) "; AssertSql.AreEqual(expected, actual); }
public void MultiJoin() { var r = Sql .From <Cliente>() .Join <Estado>().On(x => x.Item1.IdEstado == x.Item2.IdRegistro) .Join <Factura>().On(x => x.Item1.IdRegistro == x.Item3.IdCliente) .Join <ConceptoFactura>().On(x => x.Item4.IdFactura == x.Item3.IdRegistro) .Alias(x => new { clien = x.Item1, estado = x.Item2, fact = x.Item3, concepto = x.Item4 }) ; var actual = SqlFromList.FromListToStrSP(r.Clause.From, "q", false).Sql; var expected = @" FROM ""Cliente"" ""clien"" JOIN ""Estado"" ""estado"" ON (""clien"".""IdEstado"" = ""estado"".""IdRegistro"") JOIN ""Factura"" ""fact"" ON (""clien"".""IdRegistro"" = ""fact"".""IdCliente"") JOIN ""ConceptoFactura"" ""concepto"" ON (""concepto"".""IdFactura"" = ""fact"".""IdRegistro"") "; AssertSql.AreEqual(expected, actual); }
public void SubqueryStarSimple() { var r = Sql.From( Sql .From(new SqlTable <Cliente>()) .Select(x => x) ) .Select(y => new { edo = y.IdEstado, nom = y.Nombre }); var clause = r.Clause; var actual = SqlText.SqlSelect.SelectToStringSP(clause); var expected = @" SELECT ""y"".""IdEstado"" AS ""edo"", ""y"".""Nombre"" AS ""nom"" FROM ( SELECT ""x"".* FROM ""Cliente"" ""x"" ) ""y"" "; AssertSql.AreEqual(expected, actual); }
public void NamedJoinLateral() { var q = Sql .From <Cliente>() .Left().Join <Factura>().On(x => x.Item1.IdRegistro == x.Item2.IdCliente) .Left().Lateral(y => Sql.From <ConceptoFactura>() .Select(z => z) .Where(w => w.IdFactura == y.Item1.IdRegistro) ) .On(x => true) .Alias(x => new { clien = x.Item1, factu = x.Item2, conce = x.Item3 }) .Select(r => r) ; var actual = SqlText.SqlSelect.SelectToStringSP(q.Clause); var expected = @" SELECT * FROM ""Cliente"" ""clien"" LEFT JOIN ""Factura"" ""factu"" ON (""clien"".""IdRegistro"" = ""factu"".""IdCliente"") LEFT JOIN LATERAL ( SELECT ""z"".* FROM ""ConceptoFactura"" ""z"" WHERE (""z"".""IdFactura"" = ""clien"".""IdRegistro"") ) ""conce"" ON True "; AssertSql.AreEqual(expected, actual); }
public void SubqueryNamedFromStarSimple() { var r = Sql.From( Sql .From(new SqlTable <Cliente>()) .Left().Join(new SqlTable <Factura>()) .On(x => x.Item1.IdRegistro == x.Item2.IdCliente) .Select(x => x) ) .Select(y => new { edo = y.Item1.IdEstado, nom = y.Item2.Folio }); var clause = r.Clause; var actual = SqlText.SqlSelect.SelectToStringSP(clause); var expected = @" SELECT ""y"".""IdEstado"" AS ""edo"", ""y"".""Folio"" AS ""nom"" FROM ( SELECT * FROM ""Cliente"" ""Item1"" LEFT JOIN ""Factura"" ""Item2"" ON (""Item1"".""IdRegistro"" = ""Item2"".""IdCliente"") ) ""y"" "; AssertSql.AreEqual(expected, actual); }
public void SimpleGroupBy() { var r = Sql .From(new SqlTable <Cliente>()) .Select(x => new { nom = x.Nombre, edo = x.IdEstado }) .GroupBy(x => x.IdEstado).ThenBy(x => x.Nombre) ; var clause = r.Clause; var actual = SqlText.SqlSelect.SelectToStringSP(clause); var expected = @" SELECT ""x"".""Nombre"" AS ""nom"", ""x"".""IdEstado"" AS ""edo"" FROM ""Cliente"" ""x"" GROUP BY ""x"".""IdEstado"", ""x"".""Nombre"" "; AssertSql.AreEqual(expected, actual); }
public void WindowOver() { var r = Sql .From <Cliente>() .Window(win => new { w1 = win.Rows().UnboundedPreceding().AndCurrentRow() }) .Select((x, win) => new { nom = x.Nombre, ids = Sql.Over(Sql.Sum(x.Nombre), win.w1) }); var actual = SqlText.SqlSelect.SelectToStringSP(r.Clause); var expected = @" SELECT ""x"".""Nombre"" AS ""nom"", sum(""x"".""Nombre"") OVER ""w1"" AS ""ids"" FROM ""Cliente"" ""x"" WINDOW ""w1"" AS ( ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) "; AssertSql.AreEqual(expected, actual); }
public void QueryInsertTest() { var query = Sql.From <Cliente>().Select(x => new Cliente { Nombre = "Hola", Apellido = x.Apellido, Dir = new Direccion { Calle = x.Dir.Calle } }); var clause = new InsertClause( table: "Cliente", value: null, query: query.Clause, onConflict: null, returning: null ); var ret = SqlInsertConverter.InsertToString(clause, ParamMode.Substitute, new SqlParamDic()).Sql; var expected = @" INSERT INTO ""Cliente"" (Nombre, Apellido, Dir_Calle) SELECT 'Hola' AS ""Nombre"", ""x"".""Apellido"" AS ""Apellido"", ""x"".""Dir_Calle"" AS ""Dir_Calle"" FROM ""Cliente"" ""x"" "; AssertSql.AreEqual(expected, ret); }
public void SimpleSelectOrderByDesc() { var r = Sql .From(new SqlTable <Cliente>()) .Select(x => new { nom = x.Nombre, edo = x.IdEstado }) .OrderBy(x => x.Nombre, OrderByOrder.Desc) ; var clause = r.Clause; var actual = SqlText.SqlSelect.SelectToStringSP(clause); var expected = @" SELECT ""x"".""Nombre"" AS ""nom"", ""x"".""IdEstado"" AS ""edo"" FROM ""Cliente"" ""x"" ORDER BY ""x"".""Nombre"" DESC "; AssertSql.AreEqual(expected, actual); }
public void SimpleReturning() { Expression <Func <Cliente> > valueExpr = () => new Cliente { Nombre = "Rafael", Apellido = "Salguero", }; Expression <Func <Cliente, ReturningTestType> > returningExpr = x => new ReturningTestType { id = x.IdRegistro }; var clause = new InsertClause( table: "Cliente", value: valueExpr.Body, query: null, onConflict: null, returning: returningExpr ); var ret = SqlInsertConverter.InsertToString(clause, ParamMode.Substitute, new SqlParamDic()).Sql; var expected = @" INSERT INTO ""Cliente"" (""Nombre"", ""Apellido"") VALUES ('Rafael', 'Salguero') RETURNING ""Cliente"".""IdRegistro"" AS ""id"" "; AssertSql.AreEqual(expected, ret); }
public void ComplexTypeInsertTest() { Expression <Func <Cliente> > valueExpr = () => new Cliente { Nombre = "Rafael", Apellido = "Salguero", Dir = new Direccion { Calle = "E Baca Calderon", Personales = new DatosPersonales { Telefono = "4123" } } }; 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"", ""Dir_Calle"", ""Dir_Personales_Telefono"") VALUES ('Rafael', 'Salguero', 'E Baca Calderon', '4123') "; AssertSql.AreEqual(expected, ret); }
public void ParamFunc() { var query = SelectNomina(new FiltroNomina { Id = 20 }); var q2 = Sql .From(query) .Select(x => new { nom = x.Nombre }) ; var sql = q2.ToSql(); Assert.AreEqual(sql.Params[0].Name, "Id"); Assert.AreEqual(sql.Params[0].Value, 20); var actual = sql.Sql; var expected = @" SELECT ""x"".""Nombre"" AS ""nom"" FROM ( SELECT ""x"".* FROM ""Cliente"" ""x"" WHERE (""x"".""IdRegistro"" = @Id) ) ""x"" "; AssertSql.AreEqual(expected, actual); }
public void SimpleAliasJoinSelect() { var r = Sql .From <Cliente>() .Join <Estado>().On(x => x.Item1.IdEstado == x.Item2.IdRegistro) .Join <Factura>().On(x => x.Item1.IdRegistro == x.Item3.IdCliente) .Alias(x => new { cli = x.Item1, edo = x.Item2, fac = x.Item3 }) .Select(x => new { idCli = x.cli.IdRegistro, idEdo = x.edo.IdRegistro }) ; var expected = @" SELECT ""cli"".""IdRegistro"" AS ""idCli"", ""edo"".""IdRegistro"" AS ""idEdo"" FROM ""Cliente"" ""cli"" JOIN ""Estado"" ""edo"" ON (""cli"".""IdEstado"" = ""edo"".""IdRegistro"") JOIN ""Factura"" ""fac"" ON (""cli"".""IdRegistro"" = ""fac"".""IdCliente"") "; var actual = SqlSelect.SelectToStringSP(r.Clause); AssertSql.AreEqual(expected, actual); }
public void ExpressionTest() { var obtenerDto = ExprFunc(fac => new Factura { IdCliente = 10 });; var r = Sql .From <Factura>() .Inner().Join <Cliente>() .On(x => x.Item1.IdCliente == x.Item2.IdRegistro) .Alias(x => new { fac = x.Item1, cli = x.Item2 }) .Select(from => obtenerDto.Invoke(from.fac) ); var actual = r.ToSql().Sql; var expected = @" SELECT 42 AS ""Folio"", 'Rafa' AS ""Serie"" FROM ""Factura"" ""fac"" JOIN ""Cliente"" ""cli"" ON (""fac"".""IdCliente"" = ""cli"".""IdRegistro"") "; AssertSql.AreEqual(expected, actual); }
public void ParamClass() { var pars = new ParB { ParA = new ParA { Param = 20 } }; var q = Sql.From <Cliente>() .Select(x => x) .Where(x => x.IdRegistro == pars.ParA.Param) .ToSql(); var expected = @" SELECT ""x"".* FROM ""Cliente"" ""x"" WHERE (""x"".""IdRegistro"" = @Param) "; AssertSql.AreEqual(expected, q.Sql); Assert.AreEqual(q.Params[0].Name, "Param"); Assert.AreEqual(q.Params[0].Value, 20); }
public void SqlSelectComplexType() { ISqlSelect query = Sql.From <Cliente>().Select(x => new Cliente { Nombre = "Hola", Apellido = x.Apellido, Dir = new Direccion { Calle = x.Dir.Calle, Personales = new DatosPersonales { Telefono = "1234" } } }); var r = query.ToSql().Sql; var expected = @" SELECT 'Hola' AS ""Nombre"", ""x"".""Apellido"" AS ""Apellido"", ""x"".""Dir_Calle"" AS ""Dir_Calle"", '1234' AS ""Dir_Personales_Telefono"" FROM ""Cliente"" ""x"" "; AssertSql.AreEqual(expected, r); }
public void SubqueryIn() { var q = Sql .From <Cliente>() .Select(x => x) .Where(cli => Sql.In( 1, Sql .From <Factura>() .Select(fac => fac.IdRegistro) .Where(fac => fac.IdCliente == cli.IdRegistro) ) ); var actual = q.ToSql().Sql; var expected = @" SELECT ""x"".* FROM ""Cliente"" ""x"" WHERE (1 IN ( SELECT ""fac"".""IdRegistro"" FROM ""Factura"" ""fac"" WHERE (""fac"".""IdCliente"" = ""x"".""IdRegistro"") )) "; AssertSql.AreEqual(expected, actual); }
public void EmptyWindow() { var r = Sql .From <Cliente>() .Window(win => new { win1 = win .Rows() .UnboundedPreceding() .AndCurrentRow() .ExcludeNoOthers(), }) .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"" WINDOW ""win1"" AS ( ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS ) "; AssertSql.AreEqual(expected, actual); }
public void JoinStar() { var r = Sql .From <Cliente>() .Join <Factura>() .On(x => x.Item2.IdCliente == x.Item1.IdRegistro) .Alias(x => new { cli = x.Item1, fac = x.Item2 }) .Select(x => Sql.Star(x.cli, x.fac).Map(new ClienteDTO { NombreCompleto = x.cli.Nombre + x.cli.Apellido })) ; var actual = r.ToString(); var expected = @" SELECT ""cli"".*, ""fac"".*, (""cli"".""Nombre"" || ""cli"".""Apellido"") AS ""NombreCompleto"" FROM ""Cliente"" ""cli"" JOIN ""Factura"" ""fac"" ON (""fac"".""IdCliente"" = ""cli"".""IdRegistro"") "; AssertSql.AreEqual(expected, actual); }
public void JoinLateralSubqueryFunction() { var q = Sql.From <Cliente>() .Left().Lateral(c => JoinLateralSubqueryFunction_QueryFacturas(() => 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 JoinLateralSubqueryExpressionNeasted() { Expression <Func <int, ISqlSelect <Factura> > > queryFacturas = idCliente => Sql.From <Factura>() .Select(x => x) .Where(y => y.IdCliente == idCliente); Expression <Func <int, ISqlSelect <Factura> > > queryFacturas2 = idCliente => Sql.From(queryFacturas.Invoke(idCliente)) .Select(x => x) .Where(y => y.IdCliente == idCliente); Expression <Func <int, ISqlSelect <Factura> > > subqueryExpr = idCliente => Sql.From(queryFacturas2.Invoke(idCliente)) .Select(x => x) .Where(y => y.IdCliente == idCliente); var q = Sql.From <Cliente>() .Left().Lateral(c => subqueryExpr.Invoke(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 ( SELECT ""x"".* FROM ( SELECT ""x"".* FROM ""Factura"" ""x"" WHERE (""x"".""IdCliente"" = ""cliente"".""IdRegistro"") ) ""x"" WHERE (""x"".""IdCliente"" = ""cliente"".""IdRegistro"") ) ""x"" WHERE (""x"".""IdCliente"" = ""cliente"".""IdRegistro"") ) ""factura"" ON (""cliente"".""IdRegistro"" = ""factura"".""IdCliente"") "; AssertSql.AreEqual(expected, actual); }
public void SimpleWhereSinSelectInterface() { var r = QueryCliente <Cliente>(); var actual = r.ToSql().Sql; var expected = @" SELECT ""x"".* FROM ""Cliente"" ""x"" WHERE (""x"".""Nombre"" = 'Rafa') "; AssertSql.AreEqual(expected, actual); }
public void SubquerySimpleJoinOutterJoin() { 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 { cliId = x.cli.IdRegistro, edoId = x.edo.IdRegistro }) ) .Join <Factura>() .On(x => x.Item2.IdCliente == x.Item1.cliId) .Alias(x => new { sq = x.Item1, fac = x.Item2, }) .Select(subQ => new { idEdo = subQ.sq.edoId, cliN = subQ.sq.cliId, fac = subQ.fac.Folio }); var clause = r.Clause; var actual = SqlText.SqlSelect.SelectToStringSP(clause); var expected = @" SELECT ""sq"".""edoId"" AS ""idEdo"", ""sq"".""cliId"" AS ""cliN"", ""fac"".""Folio"" AS ""fac"" FROM ( SELECT ""cli"".""IdRegistro"" AS ""cliId"", ""edo"".""IdRegistro"" AS ""edoId"" FROM ""Cliente"" ""cli"" JOIN ""Estado"" ""edo"" ON (""cli"".""IdEstado"" = ""edo"".""IdRegistro"") ) ""sq"" JOIN ""Factura"" ""fac"" ON (""fac"".""IdCliente"" = ""sq"".""cliId"") "; AssertSql.AreEqual(expected, actual); }
public void StarSelect() { var r = Sql .From(new SqlTable <Cliente>()) .Select(x => x); var clause = r.Clause; var actual = SqlText.SqlSelect.SelectToStringSP(clause); var expected = @" SELECT ""x"".* FROM ""Cliente"" ""x"" "; AssertSql.AreEqual(expected, actual); }
public void SimpleOnConflict() { Expression <Func <Cliente> > valueExpr = () => new Cliente { Nombre = "Rafael", Apellido = "Salguero", }; Expression <Func <Cliente, int> > indexExpr = x => x.IdRegistro; Expression <Func <Cliente, Cliente, Cliente> > updateExpr = (excluded, orig) => new Cliente { Nombre = excluded.Nombre + orig.Nombre, Apellido = orig.Apellido, Tipo = TipoPersona.Fisica }; var doUpdate = new OnConflictDoUpdateClause( set: updateExpr, where : null ); var onConf = new OnConflictClause( indexExpressions: new LambdaExpression[] { indexExpr }, where : null, doUpdate: doUpdate ); var clause = new InsertClause( table: "Cliente", value: valueExpr.Body, query: null, onConflict: onConf, returning: null ); var ret = SqlInsertConverter.InsertToString(clause, ParamMode.Substitute, new SqlParamDic()).Sql; var expected = @" INSERT INTO ""Cliente"" (""Nombre"", ""Apellido"") VALUES ('Rafael', 'Salguero') ON CONFLICT (""IdRegistro"") DO UPDATE SET ""Nombre"" = (EXCLUDED.""Nombre"" || ""Cliente"".""Nombre""), ""Apellido"" = ""Cliente"".""Apellido"", ""Tipo"" = 0 "; AssertSql.AreEqual(expected, ret); }
public void ScalarSelect() { var q = Sql .From <Cliente>() .Select(x => x.IdRegistro); var actual = SqlText.SqlSelect.SelectToStringScalar(q.Clause, SqlText.ParamMode.None, new SqlText.SqlParamDic()); var expected = @" SELECT ""x"".""IdRegistro"" FROM ""Cliente"" ""x"" "; Assert.IsTrue(actual.Scalar); AssertSql.AreEqual(expected, actual.Sql); }