public override async Task <List <T> > SelectAll(Expression <Func <T, bool> > @where, Expression <Func <T, object> > orderByColumn = null, bool isAscending = true, bool isIncludeDeleted = false)
{
var sb = new StringBuilder();
sb.Append($"{SQLConstants.SELECT} *{Environment.NewLine}");
sb.Append($"{SQLConstants.FROM} {_sqlConstants.TableFullName}{Environment.NewLine}");
var orderColumn = _sqlConstants.IdFieldName;
if (orderByColumn != null)
{
orderColumn = _expressionUtils.GetField(orderByColumn.Body);
}
var ascOrDesc = SQLConstants.DESC;
if (isAscending)
{
ascOrDesc = SQLConstants.ASC;
}
var parameters = new List <NpgsqlParameter>();
AppendWhere(where, parameters, sb, isIncludeDeleted);
sb.Append($"{SQLConstants.ORDER_BY} {orderColumn} {ascOrDesc}{Environment.NewLine}");
var items = await SQLExecutor.ExecuteSqlReturningEntityList <T>(sb.ToString(), parameters);
return(items);
}
public override async Task <List <T> > SelectMany(Expression <Func <T, bool> > where, int skip = 0, int take = 100, bool isIncludeDeleted = false,
List <OrderByInfo <T> > orderByInfos = null)
{
var sb = GetStringBuilderWithSelectFrom();
var parameters = new List <NpgsqlParameter>();
var prmSkip = new NpgsqlParameter <int>(SQLConstants.SKIP_PARAMETER_NAME, NpgsqlDbType.Integer)
{
TypedValue = skip
};
parameters.Add(prmSkip);
var prmTake = new NpgsqlParameter <int>(SQLConstants.TAKE_PARAMETER_NAME, NpgsqlDbType.Integer)
{
TypedValue = take
};
parameters.Add(prmTake);
AppendWhere(where, parameters, sb, isIncludeDeleted);
AppendOrderByFields(orderByInfos, sb);
sb.Append($"{PostgreSQLConstants.LIMIT} {PostgreSQLConstants.PARAMETER_PRESIGN}{SQLConstants.TAKE_PARAMETER_NAME} {PostgreSQLConstants.OFFSET} {PostgreSQLConstants.PARAMETER_PRESIGN}{SQLConstants.SKIP_PARAMETER_NAME} {Environment.NewLine}");
var items = await SQLExecutor.ExecuteSqlReturningEntityList <T>(sb.ToString(), parameters);
return(items);
}
public override async Task <List <T> > SelectAll(Expression <Func <T, bool> > where, bool isIncludeDeleted = false, List <OrderByInfo <T> > orderByInfos = null)
{
var sb = GetStringBuilderWithSelectFrom();
var parameters = new List <NpgsqlParameter>();
AppendWhere(where, parameters, sb, isIncludeDeleted);
AppendOrderByFields(orderByInfos, sb);
var items = await SQLExecutor.ExecuteSqlReturningEntityList <T>(sb.ToString(), parameters);
return(items);
}
public override async Task <List <T> > SelectAfter(Expression <Func <T, bool> > where, Guid lastUid, int take = 100, bool isIncludeDeleted = false,
List <OrderByInfo <T> > orderByInfos = null)
{
var sb = GetStringBuilderWithSelectFrom();
var parameters = new List <NpgsqlParameter>();
var prmTake = new NpgsqlParameter <int>(SQLConstants.TAKE_PARAMETER_NAME, NpgsqlDbType.Integer)
{
TypedValue = take
};
parameters.Add(prmTake);
AppendWhere(where, parameters, sb, isIncludeDeleted);
if (lastUid != Guid.Empty)
{
var prmLastUid = new NpgsqlParameter <Guid>(SQLConstants.LAST_UID_PARAMETER_NAME, NpgsqlDbType.Uuid)
{
TypedValue = lastUid
};
parameters.Add(prmLastUid);
var schemaName = _entityUtils.GetSchemaName(typeof(T));
var tableName = _entityUtils.GetTableName(typeof(T));
if (where == null && isIncludeDeleted)
{
sb.Append($"{SQLConstants.WHERE}");
}
else
{
sb.Append($" {SQLConstants.AND}");
}
sb.Append($" {_sqlConstants.IdFieldName} > ({SQLConstants.SELECT} {tableName}_id {SQLConstants.FROM} {schemaName}.{tableName} {SQLConstants.WHERE} {tableName}_uid = {PostgreSQLConstants.PARAMETER_PRESIGN}{SQLConstants.LAST_UID_PARAMETER_NAME}){Environment.NewLine}");
}
AppendOrderByFields(orderByInfos, sb);
sb.Append($"{PostgreSQLConstants.LIMIT} {PostgreSQLConstants.PARAMETER_PRESIGN}{SQLConstants.TAKE_PARAMETER_NAME} {PostgreSQLConstants.OFFSET} 0{Environment.NewLine}");
var items = await SQLExecutor.ExecuteSqlReturningEntityList <T>(sb.ToString(), parameters);
return(items);
}
public override async Task <List <T> > SelectMany(Expression <Func <T, bool> > @where, int skip = 0, int take = 100,
Expression <Func <T, object> > orderByColumn = null, bool isAscending = true, bool isIncludeDeleted = false)
{
var sb = new StringBuilder();
sb.Append($"{SQLConstants.SELECT} *{Environment.NewLine}");
sb.Append($"{SQLConstants.FROM} {_sqlConstants.TableFullName}{Environment.NewLine}");
var orderColumn = _sqlConstants.IdFieldName;
if (orderByColumn != null)
{
orderColumn = _expressionUtils.GetField(orderByColumn.Body);
}
var ascOrDesc = SQLConstants.DESC;
if (isAscending)
{
ascOrDesc = SQLConstants.ASC;
}
var parameters = new List <NpgsqlParameter>();
var prmSkip = new NpgsqlParameter <int>(SQLConstants.SKIP_PARAMETER_NAME, NpgsqlDbType.Integer)
{
TypedValue = skip
};
parameters.Add(prmSkip);
var prmTake = new NpgsqlParameter <int>(SQLConstants.TAKE_PARAMETER_NAME, NpgsqlDbType.Integer)
{
TypedValue = take
};
parameters.Add(prmTake);
AppendWhere(where, parameters, sb, isIncludeDeleted);
sb.Append($"{SQLConstants.ORDER_BY} {orderColumn} {ascOrDesc}{Environment.NewLine}");
sb.Append($"{PostgreSQLConstants.LIMIT} {PostgreSQLConstants.PARAMETER_PRESIGN}{SQLConstants.TAKE_PARAMETER_NAME} {PostgreSQLConstants.OFFSET} {PostgreSQLConstants.PARAMETER_PRESIGN}{SQLConstants.SKIP_PARAMETER_NAME} {Environment.NewLine}");
var items = await SQLExecutor.ExecuteSqlReturningEntityList <T>(sb.ToString(), parameters);
return(items);
}
public override async Task <List <T> > SelectAfter(Expression <Func <T, bool> > @where, Guid lastUid, int take = 100,
Expression <Func <T, object> > orderByColumn = null, bool isAscending = true, bool isIncludeDeleted = false)
{
var sb = new StringBuilder();
sb.Append($"{SQLConstants.SELECT} *{Environment.NewLine}");
sb.Append($"{SQLConstants.FROM} {_sqlConstants.TableFullName}{Environment.NewLine}");
var orderColumn = _sqlConstants.IdFieldName;
if (orderByColumn != null)
{
orderColumn = _expressionUtils.GetField(orderByColumn.Body);
}
var ascOrDesc = SQLConstants.DESC;
if (isAscending)
{
ascOrDesc = SQLConstants.ASC;
}
var parameters = new List <NpgsqlParameter>();
var prmTake = new NpgsqlParameter <int>(SQLConstants.TAKE_PARAMETER_NAME, NpgsqlDbType.Integer)
{
TypedValue = take
};
parameters.Add(prmTake);
AppendWhere(where, parameters, sb, isIncludeDeleted);
if (lastUid != Guid.Empty)
{
var prmLastUid = new NpgsqlParameter <Guid>(SQLConstants.LAST_UID_PARAMETER_NAME, NpgsqlDbType.Uuid)
{
TypedValue = lastUid
};
parameters.Add(prmLastUid);
var schemaName = _entityUtils.GetSchemaName(typeof(T));
var tableName = _entityUtils.GetTableName(typeof(T));
if (where == null && isIncludeDeleted)
{
sb.Append($"{SQLConstants.WHERE}");
}
else
{
sb.Append($" {SQLConstants.AND}");
}
sb.Append($" {_sqlConstants.IdFieldName} > ({SQLConstants.SELECT} {tableName}_id {SQLConstants.FROM} {schemaName}.{tableName} {SQLConstants.WHERE} {tableName}_uid = {PostgreSQLConstants.PARAMETER_PRESIGN}{SQLConstants.LAST_UID_PARAMETER_NAME}){Environment.NewLine}");
}
sb.Append($"{SQLConstants.ORDER_BY} {orderColumn} {ascOrDesc}{Environment.NewLine}");
sb.Append($"{PostgreSQLConstants.LIMIT} {PostgreSQLConstants.PARAMETER_PRESIGN}{SQLConstants.TAKE_PARAMETER_NAME} {PostgreSQLConstants.OFFSET} 0{Environment.NewLine}");
var items = await SQLExecutor.ExecuteSqlReturningEntityList <T>(sb.ToString(), parameters);
return(items);
}
public override async Task <List <T> > SelectAfter(Expression <Func <T, bool> > @where, long lastId, int take = 100,
Expression <Func <T, object> > orderByColumn = null, bool isAscending = true, bool isIncludeDeleted = false)
{
var sb = new StringBuilder();
sb.Append($"{SQLConstants.SELECT} *{Environment.NewLine}");
sb.Append($"{SQLConstants.FROM} {_sqlConstants.TableFullName}{Environment.NewLine}");
List <string> orderColumns = new List <string>();
if (orderByColumn != null)
{
var body = orderByColumn.Body as NewExpression;
if (body is null)
{
orderColumns.Add(_expressionUtils.GetField(orderByColumn.Body));
}
else
{
for (int i = 0; i < body.Arguments.Count; i++)
{
orderColumns.Add(_expressionUtils.GetField(body.Arguments[i]));
}
}
}
else
{
orderColumns.Add(_sqlConstants.IdFieldName);
}
var ascOrDesc = SQLConstants.DESC;
if (isAscending)
{
ascOrDesc = SQLConstants.ASC;
}
var parameters = new List <NpgsqlParameter>();
var prmTake = new NpgsqlParameter <int>(SQLConstants.TAKE_PARAMETER_NAME, NpgsqlDbType.Integer)
{
TypedValue = take
};
parameters.Add(prmTake);
AppendWhere(where, parameters, sb, isIncludeDeleted);
sb.Append($" {SQLConstants.AND} {_sqlConstants.IdFieldName} > {lastId}{Environment.NewLine}");
sb.Append($"{SQLConstants.ORDER_BY} ");
for (int i = 0; i < orderColumns.Count; i++)
{
sb.Append($"{orderColumns[i]} {ascOrDesc}");
if (i != orderColumns.Count - 1)
{
sb.Append(", ");
}
}
sb.Append($"{Environment.NewLine}");
sb.Append($"{PostgreSQLConstants.LIMIT} {PostgreSQLConstants.PARAMETER_PRESIGN}{SQLConstants.TAKE_PARAMETER_NAME} {PostgreSQLConstants.OFFSET} 0{Environment.NewLine}");
var items = await SQLExecutor.ExecuteSqlReturningEntityList <T>(sb.ToString(), parameters);
return(items);
}
