firstordefault - linq inner join c# entity framework

Agregue dinámicamente la cláusula where en Entity Framework

Question

Tengo esta declaración SQL

SELECT userID from users WHERE
(name=''name1'' AND username=''username1'') OR
(name=''name2'' AND username=''username2'') OR
(name=''name3'' AND username=''username3'') OR
..........
(name=''nameN'' AND username=''usernameN'')

¿Cómo puedo implementar esta declaración con el marco de la entidad utilizando LINQ?

Answer 1

NOTA: esto se modifica de algo que tengo, por lo que podría no funcionar de la caja. Pero sería un buen punto de partida.

 public static IQueryable<TEntity> Where<TEntity>(this IQueryable<TEntity> source,
IEnumerable<WhereSpecifier> orClauses)
where TEntity : class
{
if (!orClauses.Any()) return source.Where(t => false);
Type type = typeof (TEntity);
ParameterExpression parameter = null;
Expression predicate = Expression.Constant(false, typeof (bool));
ParameterExpression whereEnt = Expression.Parameter(type, "WhereEnt");
foreach (WhereSpecifier orClause in orClauses)
{
Expression selector;
if (orClause.Selector != null)
{
selector = orClause.Selector;
parameter = orClause.Parameter;
}
else
{
parameter = whereEnt;
Type selectorResultType;
selector = GenerateSelector<TEntity>(parameter, orClause.Column, out selectorResultType);
}
Expression clause = selector.CallMethod(orClause.Method,
MakeConstant(selector.Type, orClause.Value), orClause.Modifiers);
predicate = Expression.Or(predicate, clause);
}
var lambda = Expression.Lambda(predicate, whereEnt);
var resultExp = Expression.Call(typeof (Queryable), "Where", new[] {type},
source.Expression, Expression.Quote(lambda));
return source.Provider.CreateQuery<TEntity>(resultExp);
}

GenerateSelector:

public static Expression GenerateSelector<TEntity>(ParameterExpression parameter, string propertyName,
out Type resultType) where TEntity : class
{
// create the selector part, but support child properties
PropertyInfo property;
Expression propertyAccess;
if (propertyName.Contains(''.''))
{
// support to be sorted on child fields.
String[] childProperties = propertyName.Split(''.'');
property = typeof (TEntity).GetProperty(childProperties[0]);
propertyAccess = Expression.MakeMemberAccess(parameter, property);
for (int i = 1; i < childProperties.Length; i++)
{
property = property.PropertyType.GetProperty(childProperties[i]);
propertyAccess = Expression.MakeMemberAccess(propertyAccess, property);
}
}
else
{
property = typeof (TEntity).GetProperty(propertyName);
propertyAccess = Expression.MakeMemberAccess(parameter, property);
}
resultType = property.PropertyType;
return propertyAccess;
}

WHereSpecifier:

public class WhereSpecifier
{
public WhereSpecifier(string column, CheckMethod method, string value, CheckMethodModifiers modifiers)
{
Modifiers = modifiers;
Value = value;
Column = column;
Method = method;
}
public WhereSpecifier(string column, CheckMethod method, string value)
: this(column, method, value, CheckMethodModifiers.None)
{
}
public Expression Selector { get; set; }
public ParameterExpression Parameter { get; set; }
public string Column { get; set; }
public CheckMethod Method { get; set; }
public CheckMethodModifiers Modifiers { get; set; }
public string Value { get; set; }
}

Uso:

var column = typeof(TEntity).Name + "ID";
var where = from id in SelectedIds
select new WhereSpecifier(column, CheckMethod.Equal, id.ToString());
return GetTable().Where(where);

Answer 2

No olvide que la estructura de la entidad también comprende la entidad sql , por lo que puede hacer esta parte de la consulta en una cadena. Crear una cadena es bastante conveniente cuando tienes cosas dinámicas que debes hacer.

Answer 3

Probé la solución @Egor Pavlikhin pero obtuve "The LINQ expression node type ''Invoke'' is not supported in LINQ to Entities." .

De acuerdo a this puedes usar PredicateExtensions :

var predicate = PredicateExtensions.Begin<User>();
foreach (var name in names)
{
pr = pr.Or(x => x.Name == name);
}
return _context.Users.Where(predicate);

Answer 4

Puedes usar una cosa hermosa llamada PredicateBuilder . Usalo asi

var pr = PredicateBuilder.False<User>();
foreach (var name in names)
{
pr = pr.Or(x => x.Name == name && x.Username == name);
}
return query.AsExpandable().Where(pr);

Answer 5

Tuve que construir el predicado para la cláusula ''Dónde'' de forma dinámica en función de las selecciones de la interfaz de usuario. ''System.Dynamic.Linq'' permite predicados a partir de cadenas.

foreach (var name in names)
{
query = query.Where("Name=@0 And UserName=@1", name, name);
}
return query;

''System.Dynamic.Linq'' está disponible como un paquete nuget. Echa un vistazo a la introducción de Scott Guthrie al tema here .

Answer 6

 Expression<Func<User, bool>> whereExpression = null;
foreach (var name in names)
{
Expression<Func<User, bool>> e1 = u => u.Name == name;
Expression<Func<User, bool>> andExpression = e1.And(u => u.Username == name);
whereExpression = whereExpression == null ? andExpression : whereExpression.Or(andExpression);
}
return query.Where(whereExpression);

Este ayudante puede ayudarte.

public static class ExpressionExtensions
{
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> leftExpression, Expression<Func<T, bool>> rightExpression)
{
if (leftExpression == null) return rightExpression;
if (rightExpression == null) return leftExpression;
var paramExpr = Expression.Parameter(typeof(T));
var exprBody = Expression.And(leftExpression.Body, rightExpression.Body);
exprBody = (BinaryExpression)new ParameterReplacer(paramExpr).Visit(exprBody);
return Expression.Lambda<Func<T, bool>>(exprBody, paramExpr);
}
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> leftExpression, Expression<Func<T, bool>> rightExpression)
{
if (leftExpression == null) return rightExpression;
if (rightExpression == null) return leftExpression;
var paramExpr = Expression.Parameter(typeof(T));
var exprBody = Expression.Or(leftExpression.Body, rightExpression.Body);
exprBody = (BinaryExpression)new ParameterReplacer(paramExpr).Visit(exprBody);
return Expression.Lambda<Func<T, bool>>(exprBody, paramExpr);
}
}
class ParameterReplacer : ExpressionVisitor
{
private readonly ParameterExpression _parameter;
protected override Expression VisitParameter(ParameterExpression node)
{
return base.VisitParameter(_parameter);
}
internal ParameterReplacer(ParameterExpression parameter)
{
_parameter = parameter;
}
}