EDITED:
I'm trying to create a query using Expression Trees so that I can obtain a simple result, but I haven't been able of figuring it out.
I've got some entities from EF. Entity PointOfSale , that has some attributes like: Location (that is a child of Region) TypeOfClient ImportanceOfPOS QuantityOfSales.
Then I have a class Filter, that has a list of Regions, a list of TypesOfClients, a Dist of ImportanceOfPOS, and a minimum and maximum value for the quantity of sales.
The classes are defined below:
Entities context = new Entities();
class PointOfSale
{
int p_id;
Location l;
QuantityOfSales q;
TypeOfCliente t;
ImportanceOfClient i;
int l_id_fk;
int t_id_fk;
int i_id_fk;
}
class Location
{
int id;
int region_id_fk;
}
class Region
{
int id;
string value;
}
class ValuesForQuantity
{
int p_id; // ---> Equal to the POS_id
int? min_value;
int? max_value;
}
class QuantityOfSales
{
int id;
int value;
}
class TypeOfCliente
{
int id;
string value;
}
class ImportanceOfClient;
{
int id;
string value;
}
class Filter
{
List<Region> rs;
ValuesForQuantity v;
List<ImportanceOfClient> is;
List<TypeOfClient> ts;
}
Keep in mind that all the fields are optional: I may or may not have regions, I may or may not have TypeOfClient. Also, in ValueForQuantities, I may have just one of the fields chosen (just maximum or minimum).
I'm trying to build a dynamic version of this:
var query =
from p in context.PointsOfSale
where p.i exists in filter.is &&
p.t exists in filter.ts &&
p.l in (from l1 in context.Locations where l1 in filter.rs select r) &&
p.t in (from q in context.Quantities where q.value < filter.v.max_value && q.value > filter.v.max_value)
select p;
Which will generate a code like this:
(p.i.id == filter.i[0] OR p.i.id == filter.i[1] OR p.i.id == filter.i[2])
AND
(p.t.id == filter.t[0] OR p.t.id == filter.t[1])
AND
p.l in (select id from Region r where (r.id == filter.r[0] OR r.id == filter.r[1])
AND
p.q.value < filter.v.max
AND
p.q.value > filter.v.min
My expression tree so far is this:
IQueryable<Points_of_sale> queryableDataPOS = context.Points_of_sale.AsQueryable<Points_of_sale>();
ParameterExpression pePOS = Expression.Parameter(typeof(Points_of_sale), "point_of_sale");
ParameterExpression peLocation = Expression.Parameter(typeof(Location), "location");
List<Expression> expressions = new List<Expression>();
if (showRegion)
{
List<Location> ls = getAllLocations(regionList);
List<Expression> choiceExpressions = new List<Expression>();
foreach (Location l in ls)
{
Expression left = Expression.Property(pePOS, typeof(Points_of_sale).GetProperty("location_id_fk"));
left = Expression.Convert(left, t.location_id.GetType());
Expression right = Expression.Constant(t.territory_id);
Expression expression = Expression.Equal(left, right);
choiceExpressions.Add(expression);
}
if (choiceExpressions.Count > 0)
{
Expression totalChoiceExpression = choiceExpressions[0];
for (int i = 1; i < choiceExpressions.Count; i++)
{
totalChoiceExpression = Expression.Or(totalChoiceExpression, choiceExpressions[i]);
}
expressions.Add(totalChoiceExpression);
}
}
if (showTypeOfClient)
{
List<Expression> choiceExpressions = new List<Expression>();
foreach (TypeOfClient choice in clients)
{
Expression left = Expression.Property(pePOS, typeof(Points_of_sale).GetProperty("type_of_client_id_fk"));
left = Expression.Convert(left, choice.type_of_client.GetType());
Expression right = Expression.Constant(choice.type_of_client_id);
Expression expression = Expression.Equal(left, right);
choiceExpressions.Add(expression);
}
if (choiceExpressions.Count > 0)
{
Expression totalChoiceExpression = choiceExpressions[0];
for (int i = 1; i < choiceExpressions.Count; i++)
{
totalChoiceExpression = Expression.Or(totalChoiceExpression, choiceExpressions[i]);
}
expressions.Add(totalChoiceExpression);
}
}
if (showImportanceOfClient)
{
List<Expression> choiceExpressions = new List<Expression>();
foreach (ImportanceOfClient choice in importanceOfClients)
{
Expression left = Expression.Property(pePOS, typeof(Points_of_sale).GetProperty("importance_of_client_id_fk"));
left = Expression.Convert(left, choice.importance_of_client_id.GetType());
Expression right = Expression.Constant(choice.importance_of_client_id);
Expression expression = Expression.Equal(left, right);
choiceExpressions.Add(expression);
}
if (choiceExpressions.Count > 0)
{
Expression totalChoiceExpression = choiceExpressions[0];
for (int i = 1; i < choiceExpressions.Count; i++)
{
totalChoiceExpression = Expression.Or(totalChoiceExpression, choiceExpressions[i]);
}
expressions.Add(totalChoiceExpression);
}
}
if (showQuantityOfSales)
{
// I have no idea how to build this one
}
Expression totalQuery = expressions[0];
// Make the && between all expressions
for (int i = 1; i < expressions.Count; i++)
{
totalQuery = Expression.And(totalQuery, expressions[i]);
}
MethodCallExpression whereCallExpression = Expression.Call(
typeof(Queryable),
"Where",
new Type[] { queryableDataPOS.ElementType },
queryableDataPOS.Expression,
Expression.Lambda<Func<Points_of_sale, bool>>(totalQuery, new ParameterExpression[] { pePOS }));
IQueryable<Points_of_sale> results = queryableDataPOS.Provider.CreateQuery<Points_of_sale>(whereCallExpression);
So, 2 questions:
1 - How can I build the region part without the method that returns all the locations. 2 - How can I create the expression tree branch for the Quantity part?
Tnks a lot and I hope it's now clear :)
First, build a LambdaExpression from your expression "sub". Then build the "Contains" call (actually it is a call to Queryable.Any):
var anyExpression = Expression.Call("Queryable.Any", sequence, lambda);
You are building this expression:
Queryable.Any(sequence, x => x > 5);
Edit: It looks like you don't need to build an expression tree at all. What about this:
var query = context.P_List;
if(filter.zs != null && filter.zs.Count != 0)
query = query.Where(p => filter.zs.Contains<int>(p.z));
if(filter.ys != null && filter.ys.Count != 0)
query = query.Where(p => filter.ys.Contains<int>(p.y));
if(filter.as != null && filter.as.Count != 0)
query = query.Where(p => (from ac in context.a_child where filter.as.Contains(ac) select a).Contains(p.a_child));
//and the same for v2...
var list = query.ToList(); //execute the SQL
I hope that works.