/// <summary>
/// Agrega un SELECT a la cláusula WITH
/// </summary>
public static SqlWithFromList <TWith, TOut> Query <TWith, TOut>(this ISqlWith <TWith> with, Expression <Func <TWith, ISqlSelect <TOut> > > select)
{
var clauseExpr = SqlWith.SubqueryRawSubs(select.Body, select.Parameters[0]);
var clause = (ISqlSelect <TOut>)SqlWith.GetSelectFromExpr(clauseExpr);
var wi = new WithSelectClause(select.Parameters[0], with);
return(new SqlWithFromList <TWith, TOut>(wi, clause));
}
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);
}
