making a global filter for entity framework

Question

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 => m.active);
    }
    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

Update:

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

Update2:

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() 
            //GlobalDefaultScope(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 {
get 
{
   //something like:
   return GlobalDefaultScope(_sub_relation1 ).ToList(); 
}  
set;}

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

Answer 1

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

or

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

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

Answer 2

You can do it like this:

1. In OnModelCreating add an IsDeleted discriminator to every entity that can be soft deleted
2. Override SaveChanges and find all the entries to be deleted
3. Run SQL on these entries to set the IsDeleted discriminator then set their state to "detached"
4. Change any unique indexes to ignore any soft deleted records

You can find working code at this answer: How to soft delete using Entity Framework Code First