c# - framework - ¿Cómo convertir un árbol de expresiones a una consulta SQL parcial?

linq entity framework c#

Question

Cuando EF o LINQ to SQL ejecuta una consulta, este:

1. Construye un árbol de expresiones desde el código,
2. Convierte el árbol de expresiones en una consulta SQL,
3. Ejecuta la consulta, obtiene los resultados brutos de la base de datos y los convierte al resultado que utilizará la aplicación.

Mirando el rastro de la pila, no puedo entender dónde ocurre la segunda parte.

En general, ¿es posible usar una parte existente de EF o (preferiblemente) LINQ to SQL para convertir un objeto Expression a una consulta SQL parcial (usando la sintaxis de Transact-SQL), o tengo que reinventar la rueda?

Actualización: un comentario pide que proporcione un ejemplo de lo que intento hacer.

En realidad, la respuesta de Ryan Wright a continuación ilustra perfectamente lo que quiero lograr como resultado, excepto el hecho de que mi pregunta es específicamente sobre cómo puedo hacerlo mediante el uso de los mecanismos existentes de .NET Framework realmente utilizados por EF y LINQ to SQL , en lugar de tener que reinventar la rueda y escribir miles de líneas de código no tan probado para hacer algo similar.

Aquí también hay un ejemplo. Nuevamente, tenga en cuenta que no hay código generado por ORM.

private class Product
{
[DatabaseMapping("ProductId")]
public int Id { get; set; }
[DatabaseMapping("Price")]
public int PriceInCents { get; set; }
}
private string Convert(Expression expression)
{
// Some magic calls to .NET Framework code happen here.
// [...]
}
private void TestConvert()
{
Expression<Func<Product, int, int, bool>> inPriceRange =
(Product product, int from, int to) =>
product.PriceInCents >= from && product.PriceInCents <= to;
string actualQueryPart = this.Convert(inPriceRange);
Assert.AreEqual("[Price] between @from and @to", actualQueryPart);
}

¿De dónde viene el nombre Price en la consulta esperada?

El nombre se puede obtener a través de la reflexión consultando el atributo CustomMail DatabaseMapping de la propiedad Price de la clase Product .

¿De dónde vienen los nombres @from y @to en la consulta esperada?

Esos nombres son los nombres reales de los parámetros de la expresión.

¿De dónde viene between … and venir en la consulta esperada?

Este es un posible resultado de una expresión binaria. Tal vez EF o LINQ to SQL, en lugar de between … and declaración, se quedarían con [Price] >= @from and [Price] <= @to en [Price] >= @from and [Price] <= @to lugar. Está bien también, realmente no importa ya que el resultado es lógicamente el mismo (no menciono el rendimiento).

¿Por qué no hay ningún where en la consulta esperada?

Porque nada indica en la Expression que debe haber una palabra clave where . Tal vez la expresión real sea solo una de las expresiones que se combinarían más adelante con operadores binarios para construir una consulta más grande para anteponer con un where .

Answer 1

Básicamente tienes que reinventar la rueda. QueryProvider es lo que hace la traducción de los árboles de expresión a su sintaxis nativa de la tienda. Es lo que va a manejar situaciones especiales al igual que string.Contains (), string.StartsWith () y todas las funciones especiales que lo manejan. También gestionará las búsquedas de metadatos en las distintas capas de su ORM (* .edml en el caso de Entity Framework primero en base de datos o modelo primero). Ya hay ejemplos y marcos para construir comandos SQL. Pero lo que estás buscando suena como una solución parcial.

También entiendo que los metadatos de tabla / vista son necesarios para determinar correctamente lo que es legal. Los proveedores de consultas son bastante complejos y hacen mucho por usted más allá de hacer simples conversiones de árboles de expresiones en SQL.

En respuesta a su ¿dónde ocurre la segunda parte? La segunda parte ocurre durante la enumeración de IQueryable. Las IQueryables también son IEnumerables y, en última instancia, cuando se llama a GetEnumerator, a su vez llamará al proveedor de consultas con el árbol de expresiones que utilizará sus metadatos para producir un comando sql. No es exactamente lo que sucede, pero debería transmitir la idea.

Answer 2

En Linq2SQL puedes usar:

var cmd = DataContext.GetCommand(expression);
var sqlQuery = cmd.CommandText;

Answer 3

