Implement a simple global filtering query from the EF core in ASP.

Objective

I do not know if you and I have the same problem:

Generally in the database design phase, there will be some default rules, there is a hard rule is not to do any table data delete hard delete operations, because each piece of data is useful to us, and is worth analyzing.

So we usually add a "whether delete isdeleted" or "Valid IsValid" field in each table to identify the status of this data is available!

So the question is, are we going to add this condition or ignore it when writing SQL or LINQ? The answer, of course, depends on the actual business needs and circumstances. For example, a commodity, on the shelf, it must be effective and for customers to buy, but one day was informed of the next (deleted), then in the customer's order list you also want to display for customers to view!

But then again, I think most of the time when the query we will filter out these invalid data, so every SQL or LINQ there are ubiquitous isdeleted=0 similar to the conditions, and sometimes we will accidentally forget this condition in the brain. So is there a way to solve this problem once and for all, or more easily? Then the protagonist EF Core is on the pitch!

1. Use the Global Filter query function with EF core

This is very simple to use, just to set the ModelBuilder on the table entities that need to be globally filtered in onmodelcreating. Prepare a deleted and not deleted data in the System User's table first.

    /// <summary>    ///System Context/// </summary>     Public classLightcontext:dbcontext { PublicLightcontext (dbcontextoptions<lightcontext> options):Base(options) {}protected Override voidonmodelcreating (ModelBuilder ModelBuilder) {modelbuilder.entity<ApplicationUser> (M ={m.property (t=t.email). Hasmaxlength ( -); M.property (t=t.username). IsRequired (). Hasmaxlength ( -); M.property (t=T.password). IsRequired (). Hasmaxlength ( -); m.hasqueryfilter (n = =! n.isdeleted);//The default query is not deleted by the user }); Base.        Onmodelcreating (ModelBuilder); }        /// <summary>        ///System Application User/// </summary>         PublicDbset<applicationuser> ApplicationUser {Get;Set; } }

Run the program and then request the user interface, then the result is that we only get id=1 data, id=2 data has reached our expectations are filtered out!

Analysis: Although the above approach has achieved results, it is not flexible enough. If I want to query all the data at this time, then this global filter is deleted, and if I have to query the deleted data, but also to change the code!

Therefore, every time we query, we need to accept a condition to identify the validity of the queried data, and pass this condition parameter to the database context DbContext, dynamically to get the data we want!

2. Accept global filtering parameters in ASP.

First of all, we want to dynamically accept a "delete" parameter object in the service configuration item by using the request context HttpContext, we temporarily define this parameter as "D", meaning: 0: not deleted, 1: deleted, 2: All, the same default query all the data not deleted

This parameter is then passed in with the constructor of the database context DbContext, taking into account the GET request and the POST request, the final code is as follows:

 Public voidconfigureservices (iservicecollection services) {//ADD DbContext//Services. adddbcontext<lightcontext> (options =//options. Usesqlserver (configuration.getconnectionstring ("Lightconnection" ));Services. AddTransient<LightContext> (factory =            {                varBuilder =NewDbcontextoptionsbuilder<lightcontext>(); Builder. Usesqlserver (Configuration.getconnectionstring ("lightconnection")); varaccessor = factory. Getservice<ihttpcontextaccessor>(); BOOL? isDeleted =false;//Default global query for data that is not deleted                if(Accessor. HttpContext! =NULL)                {                    stringMETHOD =accessor.                    HttpContext.Request.Method.ToLower (); Stringvalues queryisdeleted=Stringvalues.empty; if(Method = ="Get") {queryisdeleted= Accessor. httpcontext.request.query["D"];                    }                    Else if(Method = ="Post"&&accessor. HttpContext.Request.HasFormContentType) {queryisdeleted= Accessor. httpcontext.request.form["D"]; }                    if(!Stringvalues.isnullorempty (queryisdeleted)) {                        intIsdeletedint =0;//0: Not deleted, 1: deleted, 2: All                        if(int. TryParse (Queryisdeleted.firstordefault (), outisdeletedint)) {                            if(Isdeletedint = =0) {isDeleted=false; }                            Else if(Isdeletedint = =1) {isDeleted=true; }                            Else if(Isdeletedint = =2) {isDeleted=NULL; }                        }                    }                }                return NewLightcontext (builder.            Options, isDeleted);        }); }

3. Add custom filters to EF core warehousing

Next, add a isdeleted query condition to the database context DbContext and privatize the assignment operation, simply by assigning the constructor function. The code changes are as follows:

    /// <summary>    ///System Context/// </summary>     Public classLightcontext:dbcontext { Public BOOL? IsDeleted {Get;Private Set; }//prohibit assignment of isdeleted to the outside world, limit the assignment of values in constructors         PublicLightcontext (dbcontextoptions<lightcontext> options,BOOL? isDeleted =false) :Base(options) {IsDeleted=isDeleted; }    }

Then this condition can be used in our EF warehousing module, according to our actual needs can be different conditional query, part of the code is as follows:

    /// <summary>    ///EF implements warehousing interfaces/// </summary>    /// <typeparam name= "T" >Entity</typeparam>     Public classEfrepository<t>: Irepository<t>, irepositoryasync<t>whereT:basemodel {protected ReadOnlyLightcontext _lightcontext;  Publicefrepository (Lightcontext lightcontext) {_lightcontext=Lightcontext; }         PublicT GetById (intID) {if(_lightcontext.isdeleted.hasvalue) {return_lightcontext.set<t> (). Where (m = = m.isdeleted = = _lightcontext.isdeleted.value). FirstOrDefault (m = m.id = =ID); }            return_lightcontext.set<t> (). FirstOrDefault (m = m.id = =ID); }         Public AsyncTask<t> Getbyidasync (intID) {if(_lightcontext.isdeleted.hasvalue) {return await_lightcontext.set<t> (). Where (m = = m.isdeleted = = _lightcontext.isdeleted.value). Firstordefaultasync (m = m.id = =ID); }            return await_lightcontext.set<t> (). Firstordefaultasync (m = m.id = =ID); }         PublicIenumerable<t>GetList () {if(_lightcontext.isdeleted.hasvalue) {return_lightcontext.set<t> (). Where (m = m.isdeleted = =_lightcontext.isdeleted.value).            ToList (); }            return_lightcontext.set<t>().        ToList (); }         Public AsyncTask<ienumerable<t>>Getlistasync () {if (_lightcontext.isdeleted.hasvalue) {return await _lightcontext.set<t> (). Where (m = = m.isdeleted = = _lightcontext.isdeleted.value).            Tolistasync (); }            return await_lightcontext.set<t>().        Tolistasync (); }    }

Finally, the filter query that comes with EF core can be completely omitted.

// m.hasqueryfilter (n =!n.isdeleted);

At this point the entire adjustment has been completed, although seemingly simple, but the feeling is quite practical, also if you need other general filter conditions, such as time, can be added as appropriate! The final effect is as follows:

4. Finally

A little progress every day, is a very happy thing! Wish you a happy new Year in advance:)

Implement a simple global filtering query from the EF core in ASP.