/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
protected override void GenerateOrderings(SelectExpression selectExpression)
{
base.GenerateOrderings(selectExpression);
// In SQL Server, if an offset is specified, then an ORDER BY clause must also exist.
// Generate a fake one.
if (!selectExpression.Orderings.Any() && selectExpression.Offset != null)
{
Sql.AppendLine().Append("ORDER BY (SELECT 1)");
}
}
public override Expression VisitSelect(SelectExpression selectExpression)
{
base.VisitSelect(selectExpression);
//And here we check if query requires recompilation
if (selectExpression.Tags.Contains(QueryHints.RecompileTag))
{
Sql.AppendLine().AppendLine("option(recompile)");
}
return(selectExpression);
}
protected override Expression VisitConditional(ConditionalExpression conditionalExpression)
{
Check.NotNull(conditionalExpression, nameof(conditionalExpression));
Sql.AppendLine("CASE");
using (Sql.Indent()) {
Sql.Append("WHEN ");
Visit(conditionalExpression.Test);
Sql.AppendLine();
Sql.Append("THEN ");
if (conditionalExpression.IfTrue is ConstantExpression constantExpression && constantExpression.Type == typeof(bool))
{
Sql.Append(((bool)constantExpression.Value) ? TypedTrueLiteral : TypedFalseLiteral);
}
public void Delete(Cliente objeto)
{
Sql.Clear();
Sql.AppendLine("DELETE FROM Clientes ");
Sql.AppendLine("WHERE Id = @Id ");
var Command = AcessoDados.CreateCommand(Sql.ToString());
Command.Parameters.AddWithValue("@Id", objeto.Id);
Command.ExecuteNonQuery();
}
public List <Cliente> SelectAll()
{
Sql.Clear();
Sql.AppendLine(" SELECT * FROM Clientes ");
var Command = AcessoDados.CreateCommand(Sql.ToString());
var Reader = AcessoDados.Reader(Command);
var Clientes = Cast(Reader);
return(Clientes);
}
public List <Endereco> SelectAll()
{
Sql.Clear();
Sql.AppendLine(" SELECT * FROM Endereco ");
var Command = AcessoDados.CreateCommand(Sql.ToString());
var Reader = AcessoDados.Reader(Command);
var Enderecos = Cast(Reader);
return(Enderecos);
}
/// <summary>
/// This API supports the Entity Framework Core infrastructure and is not intended to be used
/// directly from your code. This API may change or be removed in future releases.
/// </summary>
protected override void GenerateLimitOffset(SelectExpression selectExpression)
{
if (selectExpression.Offset == null)
{
return;
}
if (!selectExpression.OrderBy.Any())
{
Sql.AppendLine().Append("ORDER BY 'a'");
}
Sql.AppendLine().Append(" SKIP ");
Visit(selectExpression.Offset);
}
public override Expression VisitFromSql(FromSqlExpression fromSqlExpression)
{
Check.NotNull(fromSqlExpression, nameof(fromSqlExpression));
Sql.AppendLine("(");
using (Sql.Indent()) {
GenerateFromSql(fromSqlExpression.Sql, fromSqlExpression.Arguments, ParameterValues);
}
Sql.Append(") ")
.Append(SqlGenerator.DelimitIdentifier(fromSqlExpression.Alias));
return(fromSqlExpression);
}
protected override void GenerateLimitOffset(SelectExpression selectExpression)
{
if (selectExpression.Projection.OfType <RowNumberExpression>().Any())
{
return;
}
if (selectExpression.Offset != null &&
!selectExpression.OrderBy.Any())
{
Sql.AppendLine().Append("ORDER BY @@ROWCOUNT");
}
base.GenerateLimitOffset(selectExpression);
}
/// <summary>
/// 重写LimitOffset分页(12c支持的原生分页)
/// </summary>
/// <param name="selectExpression"></param>
protected override void GenerateLimitOffset(SelectExpression selectExpression)
{
try
{
if (Check.IsTraceEnabled(m_oracleLogger?.Logger))
{
Trace<DbLoggerCategory.Query>.Write(m_oracleLogger, LogLevel.Trace, OracleTraceTag.Entry, OracleTraceClassName.OracleQuerySqlGenerator, OracleTraceFuncName.GenerateLimitOffset);
}
Check.NotNull(selectExpression, nameof(selectExpression));
if (selectExpression.Limit != null && selectExpression.Offset == null)
{
Sql.AppendLine().Append("FETCH FIRST ");
Visit(selectExpression.Limit);
Sql.Append(" ROWS ONLY");
}
else
{
base.GenerateLimitOffset(selectExpression);
}
if (selectExpression.Limit != null)
{
if (Check.IsTraceEnabled(m_oracleLogger?.Logger))
{
Trace<DbLoggerCategory.Query>.Write(m_oracleLogger, LogLevel.Trace, OracleTraceTag.SQL, OracleTraceClassName.OracleQuerySqlGenerator, OracleTraceFuncName.GenerateLimitOffset, $"Limit:{selectExpression.Limit.ToString()}");
}
}
else if (Check.IsTraceEnabled(m_oracleLogger?.Logger))
{
Trace<DbLoggerCategory.Query>.Write(m_oracleLogger, LogLevel.Trace, OracleTraceTag.SQL, OracleTraceClassName.OracleQuerySqlGenerator, OracleTraceFuncName.GenerateLimitOffset, "Limit:null");
}
}
catch (Exception ex)
{
if (Check.IsErrorEnabled(m_oracleLogger?.Logger))
{
Trace<DbLoggerCategory.Query>.Write(m_oracleLogger, LogLevel.Error, OracleTraceTag.Error, OracleTraceClassName.OracleQuerySqlGenerator, OracleTraceFuncName.GenerateLimitOffset, ex.ToString());
}
throw;
}
finally
{
if (Check.IsTraceEnabled(m_oracleLogger?.Logger))
{
Trace<DbLoggerCategory.Query>.Write(m_oracleLogger, LogLevel.Trace, OracleTraceTag.Exit, OracleTraceClassName.OracleQuerySqlGenerator, OracleTraceFuncName.GenerateLimitOffset);
}
}
}
protected override void GenerateTop(SelectExpression selectExpression)
{
if (selectExpression.Limit != null)
{
Sql.AppendLine().Append("FIRST ");
Visit(selectExpression.Limit);
Sql.AppendLine().Append(" ");
}
if (selectExpression.Offset != null)
{
Sql.Append("SKIP ");
Visit(selectExpression.Offset);
Sql.Append(" ");
}
}
protected override void GenerateLimitOffset(SelectExpression selectExpression)
{
#if SQLCE35
if (selectExpression.Offset != null)
{
throw new NotSupportedException("SKIP clause is not supported by SQL Server Compact 3.5");
}
#endif
if ((selectExpression.Offset != null) &&
!selectExpression.OrderBy.Any())
{
Sql.AppendLine().Append("ORDER BY GETDATE()");
}
base.GenerateLimitOffset(selectExpression);
}
protected override void GenerateLimitOffset(SelectExpression selectExpression)
{
Check.NotNull(selectExpression, nameof(selectExpression));
if (selectExpression.Limit != null || selectExpression.Offset != null)
{
Sql.AppendLine()
.Append("LIMIT ")
.Append(selectExpression.Limit ?? -1);
if (selectExpression.Offset != null)
{
Sql.Append(" OFFSET ")
.Append(selectExpression.Offset);
}
}
}
public List <Endereco> Select(Endereco Object)
{
Sql.Clear();
Sql.AppendLine(" SELECT * FROM Endereco ");
Sql.AppendLine(" WHERE IdCliente = @IdCliente ");
var Command = AcessoDados.CreateCommand(Sql.ToString());
Command.Parameters.AddWithValue("@IdCliente", Object.IdCliente);
var Reader = AcessoDados.Reader(Command);
var Enderecos = Cast(Reader);
return(Enderecos);
}
public Cliente Find(Cliente objeto)
{
Sql.Clear();
Sql.AppendLine("SELECT * FROM Clientes ");
Sql.AppendLine("WHERE Clientes.Id = @Id ");
var Command = AcessoDados.CreateCommand(Sql.ToString());
Command.Parameters.AddWithValue("@Id", objeto.Id);
var Reader = AcessoDados.Reader(Command);
var Cliente = Cast(Reader).FirstOrDefault();
return(Cliente);
}
public Endereco Find(int Id)
{
Sql.Clear();
Sql.AppendLine(" SELECT * FROM Endereco ");
Sql.AppendLine(" WHERE Id = @Id ");
var Command = AcessoDados.CreateCommand(Sql.ToString());
Command.Parameters.AddWithValue("@Id", Id);
var Reader = AcessoDados.Reader(Command);
var Enderecos = Cast(Reader);
return(Enderecos.FirstOrDefault());
}
protected override void GenerateLimitOffset(SelectExpression selectExpression)
{
if (selectExpression.Limit != null || selectExpression.Offset != null)
{
Sql.AppendLine().Append("LIMIT ");
Visit(
selectExpression.Offset
?? new SqlConstantExpression(Expression.Constant(-1), selectExpression.Offset.TypeMapping));
if (selectExpression.Limit != null)
{
Sql.Append(", ");
Visit(selectExpression.Limit);
}
}
}
protected override void GenerateLimitOffset(SelectExpression selectExpression)
{
ThrowIf.Argument.IsNull(selectExpression, "selectExpression");
if (selectExpression.Limit != null || selectExpression.Offset != null)
{
Sql.AppendLine().Append("LIMIT ");
Visit(selectExpression.Limit ?? Expression.Constant(-1));
if (selectExpression.Offset != null)
{
Sql.Append(" OFFSET ");
Visit(selectExpression.Offset);
}
}
}
public void Update(Cliente objeto)
{
Sql.Clear();
Sql.AppendLine("UPDATE Clientes SET ");
Sql.AppendLine(" Nome = @Nome, @Sobrenome = @Sobrenome, Idade = @Idade, DataNascimento = @DataNascimento ");
Sql.AppendLine("WHERE Id = @Id ");
var Command = AcessoDados.CreateCommand(Sql.ToString());
Command.Parameters.AddWithValue("@Id", objeto.Id);
Command.Parameters.AddWithValue("@Nome", objeto.Nome);
Command.Parameters.AddWithValue("@Sobrenome", objeto.Sobrenome);
Command.Parameters.AddWithValue("@Idade", objeto.Idade);
Command.Parameters.AddWithValue("@DataNascimento", objeto.DataNascimento);
Command.ExecuteNonQuery();
}
protected override void GenerateLimitOffset(SelectExpression selectExpression)
{
Check.NotNull(selectExpression, nameof(selectExpression));
if (selectExpression.Limit != null &&
selectExpression.Offset == null)
{
Sql.AppendLine().Append("FETCH FIRST ");
Visit(selectExpression.Limit);
Sql.Append(" ROWS ONLY");
}
else
{
base.GenerateLimitOffset(selectExpression);
}
}
public Cliente Insert(Cliente objeto)
{
Sql.Clear();
Sql.AppendLine("INSERT INTO Clientes (Nome, Sobrenome, Idade, DataNascimento) ");
Sql.AppendLine("OUTPUT inserted.Id ");
Sql.AppendLine("VALUES (@Nome, @Sobrenome, @Idade, @DataNascimento) ");
var Command = AcessoDados.CreateCommand(Sql.ToString());
Command.Parameters.AddWithValue("@Nome", objeto.Nome);
Command.Parameters.AddWithValue("@Sobrenome", objeto.Sobrenome);
Command.Parameters.AddWithValue("@Idade", objeto.Idade);
Command.Parameters.AddWithValue("@DataNascimento", objeto.DataNascimento);
objeto.Id = (int)Command.ExecuteScalar();
return(objeto);
}
protected override void GenerateLimitOffset(SelectExpression selectExpression)
{
Check.NotNull(selectExpression, nameof(selectExpression));
if ((selectExpression.Limit != null) ||
(selectExpression.Offset != null))
{
Sql.AppendLine()
.Append("LIMIT ");
Visit(selectExpression.Limit ?? Expression.Constant(-1));
if (selectExpression.Offset != null)
{
Sql.Append(" OFFSET ");
Visit(selectExpression.Offset);
}
}
}
// https://github.com/aspnet/EntityFrameworkCore/issues/19031
protected override void GenerateOrderings(SelectExpression selectExpression)
{
if (selectExpression.Orderings.Any())
{
var orderings = selectExpression.Orderings.ToList();
if (selectExpression.Limit == null &&
selectExpression.Offset == null)
{
orderings.RemoveAll(oe => oe.Expression is SqlConstantExpression || oe.Expression is SqlParameterExpression);
}
if (orderings.Count > 0)
{
Sql.AppendLine()
.Append("ORDER BY ");
GenerateList(orderings, e => Visit(e));
}
}
}
/// <summary>
/// This API supports the Entity Framework Core infrastructure and is not intended to be used
/// directly from your code. This API may change or be removed in future releases.
/// </summary>
protected override void GenerateLimitOffset(SelectExpression selectExpression)
{
Check.NotNull(selectExpression, nameof(selectExpression));
if (selectExpression.Limit != null)
{
Sql.AppendLine().Append("LIMIT ");
Visit(selectExpression.Limit);
}
if (selectExpression.Offset != null)
{
if (selectExpression.Limit == null)
{
// if we want to use Skip() without Take() we have to define the upper limit of LIMIT
Sql.AppendLine().Append("LIMIT ").Append(18446744073709551610);
}
Sql.Append(" OFFSET ");
Visit(selectExpression.Offset);
}
}
protected override void GeneratePredicate([NotNull] Expression predicate)
{
Expression expression = Db2ApplyOptimizations(predicate, true, false);
BinaryExpression binaryExpression;
if ((binaryExpression = (expression as BinaryExpression)) != null)
{
bool?booleanConstantValue = GetBooleanConstantValue(binaryExpression.Left);
bool?booleanConstantValue2 = GetBooleanConstantValue(binaryExpression.Right);
if (binaryExpression.NodeType == ExpressionType.Equal && booleanConstantValue == true && booleanConstantValue2 == true)
{
return;
}
if (binaryExpression.NodeType == ExpressionType.NotEqual && booleanConstantValue == false && booleanConstantValue2 == false)
{
return;
}
}
Sql.AppendLine().Append("WHERE ");
(this).Visit(expression);
}
protected override void VisitQuery(QueryExpression queryExpression)
{
switch (queryExpression)
{
case MySQLRawQueryExpression rawExpression:
Sql.Append(rawExpression.SqlText);
break;
case TransactionExpression transaction:
Sql.AppendLine("START TRANSACTION;");
foreach (var query in transaction.Queries)
{
Visit(query);
}
Sql.AppendLine("COMMIT;");
break;
default:
base.VisitQuery(queryExpression);
break;
}
}
public Endereco Insert(Endereco objeto)
{
Sql.Clear();
Sql.AppendLine("INSERT INTO Endereco (IdCliente, Logradouro, Bairro, Cidade, Estado, Cep, Tipo) ");
Sql.AppendLine("OUTPUT inserted.Id ");
Sql.AppendLine("VALUES (@IdCliente, @Logradouro, @Bairro, @Cidade, @Estado, @Cep, @Tipo) ");
var Command = AcessoDados.CreateCommand(Sql.ToString());
Command.Parameters.AddWithValue("@IdCliente", objeto.IdCliente);
Command.Parameters.AddWithValue("@Logradouro", objeto.Logradouro);
Command.Parameters.AddWithValue("@Bairro", objeto.Bairro);
Command.Parameters.AddWithValue("@Cidade", objeto.Cidade);
Command.Parameters.AddWithValue("@Estado", objeto.Estado);
Command.Parameters.AddWithValue("@Cep", objeto.Cep);
Command.Parameters.AddWithValue("@Tipo", objeto.Tipo);
objeto.Id = (int)Command.ExecuteScalar();
return(objeto);
}
/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
protected override void GenerateLimitOffset(SelectExpression selectExpression)
{
Check.NotNull(selectExpression, nameof(selectExpression));
if (selectExpression.Limit != null ||
selectExpression.Offset != null)
{
Sql.AppendLine()
.Append("LIMIT ");
Visit(
selectExpression.Limit
?? new SqlConstantExpression(Expression.Constant(-1), selectExpression.Offset !.TypeMapping));
if (selectExpression.Offset != null)
{
Sql.Append(" OFFSET ");
Visit(selectExpression.Offset);
}
}
}
protected override void GenerateLimitOffset(SelectExpression selectExpression)
{
// Note: For Limit without Offset, SqlServer generates TOP()
if (selectExpression.Offset != null)
{
Sql.AppendLine()
.Append("OFFSET ");
Visit(selectExpression.Offset);
Sql.Append(" ROWS");
if (selectExpression.Limit != null)
{
Sql.Append(" FETCH NEXT ");
Visit(selectExpression.Limit);
Sql.Append(" ROWS ONLY");
}
}
}
/// <inheritdoc />
protected override void GenerateLimitOffset(SelectExpression selectExpression)
{
GaxPreconditions.CheckNotNull(selectExpression, nameof(selectExpression));
if (selectExpression.Limit != null)
{
Sql.AppendLine().Append("LIMIT ");
Visit(selectExpression.Limit);
}
else if (selectExpression.Offset != null)
{
// Cloud Spanner requires limit if offset is specified.
Sql.AppendLine().Append($"LIMIT {int.MaxValue}");
}
if (selectExpression.Offset == null)
{
return;
}
Sql.Append(" OFFSET ");
Visit(selectExpression.Offset);
}