La respuesta corta parece ser que no puede usar una parte de EF o LINQ en SQL como acceso directo a la traducción. Necesita al menos una subclase de ObjectContext para acceder a la propiedad internal protected QueryProvider internal protected , y eso significa que todos los gastos generales de creación del contexto, incluidos todos los metadatos, etc.

Suponiendo que está bien con eso, para obtener una consulta SQL parcial, por ejemplo, solo la cláusula WHERE , básicamente va a necesitar el proveedor de consultas y llamar a IQueryProvider.CreateQuery() tal como lo hace LINQ en su implementación de Queryable.Where . Para obtener una consulta más completa, puede usar ObjectQuery.ToTraceString() .

En cuanto a dónde sucede esto, los fundamentos del proveedor de LINQ declaran generalmente que

IQueryProvider devuelve una referencia a IQueryable con el árbol de expresiones construido pasado por el marco LINQ, que se utiliza para otras llamadas. En términos generales, cada bloque de consulta se convierte en un grupo de llamadas a métodos. Para cada llamada de método, hay algunas expresiones involucradas. Al crear nuestro proveedor, en el método IQueryProvider.CreateQuery, ejecutamos las expresiones y llenamos un objeto de filtro, que se utiliza en el método IQueryProvider.Execute para ejecutar una consulta en el almacén de datos.

y eso

la consulta se puede ejecutar de dos maneras, implementando el método GetEnumerator (definido en la interfaz IEnumerable) en la clase Query (que hereda de IQueryable); o puede ser ejecutado por el tiempo de ejecución LINQ directamente

Verificando EF debajo del depurador es el primero.

Si no quiere reinventar por completo la rueda y EF y LINQ to SQL no son opciones, quizás esta serie de artículos ayude:

• Cómo: Traducción de LINQ a SQL
• Cómo: Traducción de LINQ a SQL - Parte II
• Cómo: Traducción de LINQ a SQL - Parte III

Aquí hay algunas fuentes para crear un proveedor de consultas que probablemente impliquen mucho más trabajo pesado de su parte para implementar lo que desea:

• LINQ: creación de una serie de proveedores IQueryable
• Crear un proveedor de LINQ personalizado utilizando LinqExtender

Answer 4

