Expression Tree search using contains

c# contains expression-trees foreign-keys

Question

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 :)

Popular Answer

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why