I am trying to build generic method which would look simething like this:
public static IQueryable<TModel> IncludeByUserCondition<TModel, TIncludable>(this IQueryable<TModel> query, Func<TModel, IQueryable<TIncludable>> includes, List<int> userIDs)
where TModel : class
where TIncludable: class
{
Expression<Func<TModel, object>> result = x => includes(x);
if(typeof(ASqlBase).IsAssignableFrom(typeof(TIncludable)))
{
result = x =>
includes(x)
.Select(prop => prop as ASqlBase)
.Where(prop =>
prop.DeleteDate == null
)
.Where(prop =>
userIDs != null && userIDs.Count > 0 ? userIDs.Contains(prop.IdentityUnitID) : true
)
.Select(prop => prop as TIncludable);
}
query = query.Include(result);
return query;
}
This method would allow me to centrally check if user can read navigation property's value and, if so, include it in result. My applications read rights are conceived in hierarchical way: logged user can read his records and records of all users he had added to the system. Because of that, I cannot determine all visible records in compile-time and, thus, cannot use different database contexts for different groups of users. Also, since this is only one of many ways for filtering data, unfortunately I cannot make use of Global Filters.
I am trying to call the above method like this:
qry = qry.IncludeByUserCondition<AllocatedFund, AllocatedFundDetailPaymentMade>(p => p.AllocatedFundDetailPaymentsMade.AsQueryable(), allowedUserIDs);
However, when I try to invoke it in run-time, I get the following exception:
The expression 'Invoke(__includes_0, x).Select(prop => (prop As ASqlBase)).Where(prop => ((prop.DeleteDate == null))).Where(prop => True).Select(prop => (prop As AllocatedFundDetailPaymentMade))' is invalid inside an 'Include' operation, since it does not represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, use casting ('t => ((Derived)t).MyProperty') or the 'as' operator ('t => (t as Derived).MyProperty'). Collection navigation access can be filtered by composing Where, OrderBy(Descending), ThenBy(Descending), Skip or Take operations.
On another hand, when I try to run query manually, everything works fine:
qry = qry.Include(p =>
p.AllocatedFundDetailPaymentsMade
.AsQueryable()
.Where(prop =>
prop.DeleteDate == null
)
.Where(prop =>
userIDs != null && userIDs.Count > 0 ? userIDs.Contains(prop.IdentityUnitID) : true
)
Since I have more than just one navigation property to include in this manner (and I have to perform query in similar manner for all 30+ other models I use in my application), I wouldn't want to manually write those where clauses in every query. Does anybody know the solution for this problem? Any help would be kindly appreciated.
EDIT:
ASqlBase is just base, abstract class from which some other models interit (although not all of them - ie. User model does not inherit from ASqlBase).
ASqlBase looks like this:
public abstract class ASqlBase
{
[Key]
public int ID { get; set; }
[Required]
public int UserID { get; set; }
[ForeignKey("UserID")]
public virtual User User { get; set; }
public DateTime? DeleteDate { get; set; }
}
I plan to use that function to get data and then display it in report. I'm giving example for Person, then the method call would look something like this:
var qry = dbContext.Person.IncludeByUserCondition<Person, Athlete>(p => p.Athletes.AsQueryable(), athleteAllowedUserIDs);
qry = qry.IncludeByUserCondition<Person, Employee>(p => p.Employees.AsQueryable(), employeeAllowedUserIDs);
qry = qry.IncludeByUserCondition<Person, Student>(p => p.Students.AsQueryable(), studentAllowedUserIDs);
Person model looks something like this:
public class Person : ASqlBase
{
...
public virtual ICollection<Athlete> Athletes { get; set; }
public virtual ICollection<Employee> Employees { get; set; }
public virtual ICollection<Student> Students { get; set; }
}
All of the above models: Athlete, Employee and Student inherit from ASqlBase
EDIT 2:
Sorry for bad method naming, method should be called IncludeByUserCondition, not IncludeMultiple (as it was named before).
A little bit simplified usage. You do not need to call AsQueryable() and explicitly specify generic parameters:
var qry = dbContext.Person.IncludeByUserCondition(p => p.Athletes, athleteAllowedUserIDs);
qry = qry.IncludeByUserCondition(p => p.Employees, employeeAllowedUserIDs);
qry = qry.IncludeByUserCondition(p => p.Students, studentAllowedUserIDs);
And realization:
public static class IncludeExtensions
{
public static IQueryable<TModel> IncludeByUserCondition<TModel, TRelated>(this IQueryable<TModel> query,
Expression<Func<TModel, IEnumerable<TRelated>>> collectionProp, List<int> userIDs)
where TModel : class
where TRelated : ASqlBase
{
var relatedParam = Expression.Parameter(typeof(TRelated), "r");
// r.DeleteDate == null
var filterPredicate = (Expression)Expression.Equal(
Expression.PropertyOrField(relatedParam, nameof(ASqlBase.DeleteDate)),
Expression.Constant(null, typeof(DateTime?)));
if (userIDs?.Count > 0)
{
// r.DeleteDate == null && userIDs.Contains(r.UserID)
filterPredicate = Expression.AndAlso(filterPredicate,
Expression.Call(typeof(Enumerable), nameof(Enumerable.Contains), new[] { typeof(int) },
Expression.Constant(userIDs),
Expression.PropertyOrField(relatedParam, nameof(ASqlBase.UserID))));
}
// r => r.DeleteDate == null && userIDs.Contains(r.UserID)
var filterLambda = Expression.Lambda(filterPredicate, relatedParam);
// p => p.Navigation.Where(r => r.DeleteDate == null && userIDs.Contains(r.UserID))
var transformedProp = Expression.Lambda(Expression.Call(typeof(Enumerable), nameof(Enumerable.Where),
new[] { typeof(TRelated) }, collectionProp.Body, filterLambda), collectionProp.Parameters);
// query.Include(p => p.Navigation.Where(r => r.DeleteDate == null && userIDs.Contains(r.UserID)))
var includeExpression = Expression.Call(typeof(EntityFrameworkQueryableExtensions),
nameof(EntityFrameworkQueryableExtensions.Include),
new[] { typeof(TModel), typeof(IEnumerable<TRelated>) },
query.Expression,
Expression.Quote(transformedProp));
// instantiate new IQueryable<TModel>
var resultQuery = query.Provider.CreateQuery<TModel>(includeExpression);
return resultQuery;
}
}