No está completo, pero aquí hay algunos pensamientos para que puedas escuchar si vienes por esto más tarde:

 private string CreateWhereClause(Expression<Func<T, bool>> predicate)
{
StringBuilder p = new StringBuilder(predicate.Body.ToString());
var pName = predicate.Parameters.First();
p.Replace(pName.Name + ".", "");
p.Replace("==", "=");
p.Replace("AndAlso", "and");
p.Replace("OrElse", "or");
p.Replace("/"", "/'");
return p.ToString();
}
private string AddWhereToSelectCommand(Expression<Func<T, bool>> predicate, int maxCount = 0)
{
string command = string.Format("{0} where {1}", CreateSelectCommand(maxCount), CreateWhereClause(predicate));
return command;
}
private string CreateSelectCommand(int maxCount = 0)
{
string selectMax = maxCount > 0 ? "TOP " + maxCount.ToString() + " * " : "*";
string command = string.Format("Select {0} from {1}", selectMax, _tableName);
return command;
}

Answer 5

No estoy seguro de si esto es exactamente lo que necesita, pero parece que podría estar cerca:

string[] companies = { "Consolidated Messenger", "Alpine Ski House", "Southridge Video", "City Power & Light",
"Coho Winery", "Wide World Importers", "Graphic Design Institute", "Adventure Works",
"Humongous Insurance", "Woodgrove Bank", "Margie''s Travel", "Northwind Traders",
"Blue Yonder Airlines", "Trey Research", "The Phone Company",
"Wingtip Toys", "Lucerne Publishing", "Fourth Coffee" };
// The IQueryable data to query.
IQueryable<String> queryableData = companies.AsQueryable<string>();
// Compose the expression tree that represents the parameter to the predicate.
ParameterExpression pe = Expression.Parameter(typeof(string), "company");
// ***** Where(company => (company.ToLower() == "coho winery" || company.Length > 16)) *****
// Create an expression tree that represents the expression ''company.ToLower() == "coho winery"''.
Expression left = Expression.Call(pe, typeof(string).GetMethod("ToLower", System.Type.EmptyTypes));
Expression right = Expression.Constant("coho winery");
Expression e1 = Expression.Equal(left, right);
// Create an expression tree that represents the expression ''company.Length > 16''.
left = Expression.Property(pe, typeof(string).GetProperty("Length"));
right = Expression.Constant(16, typeof(int));
Expression e2 = Expression.GreaterThan(left, right);
// Combine the expression trees to create an expression tree that represents the
// expression ''(company.ToLower() == "coho winery" || company.Length > 16)''.
Expression predicateBody = Expression.OrElse(e1, e2);
// Create an expression tree that represents the expression
// ''queryableData.Where(company => (company.ToLower() == "coho winery" || company.Length > 16))''
MethodCallExpression whereCallExpression = Expression.Call(
typeof(Queryable),
"Where",
new Type[] { queryableData.ElementType },
queryableData.Expression,
Expression.Lambda<Func<string, bool>>(predicateBody, new ParameterExpression[] { pe }));
// ***** End Where *****
// ***** OrderBy(company => company) *****
// Create an expression tree that represents the expression
// ''whereCallExpression.OrderBy(company => company)''
MethodCallExpression orderByCallExpression = Expression.Call(
typeof(Queryable),
"OrderBy",
new Type[] { queryableData.ElementType, queryableData.ElementType },
whereCallExpression,
Expression.Lambda<Func<string, string>>(pe, new ParameterExpression[] { pe }));
// ***** End OrderBy *****
// Create an executable query from the expression tree.
IQueryable<string> results = queryableData.Provider.CreateQuery<string>(orderByCallExpression);
// Enumerate the results.
foreach (string company in results)
Console.WriteLine(company);

Answer 6

Puedes usar el siguiente código:

var query = from c in Customers
select c;
string sql = ((ObjectQuery)query).ToTraceString();

Eche un vistazo a la siguiente información: Recuperando el SQL generado por el proveedor de la entidad .

Answer 7

Sí, es posible, puede analizar un árbol de expresiones LINQ utilizando el patrón de visitante. Debería construir un traductor de consultas al crear una clase de ExpressionVisitor como se muestra a continuación. Al enganchar los puntos correctos, puede usar el traductor para construir su cadena SQL a partir de su expresión LINQ. Tenga en cuenta que el siguiente código solo trata con las cláusulas básicas order / orderby / skip / take, pero puede completarlo con más según sea necesario. Espero que sirva como un buen primer paso.

public class MyQueryTranslator : ExpressionVisitor
{
private StringBuilder sb;
private string _orderBy = string.Empty;
private int? _skip = null;
private int? _take = null;
private string _whereClause = string.Empty;
public int? Skip
{
get
{
return _skip;
}
}
public int? Take
{
get
{
return _take;
}
}
public string OrderBy
{
get
{
return _orderBy;
}
}
public string WhereClause
{
get
{
return _whereClause;
}
}
public MyQueryTranslator()
{
}
public string Translate(Expression expression)
{
this.sb = new StringBuilder();
this.Visit(expression);
_whereClause = this.sb.ToString();
return _whereClause;
}
private static Expression StripQuotes(Expression e)
{
while (e.NodeType == ExpressionType.Quote)
{
e = ((UnaryExpression)e).Operand;
}
return e;
}
protected override Expression VisitMethodCall(MethodCallExpression m)
{
if (m.Method.DeclaringType == typeof(Queryable) && m.Method.Name == "Where")
{
this.Visit(m.Arguments[0]);
LambdaExpression lambda = (LambdaExpression)StripQuotes(m.Arguments[1]);
this.Visit(lambda.Body);
return m;
}
else if (m.Method.Name == "Take")
{
if (this.ParseTakeExpression(m))
{
Expression nextExpression = m.Arguments[0];
return this.Visit(nextExpression);
}
}
else if (m.Method.Name == "Skip")
{
if (this.ParseSkipExpression(m))
{
Expression nextExpression = m.Arguments[0];
return this.Visit(nextExpression);
}
}
else if (m.Method.Name == "OrderBy")
{
if (this.ParseOrderByExpression(m, "ASC"))
{
Expression nextExpression = m.Arguments[0];
return this.Visit(nextExpression);
}
}
else if (m.Method.Name == "OrderByDescending")
{
if (this.ParseOrderByExpression(m, "DESC"))
{
Expression nextExpression = m.Arguments[0];
return this.Visit(nextExpression);
}
}
throw new NotSupportedException(string.Format("The method ''{0}'' is not supported", m.Method.Name));
}
protected override Expression VisitUnary(UnaryExpression u)
{
switch (u.NodeType)
{
case ExpressionType.Not:
sb.Append(" NOT ");
this.Visit(u.Operand);
break;
case ExpressionType.Convert:
this.Visit(u.Operand);
break;
default:
throw new NotSupportedException(string.Format("The unary operator ''{0}'' is not supported", u.NodeType));
}
return u;
}
/// <summary>
///
/// </summary>
/// <param name="b"></param>
/// <returns></returns>
protected override Expression VisitBinary(BinaryExpression b)
{
sb.Append("(");
this.Visit(b.Left);
switch (b.NodeType)
{
case ExpressionType.And:
sb.Append(" AND ");
break;
case ExpressionType.AndAlso:
sb.Append(" AND ");
break;
case ExpressionType.Or:
sb.Append(" OR ");
break;
case ExpressionType.OrElse:
sb.Append(" OR ");
break;
case ExpressionType.Equal:
if (IsNullConstant(b.Right))
{
sb.Append(" IS ");
}
else
{
sb.Append(" = ");
}
break;
case ExpressionType.NotEqual:
if (IsNullConstant(b.Right))
{
sb.Append(" IS NOT ");
}
else
{
sb.Append(" <> ");
}
break;
case ExpressionType.LessThan:
sb.Append(" < ");
break;
case ExpressionType.LessThanOrEqual:
sb.Append(" <= ");
break;
case ExpressionType.GreaterThan:
sb.Append(" > ");
break;
case ExpressionType.GreaterThanOrEqual:
sb.Append(" >= ");
break;
default:
throw new NotSupportedException(string.Format("The binary operator ''{0}'' is not supported", b.NodeType));
}
this.Visit(b.Right);
sb.Append(")");
return b;
}
protected override Expression VisitConstant(ConstantExpression c)
{
IQueryable q = c.Value as IQueryable;
if (q == null && c.Value == null)
{
sb.Append("NULL");
}
else if (q == null)
{
switch (Type.GetTypeCode(c.Value.GetType()))
{
case TypeCode.Boolean:
sb.Append(((bool)c.Value) ? 1 : 0);
break;
case TypeCode.String:
sb.Append("''");
sb.Append(c.Value);
sb.Append("''");
break;
case TypeCode.DateTime:
sb.Append("''");
sb.Append(c.Value);
sb.Append("''");
break;
case TypeCode.Object:
throw new NotSupportedException(string.Format("The constant for ''{0}'' is not supported", c.Value));
default:
sb.Append(c.Value);
break;
}
}
return c;
}
protected override Expression VisitMember(MemberExpression m)
{
if (m.Expression != null && m.Expression.NodeType == ExpressionType.Parameter)
{
sb.Append(m.Member.Name);
return m;
}
throw new NotSupportedException(string.Format("The member ''{0}'' is not supported", m.Member.Name));
}
protected bool IsNullConstant(Expression exp)
{
return (exp.NodeType == ExpressionType.Constant && ((ConstantExpression)exp).Value == null);
}
private bool ParseOrderByExpression(MethodCallExpression expression, string order)
{
UnaryExpression unary = (UnaryExpression)expression.Arguments[1];
LambdaExpression lambdaExpression = (LambdaExpression)unary.Operand;
lambdaExpression = (LambdaExpression)Evaluator.PartialEval(lambdaExpression);
MemberExpression body = lambdaExpression.Body as MemberExpression;
if (body != null)
{
if (string.IsNullOrEmpty(_orderBy))
{
_orderBy = string.Format("{0} {1}", body.Member.Name, order);
}
else
{
_orderBy = string.Format("{0}, {1} {2}", _orderBy, body.Member.Name, order);
}
return true;
}
return false;
}
private bool ParseTakeExpression(MethodCallExpression expression)
{
ConstantExpression sizeExpression = (ConstantExpression)expression.Arguments[1];
int size;
if (int.TryParse(sizeExpression.Value.ToString(), out size))
{
_take = size;
return true;
}
return false;
}
private bool ParseSkipExpression(MethodCallExpression expression)
{
ConstantExpression sizeExpression = (ConstantExpression)expression.Arguments[1];
int size;
if (int.TryParse(sizeExpression.Value.ToString(), out size))
{
_skip = size;
return true;
}
return false;
}
}

Luego visita la expresión llamando:

var translator = new MyQueryTranslator();
string whereClause = translator.Translate(expression);