making a global filter for entity framework c# entity-framework linq


For my models I have a active attribute on all of them, and i want to filter all inactive if the model was not displayed on the administration What is the best way to do that, What I'm currently using is the following

in my base model class i have this method that filters the collections

public virtual IQueryable<T> GlobalDefaultScope<T>(IQueryable<T> c) where T : CModel<T>
    if (settings.is_admin)
        c = c.Where(m =>;
    return c;

and on my model for every relation i did the following method

DbSet<T> set ...
var X = set.Where(some filter);
var list = globalDefaultScope(X).ToList();
return list;

And now I'm having some serious problems when i want to eagerly load some subrelations using include("Xmodel.Ymodel") i called the globalDefaultScope in the get method for that collection that filters the collection but it keeps throwing this exception when some items in the collection are inactive

System.InvalidOperationException: The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable.

how can i fix this or how can i make this filter in a more elegant way because i'm really not very satisfied of how i implemented it.

please ask for Any missing information or code blocks or any details


I also found this link, but this way didn't work with eagerly loaded entries (include())


this is an example of how i use the include and where the error occurs

In My Model

public IQueryable<Dish> getSomeRelation(bool eagerly_load_sub_relation1, bool eagerly_load_sub_relation2)
            var query = getQuery(...);
            //getQuery =>  query = db.Entry(obj).Collection(collection).Query() 
            if ( eagerly_load_sub_relation1){
                query = query.Include(m => m.sub_relation1);
            if (eagerly_load_sub_relation2){
                query = query.Include("sub_relation2.sub_relation_of_sub_relation2");
            return query;

while i couldn't filter the relations in the include i did the following :

private ICollection<SubRelation1> _sub_relation1 {get; set;}
public ICollection<SubRelation1> sub_relation1 {
   //something like:
   return GlobalDefaultScope(_sub_relation1 ).ToList(); 

while we filter the results in sub_relation1, when i do db.SaveChanges() the mentioned error is thrown.

9/13/2013 12:02:48 AM

Popular Answer

You can create extension methods like

public static IQueryable<T> Where<T>(this IQueryable<T> source, Expression<Func<T, bool>> predicate)
    source = source.Where("isActive == true"); // From System.linq.Dynamic Library
    source = Queryable.Where<T>(source, predicate);
    return source;

and use them like

var user = db.UserProfiles.Include("webpages_Roles").Where(u => u.UserId < 30);

This should work, Please Let me know if you need more information.

Note : Please Add Nuget package for System.Linq.Dynamic to have dynamic Linq Library

Update 1:

I have included IsActive in webpages_Roles and UsreProfile table of simple membership and also included one more table Role Priority which refers to webpages_Roles, for easy to use I have changed the all the relation ships to one to many(from many to many). now if I use code similar to yours, extension method gives error when there is any user for which there are no roles.

If I give roles to every user and I have priority for every roles then this won't give error.

Please Let me know if you still need any other info.

Update 2

You can create one extension Method like

public static IQueryable<T> WhereActive<T>(this IQueryable<T> source)
            return source.Where("isActive == true"); // From System.linq.Dynamic Library

and call other methods like

var user = db.UserProfiles.Include("webpages_Roles").WhereActive().Where(u => u.UserId < 30);


var user = db.UserProfiles.Include("webpages_Roles").WhereActive().FirstOrDefault(u => u.UserId < 30);

Please Let me know if you still need any other info.

12/16/2013 10:40:12 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow