EFCore执行Sql语句的方法:FromSql与ExecuteSqlCommand
前言
在EFCore中执行Sql语句的方法为:FromSql与ExecuteSqlCommand;在EF6中的为SqlQuery与ExecuteSqlCommand,而FromSql和SqlQuery有很大区别,FromSql返回值为IQueryable,因此为延迟加载的,可以与Linq扩展方法配合使用,但是有不少的坑(EFCore版本为1.1.0),直接执行Sql语句的建议不要使用FromSql,但是EFCore中并没有提供SqlQuery方法,因此下面会贴出SqlQuery的实现代码供大家参考,以便在EFCore中能使用。
FromSql和ExecuteSqlCommand的使用
测试时使用了SqlServer2008和SqlServer Profiler进行Sql语句捕捉,EFCore的版本为1.1.0。
测试的Entity Model与DbContext
1 public class MSSqlDBContext : DbContext 2 { 3 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 4 { 5 optionsBuilder.UseSqlServer(@"data source=localhost;initial catalog=TestDB;Integrated Security=True;"); 6 } 7 public DbSet<Person> Person { get; set; } 8 public DbSet<Address> Address { get; set; } 9 } 10 11 [Table(nameof(Person))] 12 public class Person 13 { 14 public int id { get; set; } 15 public string name { get; set; } 16 [Column(TypeName = "datetime")] 17 public DateTime? birthday { get; set; } 18 public int? addrid { get; set; } 19 } 20 21 [Table(nameof(Address))] 22 public class Address 23 { 24 public int id { get; set; } 25 public string fullAddress { get; set; } 26 public double? lat { get; set; } 27 public double? lon { get; set; } 28 }
ExecuteSqlCommand
EFCore的ExecuteSqlCommand和EF6的一样,执行非查询的Sql语句:
1 var db = new MSSqlDBContext(); 2 db.Database.ExecuteSqlCommand($"update {nameof(Person)} set name=@name where id=@id", new[] 3 { 4 new SqlParameter("name", "tom1"), 5 new SqlParameter("id", 1), 6 });
FromSql
官方参考文档:https://docs.microsoft.com/en-us/ef/core/querying/raw-sql
简单使用
1 var db = new MSSqlDBContext(); 2 var name = "tom"; 3 var list = db.Set<Person>().FromSql($"select * from {nameof(Person)} where {nameof(name)}=@{nameof(name)} ", 4 new SqlParameter(nameof(name), name)).ToList();
生成的Sql:
exec sp_executesql N'select * from Person where name=@name ',N'@name nvarchar(3)',@name=N'tom'
注意:
默认生成的为Person的Model,如果Select获取的字段中不包含Person中的某字段就会抛异常了,例如:下面的语句只获取name字段,并没有包含Person的其他字段,那么抛异常:The required column 'id' was not present in the results of a 'FromSql' operation.
db.Set<Person>().FromSql($"select name from {nameof(Person)} ").ToList();
那么改为:
db.Set<Person>().Select(l => l.name).FromSql($"select name from {nameof(Person)} ").ToList();
执行存储过程
1 var db = new MSSqlDBContext(); 2 db.Set<Person>().FromSql("exec testproc @id", new SqlParameter("id", 1)).ToList();
生成的Sql:
exec sp_executesql N'exec testproc @id ',N'@id int',@id=1
与Linq扩展方法配合使用
1 var db = new MSSqlDBContext(); 2 db.Set<Person>().FromSql($"select * from {nameof(Person)} where name=@name ", new SqlParameter("@name", "tom")) 3 .Select(l => new { l.name, l.birthday }).ToList();
生成的Sql:
exec sp_executesql N'SELECT [l].[name], [l].[birthday] FROM ( select * from Person where name=@name ) AS [l]',N'@name nvarchar(3)',@name=N'tom'
inner join + order by
1 var db = new MSSqlDBContext(); 2 (from p in db.Set<Person>().FromSql($"select * from {nameof(Person)} ") 3 join a in db.Set<Address>().Where(l => true) 4 on p.addrid equals a.id 5 select new { p.id, p.name, a.fullAddress }).OrderBy(l => l.id).ToList();
生成的Sql:
SELECT [p].[id], [p].[name], [t].[fullAddress] FROM ( select * from Person ) AS [p] INNER JOIN ( SELECT [l0].* FROM [Address] AS [l0] ) AS [t] ON [p].[addrid] = [t].[id] ORDER BY [p].[id]
left join + order by
1 var db = new MSSqlDBContext(); 2 (from p in db.Set<Person>().FromSql($"select * from {nameof(Person)} ") 3 join a in db.Set<Address>().Where(l => true) 4 on p.addrid equals a.id into alist 5 from a in alist.DefaultIfEmpty() 6 select new { p.id, p.name, fullAddress = a == null ? null : a.fullAddress }).OrderBy(l => l.id).ToList();
生成的Sql:(生成的Sql很有问题,order by后面多了[p].[addrid],而且生成的select的字段也是多了)
SELECT [p].[id], [p].[addrid], [p].[birthday], [p].[name], [t].[id], [t].[fullAddress], [t].[lat], [t].[lon] FROM ( select * from Person ) AS [p] LEFT JOIN ( SELECT [l0].[id], [l0].[fullAddress], [l0].[lat], [l0].[lon] FROM [Address] AS [l0] ) AS [t] ON [p].[addrid] = [t].[id] ORDER BY [p].[id], [p].[addrid]
将FromSql换成Where扩展方法试试:
1 (from p in db.Set<Person>().Where(l => true) 2 join a in db.Set<Address>().Where(l => true) 3 on p.addrid equals a.id into alist 4 from a in alist.DefaultIfEmpty() 5 select new { p.id, p.name, fullAddress = a == null ? null : a.fullAddress }).OrderBy(l => l.id).ToList();
EFCore生成的Sql(order by后面还是多了[addrid],select的字段也是多了):
SELECT [l].[id], [l].[addrid], [l].[birthday], [l].[name], [t].[id], [t].[fullAddress], [t].[lat], [t].[lon] FROM [Person] AS [l] LEFT JOIN ( SELECT [l1].[id], [l1].[fullAddress], [l1].[lat], [l1].[lon] FROM [Address] AS [l1] ) AS [t] ON [l].[addrid] = [t].[id] ORDER BY [l].[id], [l].[addrid]
而在EF6中生成的Sql,比EFCore的生成好多了:
SELECT [Project1].[id] AS [id], [Project1].[name] AS [name], [Project1].[C1] AS [C1] FROM ( SELECT [Extent1].[id] AS [id], [Extent1].[name] AS [name], CASE WHEN ([Extent2].[id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE [Extent2].[fullAddress] END AS [C1] FROM [dbo].[Person] AS [Extent1] LEFT OUTER JOIN [dbo].[Address] AS [Extent2] ON [Extent1].[addrid] = [Extent2].[id] ) AS [Project1] ORDER BY [Project1].[id] ASC
结果说明
FromSql不能代替原来EF6的SqlQuery使用,而且结合Linq扩展方法使用的时候生成的Sql会存在一些问题(EFCore版本为:1.1.0),那么为了能在EFCore中执行Sql查询语句,下面提供对SqlQuery方法的实现。
SqlQuery的实现
1 public static IList<T> SqlQuery<T>(DbContext db, string sql, params object[] parameters) 2 where T : new() 3 { 4 //注意:不要对GetDbConnection获取到的conn进行using或者调用Dispose,否则DbContext后续不能再进行使用了,会抛异常 5 var conn = db.Database.GetDbConnection(); 6 try 7 { 8 conn.Open(); 9 using (var command = conn.CreateCommand()) 10 { 11 command.CommandText = sql; 12 command.Parameters.AddRange(parameters); 13 var propts = typeof(T).GetProperties(); 14 var rtnList = new List<T>(); 15 T model; 16 object val; 17 using (var reader = command.ExecuteReader()) 18 { 19 while (reader.Read()) 20 { 21 model = new T(); 22 foreach (var l in propts) 23 { 24 val = reader[l.Name]; 25 if (val == DBNull.Value) 26 { 27 l.SetValue(model, null); 28 } 29 else 30 { 31 l.SetValue(model, val); 32 } 33 } 34 rtnList.Add(model); 35 } 36 } 37 return rtnList; 38 } 39 } 40 finally 41 { 42 conn.Close(); 43 } 44 }
使用:
1 var db = new MSSqlDBContext(); 2 string name = "tom"; 3 var list = SqlQuery<PAModel>(db, 4 $" select p.id, p.name, a.fullAddress, a.lat, a.lon " + 5 $" from ( select * from {nameof(Person)} where {nameof(name)}=@{nameof(name)} ) as p " + 6 $" left join {nameof(Address)} as a on p.addrid = a.id ", 7 new[] { new SqlParameter(nameof(name), name) });
生成的Sql:
exec sp_executesql N' select p.id, p.name, a.fullAddress, a.lat, a.lon from ( select * from Person where name=@name ) as p left join Address as a on p.addrid = a.id ',N'@name nvarchar(3)',@name=N'tom'