What is the best way to create strongly typed LINQ queries from some given strings, via reflection

Question

I'm using EF5, unit of work, and repository pattern. I want to define some limitations on accessing data for specified users. In database I've designed a table to keep my entity names and their properties which is called EntityProperties, and another table to keep the values of those properties which is called PropertyValues, and each EntityProperty has one or more PropertyValues. In business layer when user requests data, if any limitation is defined for him, some conditions should be added to the linq statement. What I do is to get the list of entity names and their propeties and values by 'userId', then I add some 'Where' clause to the linq query. However, the entity names and their properties are of type "String", thus I should use Reflection to manage them. But I don't know this part, and I don't know how to create LINQ where clause from a given set of condition strings. For example, let's suppose that a user requests the list orders, and user id is 5. I first query those access limitation tables, and the result is:

"Order", "Id", "74"

"Order", "Id", "77"

"Order", "Id", "115"

It means that this user should only see these three orders, while in Orders table, we have more orders. So, if I want to use a LINQ query to get orders, like:

var orders = from order in Context.Orders

I need to turn it into something like:

var orders = from order in Context.Orders

// where order id should be in 74,77,115

However, getting to Order entity and Id property from "Order" and "Id" strings requires reflection. Thus two questions:

What is the best way to get strongly typed from strings? Is there a better way for me to do this, with better performance?

Answer 1

Ok. With the comments, we might go for something like that (assuming you have a navigation property in EntityProperties table, which is a collection of PropertyValues, and named PropertyValueList (if you don't have, just do a join instead of using Include).

Here is some sample code, really rustic, working only with Int32 properties, but this might be the start of a solution.

You may also look at PredicateBuilder...

Anyway

I use an "intermediate class" Filter.

public class Filter
    {
        public string PropertyName { get; set; }
        public List<string> Values { get; set; }
    }

Then an helper class, which will return an IQueryable<T>, but... filtered

public static class FilterHelper {

    public static IQueryable<T> Filter(this IQueryable<T> queryable, Context context, int userId) {
        var entityName = typeof(T).Name;
        //get all filters for the current entity by userId, Select the needed values as a `List<Filter>()`
        //this could be done out of this method and used as a parameter
        var filters = context.EntityProperties
                      .Where(m => m.entityName == entityName && m.userId = userId)
                      .Include(m => m.PropertyValueList)
                      .Select(m => new Filter {
                          PropertyName = m.property,
                          Values = m.PropertyValueList.Select(x => x.value).ToList()
                      })
                      .ToList();

        //build the expression
        var parameter = Expression.Parameter(typeof(T), "m");

        var member = GetContains( filters.First(), parameter);
        member = filters.Skip(1).Aggregate(member, (current, filter) => Expression.And(current, GetContains(filter, parameter)));
        //the final predicate
        var lambda = Expression.Lambda<Func<T, bool>>(member, new[] { parameter });
        //use Where with the final predicate on your Queryable
        return queryable.Where(lambda);
    }

//this will build the "Contains" part
private static Expression GetContains(Filter filter, Expression expression)
    {
        Expression member = expression;
        member = Expression.Property(member, filter.PropertyName);
        var values = filter.Values.Select(m => Convert.ToInt32(m));

        var containsMethod = typeof(Enumerable).GetMethods().Single(
            method => method.Name == "Contains"
                      && method.IsGenericMethodDefinition
                      && method.GetParameters().Length == 2)
                      .MakeGenericMethod(new[] { typeof(int) });
        member = Expression.Call(containsMethod, Expression.Constant(values), member);
        return member;
    }
}

usage

var orders = from order in Context.Orders
             select order;

var filteredOrders = orders.Filter(Context, 1);//where 1 is a userId