在EFCore中執行Sql語句的方法為：FromSql與ExecuteSqlCommand；在EF6中的為SqlQuery與ExecuteSqlCommand，而FromSql和SqlQuery有很大區別，FromSql返回值為IQueryable，因此為延遲加載的，可以與Linq擴展方法配合使用，但是有不少的坑(EFCore版本為1.1.0)，直接執行Sql語句的建議不要使用FromSql，但是EFCore中並沒有提供SqlQuery方法，因此下面會貼出SqlQuery的實現代碼供大家參考，以便在EFCore中能使用。
測試時使用了SqlServer2008和SqlServer Profiler進行Sql語句捕捉，EFCore的版本為1.1.0。
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 }
View Code
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 });
官方參考文檔：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
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'
View Code
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]
View Code
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方法的實現。
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 }
View Code
使用：
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'
