Use SQL to perform simple and efficient additions and deletions in EF

With the accumulation of platform data, the speed of data access is becoming more and more demanding. Optimizing background access performance will be a key task later.

However, the background in the early stages of project development is the ABP (Lite DDD) framework, integrated with Enityframework. Because previous project experience has worked with EF, it's really efficient for developer coding. But in the traditional industry, the performance requirements for data access are not high. So there's no problem with EF's performance. Then, there is a sentence called "Out of the mix, sooner or later to the". This is not the case with Web projects that are struggling with data access performance, especially when it comes to combining queries spelled out with LINQ and the amount of data is large, the query slows down.

Recently it was also the development of new features while optimizing the query speed by optimizing the syntax. However, these two days after the storage layer call from the API query data of the wonderful performance I was very helpless. In the interface of the new function (using WebSocket real-time driving data), the interface of querying the device information in the background is called, and the Entity data is not found. Then, invoking the same interface can be implemented in other interfaces. I have a lot of doubts about the framework, why?

But now the main task is to deliver the function as soon as possible, I wrote down the question.

In order to implement the interface that calls query device information in the current interface can find the data, I commented out the services layer on the repository layer call Abp.enityframework the API query data, instead of using the organization SQL statement query, delete, Add. The result is normal to get the data you want. Can't help feeling ah, those an ORM is not omnipotent, pure and broken SQL is so simple and efficient.

The following post uses SQL to perform queries, deletions, and additions in EF:

A, Query,

 var  parameter = new  SqlParameter (  "  @DeviceId   "             var  sqldeviceinfo = string . Format (@ " select * FROM dbo. Deviceinfos where [email protected]   "             var  deviceinfo = await  Context.database.sqlquery<deviceinfo> (Sqldeviceinfo, new  SqlParameter (  "  @DeviceId  , id)). Firstordefaultasync (); 

B. Delete

            var New SqlParameter ("@DeviceId", DeviceId);             var string. Format (@ "Delete from dbo. deviceandhiddentroublelinks where [email protected]");             var await Context.Database.ExecuteSqlCommandAsync (Sqldeleteid, Deviceidparamter);

C, New

                varSqladdid =string. Format (@"Insert into dbo. Deviceandhiddentroublelinks (deviceid,devicehiddentrouble_id) VALUES (@DeviceId, @HiddenTroubleId)"); foreach(varHiddentroubleidinchdevicehiddentroubleids) {                    varDeviceidandhiddentroubleidparamter =NewDbparameter[] {NewSqlParameter {parametername ="DeviceId", Value = DeviceId},NewSqlParameter {parametername ="Hiddentroubleid", Value =Hiddentroubleid}}; awaitContext.Database.ExecuteSqlCommandAsync (Sqladdid, Deviceidandhiddentroubleidparamter); }

After the change, found that the direct use of SQL is very cool. It seems that back-end performance optimization is another path.

Use SQL to perform simple and efficient additions and deletions in EF