EFCore执行Sql语句的方法:FromSql与ExecuteSqlCommand

[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的实现

View Code使⽤：1 var db = new MSSqlDBContext();2 string name = "tom";3 var list = SqlQuery(db,4 $" select , , dress, , " +5 $" from ( select * from {nameof(Person)} where {nameof(name)}=@{nameof(name)} ) as p " +6 $" left join {nameof(Address)} as a on = ",7 new[] { new SqlParameter(nameof(name), name) });⽣成的Sql：exec sp_executesql N' select , , dress, , from ( select * from Person where name=@name ) as p left join Address as a on = ',N'@name

nvarchar(3)',@name=N'tom'

2023年6月21日发(作者：)

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

View Code

ExecuteSqlCommandEFCore的ExecuteSqlCommand和EF6的⼀样，执⾏⾮查询的Sql语句：1 var db = new MSSqlDBContext();2 eSqlCommand($"update {nameof(Person)} set name=@name where id=@id", new[]

3 {4 new SqlParameter("name", "tom1"),5 new SqlParameter("id", 1),6 });

FromSql简单使⽤1 var db = new MSSqlDBContext();2 var name = "tom";3 var list = ().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' ().FromSql($"select name from {nameof(Person)} ").ToList();那么改为：().Select(l => ).FromSql($"select name from {nameof(Person)} ").ToList();

执⾏存储过程1 var db = new MSSqlDBContext();2 ().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 ().FromSql($"select * from {nameof(Person)} where name=@name ", new SqlParameter("@name", "tom"))3 .Select(l => new { , ay }).ToList();⽣成的Sql：

View Codeinner join + order by1 var db = new MSSqlDBContext();2 (from p in ().FromSql($"select * from {nameof(Person)} ")3 join a in