entity framework core 调用存储过程和方法

　　目前EF Core调用存储过程，限制很多，比如返回结果必须是定义好的DbSet<>等等。这里用一种曲线救国的方式,自定义两个方法，用原始ado.net解决问题。以MySql数据库为例，代码如下：

 1 namespace WebApi.Core.Data
 2 {
 3     using System.Data;
 4     using System.Linq;
 5 
 6     using Microsoft.EntityFrameworkCore;
 7 
 8     using MySql.Data.MySqlClient;
 9 
10     public class TestContext : DbContext
11     {
12         public TestContext(DbContextOptions<TestContext> options)
13             : base(options)
14         {
15         }
16 
17         public int ExcuteProcedure(string parcedureName, params MySqlParameter[] ps)
18         {
19             using (var cmd = this.Database.GetDbConnection().CreateCommand())
20             {
21                 cmd.CommandText = parcedureName;
22                 cmd.CommandType = CommandType.StoredProcedure;
23                 cmd.Parameters.AddRange(ps);
24                 this.Database.OpenConnection();
25                 var r = cmd.ExecuteNonQuery();
26                 this.Database.CloseConnection();
27                 return r;
28             }
29         }
30 
31         public object ExcuteFunction(string functionName, params MySqlParameter[] ps)
32         {
33             using (var cmd = this.Database.GetDbConnection().CreateCommand())
34             {
35                 // 参数字符串，传参时注意顺序
36                 var pString =
37                     $"'{ps.Aggregate(string.Empty, (c, n) => $"{c}','{n.Value}").TrimStart(new[] { ',', '\'' })}'";
38 
39                 // 可用this.Database.IsXXX()，判读数据库类型，拼command字符串，这里不写了
40                 cmd.CommandText = $"select {functionName}({pString});";
41                 this.Database.OpenConnection();
42                 var r = cmd.ExecuteScalar();
43                 this.Database.CloseConnection();
44                 return r;
45             }
46         }
47 
48         protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
49         {
50         }
51     }
52 }

 

转载于:https://www.cnblogs.com/David-Huang/p/7609993.html