making a global filter for entity framework
Solution 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.
Solution 2
You can do it like this:
- In OnModelCreating add an IsDeleted discriminator to every entity that can be soft deleted
- Override SaveChanges and find all the entries to be deleted
- Run SQL on these entries to set the IsDeleted discriminator then set their state to "detached"
- 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
Hilmi
Updated on July 25, 2022Comments
-
Hilmi almost 2 years
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 followingin 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 theglobalDefaultScope
in theget
method for that collection that filters the collection but it keeps throwing this exception when some items in the collection are inactiveSystem.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.
-
Hilmi over 10 yearsSir, It will be buggy to use this way, because you overridden the
Where
function, and in my code i useWhere First FirstOrDefault ...
and many others, i'll have to loop over them and override them all, If i miss understood any thing please tell me, Thanks alot sir -
Hilmi over 10 yearsThanks alot sir, your way is nice, but i have to go all over the site and call
WhereActive
method on them, i'll just wait to find more centralized self controlled way to do this, if i didn't find anything (and it seems i won't) i'll choose and mark your answer as the chosen one. thanks again -
CodeCaster over 10 yearsI think this, creating (and promoting) a library which also makes you lose strong-typedness on the property instead of just creating an
IActivatedEntity
interface with abool IsActive
property and putting a type constraint ofwhere T : IActivatedEntity
on theWhereActive()
extension method and applying that interface to your entities, goes a bit far imho. -
Hilmi over 10 yearsI think thats what i was looking for exactly ! but for now its not working with pre-generated views, i'll look for solution, THANKS alot sir
-
xr280xr about 7 yearsHow can you do the same using database first or why isn't it possible?