EF and LINQ call stored procedures, eflinq stored procedures

EF and LINQ call stored procedures, eflinq stored procedures

I haven't updated my article for a long time. Recently, my project is busy and I have no time to share my recent questions. Today, I encountered a very stupid problem. In C #, the stored procedure is called and its own code is also coming 10 years. This should be a very simple problem. Today there are two new APIs, one with only one parameter and one with more than 10 parameters. We didn't notice the object type before, and thought it was EF's DbContext, the result was later found to be the DataContext object of LINQ. Previously, stored procedures were encapsulated into methods based on the design interface. Now there are more than 500 tables on the designer interface, which has not been maintained for a few years. To modify anything, you can directly change the code. So here we will use code to call the stored procedure as an example.

EF calls the Stored Procedure

  using (AdventureWorks2014Entities aw=new AdventureWorks2014Entities())            {                int ret = aw.Database.ExecuteSqlCommand("EXEC [HumanResources].[uspUpdateEmployeeLogin] @BusinessEntityID,@OrganizationNode,@LoginID,@JobTitle,@HireDate,@CurrentFlag ",                    new SqlParameter("@BusinessEntityID",10),                    new SqlParameter("OrganizationNode",DBNull.Value),                    new SqlParameter("LoginID", @"adventure-works\michael6"),                    new SqlParameter("JobTitle", "Research and Development Manager23"),                    new SqlParameter("HireDate", DateTime.Now),                    new SqlParameter("CurrentFlag",true)                    );            }

Note that the first parameter of ExecuteSqlCommand here is a string, which must contain parameters, for example, @ BusinessEntityID. The parameter name in SqlParameter here can contain the @ symbol or not.

Call the stored procedure using LINQ

  using (AWDataClassesDataContext aw=new AWDataClassesDataContext())            {                int BusinessEntityID=10;                string OrganizationNode = null; //0x5AE178                string LoginID=@"adventure-works\michael6";                string JobTitle="Research and Development Manager12";                DateTime HireDate=DateTime.Now;                bool CurrentFlag=true;                string sql = string.Format(" EXEC [HumanResources].[uspUpdateEmployeeLogin] @BusinessEntityID={0},@OrganizationNode={1},@LoginID=N'{2}',@JobTitle=N'{3}',@HireDate=N'{4}',@CurrentFlag=N'{5}'"                    , BusinessEntityID.ToString(), "NULL", LoginID.ToString(), JobTitle.ToString(), HireDate.ToString(), CurrentFlag.ToString());                var ret = aw.ExecuteCommand(sql);                //var ret=aw.ExecuteCommand("EXEC [HumanResources].[uspUpdateEmployeeLogin] @BusinessEntityID={0},@OrganizationNode={1},@LoginID={2},@JobTitle={3},@HireDate={4},@CurrentFlag={5}"                //    , BusinessEntityID, null, LoginID, JobTitle, HireDate, CurrentFlag);             } 

The parameter passed after the ExecuteCommand method cannot be an object, so I can only splice SQL strings here. You may notice that the stored procedure called here is no different from the ordinary SQL statement, except that its SQL calls the stored procedure as follows:
EXEC [HumanResources]. [uspUpdateEmployeeLogin] @ BusinessEntityID = 10, @ OrganizationNode = NULL, @ LoginID = n' adventure-works \ michael6 ', @ JobTitle = n' Research and Development Manager ', @ HireDate = n' 2015/6/29 22:30:15 ', @ CurrentFlag = n' true'
Such SQL statements are dangerous (possibly due to SQL injection ). If the parameter may be Null, the SQL statement must be dynamically spliced, which is much more troublesome than writing new SqlParameter ("OrganizationNode", DBNull. Value. I used the decompilation tool and did not see the specific implementation in DataContext.
It seems that it is time to eliminate the use of LINQ to SQL.