/// <summary>
/// Wrap the query in a LIMIT 2 query and returns the result. Throws if there is not exactly one element
/// </summary>
public static T Single <T>(this ISqlSelect <T> select, NpgsqlConnection conn)
{
var q = Sql.From(select).Limit(2);
var r = q.ToList(conn);
return(r.Single());
}
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);
}
/// <summary>
/// Dado un query que obtiene los saldos de un conjunto de cuentas de detalle, acumula todos los niveles de estos saldos hasta llegar a las cuentas mayores,
/// esto devuelve los saldos por cada uno de los detalles y los saldos de cada una de las cuentas acumulativas incluidas. El resultado es muy similar a la relación analítica
/// </summary>
static ISqlSelect <CuentasRaizDetSaldo> QueryAcumularSaldosDetalle(ISqlSelect <CuentasRaizDetSaldo> saldosDetalle)
{
var q = Sql
.With(saldosDetalle)
//Obtener los acumulados de todos los niveles hacia arriba del detale
.WithRecursive(
detalle => Sql.From(detalle).Select(x => x)
).UnionAll(
//Buscamos todos los padres directos de las cuentas:
(w, rec) => Sql.RawSubquery <CuentasRaizDetSaldo>(@"
SELECT
d.""IdRaiz"", null::uuid AS ""IdCuentaDet"", d.""IdCuentaPadre"" AS ""IdCuentaAcum"", c.""IdCuentaPadre"", c.""Terminacion"", c.""Nombre"",
d.""CargoAnt"", d.""AbonoAnt"", d.""CargoPer"", d.""AbonoPer""
FROM rec d
JOIN ""CuentaAcumulativa"" c ON c.""IdRegistro"" = d.""IdCuentaPadre""
"))
.Map((det, rec) => new
{
det,
rec
})
.Query(w =>
//Sumar por cuenta:
Sql.RawSubquery <CuentasRaizDetSaldo>(@"
SELECT
d.""IdRaiz"", d.""IdCuentaDet"", d.""IdCuentaAcum"", d.""IdCuentaPadre"", d.""Terminacion"", d.""Nombre"",
sum(d.""CargoAnt"") AS ""CargoAnt"", sum(d.""AbonoAnt"") AS ""AbonoAnt"", sum(d.""CargoPer"") AS ""CargoPer"", sum(d.""AbonoPer"") AS ""AbonoPer""
FROM rec d
GROUP BY d.""IdRaiz"", d.""IdCuentaDet"", d.""IdCuentaAcum"" , d.""IdCuentaPadre"", d.""Terminacion"", d.""Nombre""
")
);
return(q);
}
/// <summary>
/// Wrap the query in a LIMIT 2 query and returns the result. Throws if there is not exactly one element
/// </summary>
public static async Task <T> SingleAsync <T>(this ISqlSelect <T> select, NpgsqlConnection conn)
{
var q = Sql.From(select).Limit(2);
var r = await q.ToListAsync(conn);
return(r.Single());
}
/// <summary>
/// Wrap the query in a LIMIT 1 query and returns the result. Retrurns null if the result is empty
/// </summary>
public static async Task <T> FirstOrDefaultAsync <T>(this ISqlSelect <T> select, NpgsqlConnection conn)
{
var q = Sql.From(select).Limit(1);
var r = await q.ToListAsync(conn);
return(r.FirstOrDefault());
}
/// <summary>
/// Wrap the query in a LIMIT 1 query and returns the result. Throws if the result is empty
/// </summary>
public static T First <T>(this ISqlSelect <T> select, NpgsqlConnection conn)
{
var q = Sql.From(select).Limit(1);
var r = q.ToList(conn);
return(r.First());
}
/// <summary>
/// Convierte un Select de Sql2Sql.Sql a un IQueryable de EFCore, relacionado con cierto DbSet o query type
/// </summary>
static IQueryable <T> ExecuteIQueryable <T>(this ISqlSelect <T> select, IQueryable <T> set)
where T : class
{
var sql = select.ToSql(ParamMode.EntityFramework);
var pars = Sql2Sql.Npgsql.NpgsqlParamLogic.GetParams(sql.Params);
return(set.FromSql(sql.Sql, pars));
}
/// <summary>
/// 访问 sql 语法树中的每一个结点，并生成相应的 Sql 语句。
/// </summary>
/// <param name="tree">The tree.</param>
/// <param name="pagingInfo">The paging information.</param>
public void Generate(SqlSelect tree, PagingInfo pagingInfo = null)
{
ISqlSelect res = tree;
if (!PagingInfo.IsNullOrEmpty(pagingInfo))
{
res = ModifyToPagingTree(tree, pagingInfo);
}
base.Visit(res);
}
/// <summary>
/// Query que devuelve los saldos de un conjunto de cuentas detalle
/// </summary>
/// <param name="detalle">Subquery que tiene un renglon por cada cuenta de detalle de interés</param>
static ISqlSelect <CuentasRaizDetSaldo> QuerySaldosDetalle(ISqlSelect <CuentasRaizDet> detalle, SaldoFiltro filtro)
{
var q = Sql
.From(detalle)
.Left().Lateral(det =>
Sql.From(Sql.RawSubquery <Mov>(@"
SELECT
CASE mov.""TipoMovimiento"" WHEN 0 THEN mov.""Importe"" ELSE 0 END AS ""Cargo"",
CASE mov.""TipoMovimiento"" WHEN 1 THEN mov.""Importe"" ELSE 0 END AS ""Abono"",
pol.""Fecha""
FROM ""Movimiento"" mov
JOIN ""Poliza"" pol ON pol.""IdRegistro"" = mov.""IdPoliza""
WHERE
mov.""IdCuentaDetalle"" = ""det"".""IdCuentaDet"" AND
pol.""Aplicada"" AND NOT pol.""Borrada""
"))
.Select(x => new MovCargoAbono
{
CargoPer = Sql.Coalesce(Sql.Filter(Sql.Sum(x.Cargo), Sql.Between(x.Fecha, filtro.FechaIni, filtro.FechaFin)), 0),
AbonoPer = Sql.Coalesce(Sql.Filter(Sql.Sum(x.Abono), Sql.Between(x.Fecha, filtro.FechaIni, filtro.FechaFin)), 0),
CargoAnt = Sql.Coalesce(Sql.Filter(Sql.Sum(x.Cargo), x.Fecha < filtro.FechaIni), 0),
AbonoAnt = Sql.Coalesce(Sql.Filter(Sql.Sum(x.Abono), x.Fecha < filtro.FechaIni), 0),
}))
.On(x => true)
.Alias(x => new
{
det = x.Item1,
mov = x.Item2
})
.Select(x => new CuentasRaizDetSaldo
{
IdRaiz = x.det.IdRaiz,
IdCuentaDet = x.det.IdCuentaDet,
IdCuentaAcum = x.det.IdCuentaAcum,
IdCuentaPadre = x.det.IdCuentaPadre,
Terminacion = x.det.Terminacion,
Nombre = x.det.Nombre,
CargoAnt = x.mov.CargoAnt,
AbonoAnt = x.mov.AbonoAnt,
CargoPer = x.mov.CargoPer,
AbonoPer = x.mov.AbonoPer
});
return(q);
}
public void SelectAllFromTable()
{
var qs = new ISqlSelect <Customer>[] {
Sql.From <Customer>(),
Sql.From <Customer>().Select(x => x),
};
foreach (var q in qs)
{
var actual = q.ToString();
var expected = @"
SELECT ""x"".* FROM ""Customer"" ""x""
";
AssertSql.AreEqual(expected, actual);
}
}
/// <summary>
/// Converts an <see cref="ISqlSelect{T}"/> to an EFCore <see cref="IQueryable{T}"/>, where the query type doesn't needs to be a <see cref="DbSet{TEntity}"/>
/// of the context but it should be registered in the model as a query type
/// </summary>
public static IQueryable <T> ToIQueryable <T>(this ISqlSelect <T> select, DbContext context)
where T : class
{
return(select.ExecuteIQueryable(context.Query <T>()).AsNoTracking());
}
/// <summary>
/// Wrap the query in a COUNT(1) query and returns the result
/// </summary>
public static long LongCount <T>(this ISqlSelect <T> select, NpgsqlConnection conn)
{
var q = Sql.From(select).Select(x => Sql.Count(1));
return(q.First(conn));
}
/// <summary>
/// Wrap the query in a COUNT(1) query and returns the result
/// </summary>
public static async Task <long> LongCountAsync <T>(this ISqlSelect <T> select, NpgsqlConnection conn)
{
var q = Sql.From(select).Select(x => Sql.Count(1));
return(await q.FirstAsync(conn));
}
/// <summary>
/// Wrap the query in a COUNT(1) query and returns the result
/// </summary>
public static int Count <T>(this ISqlSelect <T> select, NpgsqlConnection conn)
{
return((int)select.LongCount(conn));
}
/// <summary>
/// Wrap the query in a COUNT(1) query and returns the result
/// </summary>
public static async Task <int> CountAsync <T>(this ISqlSelect <T> select, NpgsqlConnection conn)
{
return((int)(await select.LongCountAsync(conn)));
}
/// <summary> /// Indica que un subquery es escalar, por lo que se puede usar dentro de expresiones de Select /// </summary> public static T Scalar <T>(this ISqlSelect <T> subquery) => throw new SqlFunctionException();
/// <summary>
/// Wrap the query in a LIMIT 1 query and returns the result. Throws if there is not exactly one element
/// </summary>
public static async Task <T> SingleAsync <T, TDb>(this ISqlSelect <T> select, DbContext context)
{
return(await DoConnection(context, conn => select.SingleAsync(conn)));
}
public static bool Exists(ISqlSelect subquery) => throw new SqlFunctionException();
/// <summary>
/// Wrap the query in a LIMIT 1 query and returns the result. Throws if there is not exactly one element
/// </summary>
public static T Single <T>(this ISqlSelect <T> select, DbContext context)
{
return(DoConnection(context, conn => select.Single(conn)));
}
/// <summary>
/// Wrap the query in a LIMIT 1 query and returns the result. Throws if the result is empty
/// </summary>
public static T First <T>(this ISqlSelect <T> select, DbContext context)
{
return(DoConnection(context, conn => select.First(conn)));
}
public SqlSelectWrapperQueryBuilder(ISqlSelect innerSqlSelect)
{
_innerSqlSelect = innerSqlSelect;
}
public static bool In <T>(T expression, ISqlSelect <T> subquery) => throw new SqlFunctionException();
/// <summary>
/// Wrap the query in a COUNT(1) query and returns the result
/// </summary>
public static async Task <int> CountAsync <T>(this ISqlSelect <T> select, DbContext context)
{
return(await DoConnectionAsync(context, conn => select.CountAsync(conn)));
}
/// <summary>
/// Dado un query que obtiene los cargos y abonos de un conjunto de cuentas, devuelve un DTO con los saldos de la cuenta, según su naturaleza y otros datos extras
/// </summary>
/// <param name="cargosAbo"></param>
/// <returns></returns>
static ISqlSelect <CuentaSaldoDto> QuerySaldoDto(ISqlSelect <ICuentaCargosAbonos> cargosAbo)
{
var mayor =
//Obtener el IdCuentaMayor y el Numero de la cuenta, ya sea la de detalle o la acumulativa:
Sql
.From(cargosAbo)
.Left().Join <ICuenta>("CuentaDetalle").On(x => x.Item2.IdRegistro == x.Item1.IdCuentaDet)
.Left().Join <ICuenta>("CuentaAcumulativa").On(x => x.Item3.IdRegistro == x.Item1.IdCuentaAcum)
.Alias(x => new
{
carAbo = x.Item1,
det = x.Item2,
acum = x.Item3
})
.Select(x => new
{
x.carAbo.CargoAnt,
x.carAbo.AbonoAnt,
x.carAbo.CargoPer,
x.carAbo.AbonoPer,
x.carAbo.IdCuentaDet,
x.carAbo.IdCuentaAcum,
IdCuenta = Sql.Coalesce(x.carAbo.IdCuentaDet, x.carAbo.IdCuentaAcum),
IdCuentaMayor = Sql.Coalesce(x.det.IdCuentaMayor, x.acum.IdCuentaMayor),
Numero = Sql.Coalesce(x.det.Numero, x.acum.Numero),
Nombre = Sql.Coalesce(x.det.Nombre, x.acum.Nombre)
});
var dto = Sql
.From(mayor)
.Inner().Join <ICuentaMayor>("CuentaAcumulativa").On(x => x.Item2.IdRegistro == x.Item1.IdCuentaMayor)
.Alias(x => new
{
cuenta = x.Item1,
mayor = x.Item2
})
.Select(x => new CuentaSaldoDto
{
Numero = x.cuenta.Numero,
CargoAnt = x.cuenta.CargoAnt,
AbonoAnt = x.cuenta.AbonoAnt,
CargoPer = x.cuenta.CargoPer,
AbonoPer = x.cuenta.AbonoPer,
CargoAct = x.cuenta.CargoAnt + x.cuenta.CargoPer,
AbonoAct = x.cuenta.AbonoAnt + x.cuenta.AbonoPer,
SaldoAnt = (x.mayor.Naturaleza == NaturalezaCuenta.Acreedora ? 1 : -1) * (x.cuenta.AbonoAnt - x.cuenta.CargoAnt),
SaldoPer = (x.mayor.Naturaleza == NaturalezaCuenta.Acreedora ? 1 : -1) * (x.cuenta.AbonoPer - x.cuenta.CargoPer),
SaldoAct = (x.mayor.Naturaleza == NaturalezaCuenta.Acreedora ? 1 : -1) * ((x.cuenta.AbonoAnt + x.cuenta.AbonoPer) - (x.cuenta.CargoAnt + x.cuenta.CargoPer)),
IdCuentaDet = x.cuenta.IdCuentaDet,
IdCuentaAcum = x.cuenta.IdCuentaAcum,
IdCuentaMayor = x.cuenta.IdCuentaMayor,
Nombre = x.cuenta.Nombre,
Naturaleza = x.mayor.Naturaleza,
Tipo = x.mayor.TipoCuenta
});
return(dto);
}
/// <summary>
/// Converts an <see cref="ISqlSelect{T}"/> to an EFCore <see cref="IQueryable{T}"/> related to the given <see cref="DbSet{TEntity}"/>
/// </summary>
public static IQueryable <T> ToIQueryableSet <T, TDbSet>(this ISqlSelect <T> select, DbSet <T> set)
where T : class
{
return(select.ExecuteIQueryable(set));
}
/// <summary>
/// Wrap the query in a COUNT(1) query and returns the result
/// </summary>
public static int Count <T>(this ISqlSelect <T> select, DbContext context)
{
return(DoConnection(context, conn => select.Count(conn)));
}
/// <summary>
/// Wrap the query in a LIMIT 1 query and returns the result. Retrurns null if the result is empty
/// </summary>
public static async Task <T> FirstOrDefaultAsync <T, TDb>(this ISqlSelect <T> select, DbContext context)
{
return(await DoConnection(context, conn => select.FirstOrDefaultAsync(conn)));
}
public SqlSelect(ISqlSelect innerSqlSelect, ISqlAlias alias)
: this(innerSqlSelect, new SqlSelectInfo(alias),
new SqlSelectWrapperQueryBuilder(innerSqlSelect))
{
}
/// <summary>
/// Wrap the query in a COUNT(1) query and returns the result
/// </summary>
public static async Task <long> LongCountAsync <T, TDb>(this ISqlSelect <T> select, DbContext context)
{
return(await DoConnection(context, conn => select.LongCountAsync(conn)));
}
private SqlSelect(ISqlSelect innerSqlSelect, SqlSelectInfo info, ISqlSelectQueryBuilder queryBuilder) : base(info, queryBuilder)
{
_innerSqlSelect = innerSqlSelect ?? throw new ArgumentNullException(nameof(innerSqlSelect));
}
