Preface
The methods of executing Sql statements in EFCore are: FromSql and ExecuteSqlCommand; in EF6, SqlQuery and ExecuteSqlCommand, and FromSql and SqlQuery are very different, and the return value of FromSql is IQueryable is lazy loading and can be used in conjunction with the Linq extension method, but there are many pitfalls (EFCore version is 1.1.0). It is recommended not to use FromSql for direct execution of Sql statements, but EFCore does not provide the SqlQuery method. Therefore, the implementation code of SqlQuery will be posted below for your reference, so that it can be used in EFCore.
The use of FromSql and ExecuteSqlCommand
SqlServer2008 and SqlServer Profiler were used to capture Sql statements during the test. The version of EFCore is 1.1.0.
Tested Entity Model and DbContext
public class MSSqlDBContext: DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"data source=localhost;initial catalog=TestDB;Integrated Security=True;");
}
public DbSet Person {get; set;}
public DbSet Address {get; set;}
}
[Table(nameof(Person))]
public class Person
{
public int id {get; set;}
public string name {get; set;}
[Column(TypeName = "datetime")]
public DateTime? birthday {get; set;}
public int? addrid {get; set;}
}
[Table(nameof(Address))]
public class Address
{
public int id {get; set;}
public string fullAddress {get; set;}
public double? lat {get; set;}
public double? lon {get; set;}
}
ExecuteSqlCommand
EFCore’s ExecuteSqlCommand is the same as EF6. Sql statement for query:
var db = new MSSqlDBContext();
2 db.Database.ExecuteSqlCommand($"update {nameof(Person)} set [email protected] where [email protected]", new[]
3 {
4 new SqlParameter("name", "tom1"),
5 new SqlParameter("id", 1),
6 });
FromSql
Official reference document: https://docs. microsoft.com/en-us/ef/core/querying/raw-sql
Easy to use
var db = new MSSqlDBContext(); 2 var name = "tom"; 3 var list = db.Set().FromSql($"select * from {nameof(Person)} where {nameof(name)}[emailprotected]{nameof(name)} ", 4 new SqlParameter(nameof(name), name)).ToList();
Generated Sql:
exec sp_executesql N'select * from Person where [email protected] ‘,N‘@name nvarchar(3)’,@name=N‘tom’
Note:
The Model of Person is generated by default. If the field obtained by Select does not contain a field in Person, it will be An exception is thrown, for example: the following statement only gets the name field, and does not contain other fields of Person, then an exception is thrown: The required column’id’ was not present in the results of a’FromSql’ operation.
< p>
db.Set().FromSql($"select name from {nameof(Person) } ").ToList();
then change to:
db.Set() .Select(l => l.name).FromSql($"select name from {nameof(Person)} ").ToList();
Execute stored procedure
var db = new MSSqlDBContext(); db.Set().FromSql("exec testproc @id", new SqlParameter("id", 1)).ToList();
Generated Sql:
exec sp_executesql N‘exec testproc @id ‘,N‘@id int’,@id=1
Use with Linq extension method
var db = new MSSqlDBContext(); db.Set().FromSql($"select * from {nameof(Person)} where [email protected] ", new SqlParameter("@name", "tom")) .Select(l => new {l.name, l.birthday }).ToList();
Generated Sql:
exec sp_executesql N'SELECT [l] .[name], [l].[birthday]
FROM (
select * from Person where [email protected]
) AS [l]‘,N‘@name nvarchar(3)‘,@name=N‘tom’
inner join + order by
< pre class="brush:csharp;gutter:true;">var db = new MSSqlDBContext();
(from p in db.Set().FromSql($”select * from {nameof(Person)} “)
join a in db.Set().Where(l => true)
on p.addrid equals a.id
select new {p.id, p.name, a.fullAddress }).OrderBy(l => l.id).ToList();
(from p in db.Set
join a in db.Set().Where(l => true)
on p.addrid equals a.id
select new {p.id, p.name, a.fullAddress }).OrderBy(l => l.id).ToList();
Sql generated by :
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
< pre class="brush:csharp;gutter:true;">var db = new MSSqlDBContext();
(from p in db.Set().FromSql($”select * from {nameof(Person)} “)
join a in db.Set().Where(l => true)
on p.addrid equals a.id into alist
from a in alist.DefaultIfEmpty()
select new {p.id, p.name, fullAddress = a == null? null: a.fullAddress }).OrderBy(l => l.id).ToList();
(from p in db.Set
join a in db.Set().Where(l => true)
on p.addrid equals a.id into alist
from a in alist.DefaultIfEmpty()
select new {p.id, p.name, fullAddress = a == null? null: a.fullAddress }).OrderBy(l => l.id).ToList();
Sql generated: (The generated Sql is very problematic, there are more [p].[addrid] after order by, and the generated select fields are also more)
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]
Replace FromSql with Where extension method to try:< /p>
(from p in db.Set().Where(l => true) join a in db.Set().Where(l => true) on p.addrid equals a.id into alist from a in alist.DefaultIfEmpty() select new {p.id, p.name, fullAddress = a == null? null: a.fullAddress }).OrderBy(l => l.id).ToList();
Sql generated by EFCore (there are still more [addrid] after order by, and more fields for 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]
The Sql generated in EF6 is much better than that of 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
Result description
FromSql cannot replace the original EF6 SqlQuery, and there will be some Sql generated when combined with the Linq extension method. Question (EFCore version: 1.1.0), then in order to be able to execute Sql query statements in EFCore, the implementation of the SqlQuery method is provided below.
SqlQuery implementation
public static IListSqlQuery (DbContext db , string sql, params object[] parameters) where T: new() { //Note: Do not use or call Dispose on the conn obtained by GetDbConnection, otherwise DbContext can no longer be used in the future, and an exception will be thrown var conn = db.Database.GetDbConnection(); try { conn.Open(); using (var command = conn.CreateCommand()) { command.CommandText = sql; command.Parameters.AddRange(parameters); var propts = typeof(T).GetProperties(); var rtnList = new List (); T model; object val; using (var reader = command.ExecuteReader()) { while (reader.Read()) { model = new T(); foreach (var l in propts) { val = reader[l.Name]; if (val == DBNull.Value) { l.SetValue(model, null); } else { l.SetValue(model, val); } } rtnList.Add(model); } } return rtnList; } } finally { conn.Close(); } }
use:
var db = new MSSqlDBContext();
string name = "tom";
var list = SqlQuery(db,
$" select p.id, p.name, a.fullAddress, a.lat, a.lon "+
$" from (select * from {nameof(Person)} where {nameof(name)}[email protected]{nameof(name)}) as p "+
$" left join {nameof(Address)} as a on p.addrid = a.id ",
new[] {new SqlParameter(nameof(name), name) });
Generated Sql:
exec sp_executesql N'select p.id , p.name, a.fullAddress, a.lat, a.lon from (select * from Person where [email protected]) as p left join Address as a on p.addrid = a.id',N'@name nvarchar (3)',@name=N'tom'
public class MSSqlDBContext: DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"data source=localhost;initial catalog=TestDB;Integrated Security=True;");
}
public DbSet Person {get; set;}
public DbSet Address {get; set;}
}
[Table(nameof(Person))]
public class Person
{
public int id {get; set;}
public string name {get; set;}
[Column(TypeName = "datetime")]
public DateTime? birthday {get; set;}
public int? addrid {get; set;}
}
[Table(nameof(Address))]
public class Address
{
public int id {get; set;}
public string fullAddress {get; set;}
public double? lat {get; set;}
public double? lon {get; set;}
}
var db = new MSSqlDBContext();
2 db.Database.ExecuteSqlCommand($"update {nameof(Person)} set [email protected] where [email protected]", new[]
3 {
4 new SqlParameter("name", "tom1"),
5 new SqlParameter("id", 1),
6 });
var db = new MSSqlDBContext(); 2 var name = "tom"; 3 var list = db.Set().FromSql($"select * from {nameof(Person)} where {nameof(name)}[emailprotected]{nameof(name)} ", 4 new SqlParameter(nameof(name), name)).ToList();
exec sp_executesql N‘select * from Person where [email protected] ‘,N‘@name nvarchar(3)’,@name=N‘tom’
db.Set().FromSql($"select name from {nameof(Person)} ") .ToList();
db.Set().Select(l => l.name).FromSql($"select name from {nameof(Person)} ").ToList();
var db = new MSSqlDBContext(); db.Set().FromSql("exec testproc @id", new SqlParameter("id", 1)).ToList();
exec sp_executesql N‘exec testproc @id ‘,N‘@id int’,@id=1
var db = new MSSqlDBContext(); db.Set().FromSql($"select * from {nameof(Person)} where [email protected] ", new SqlParameter("@name", "tom")) .Select(l => new {l.name, l.birthday }).ToList();
exec sp_executesql N‘SELECT [l].[name], [l].[birthday]
FROM (
select * from Person where [email protected]
) AS [l]‘,N‘@name nvarchar(3)‘,@name=N‘tom’
var db = new MSSqlDBContext();
(from p in db.Set().FromSql($"select * from {nameof(Person)} ")
join a in db.Set().Where(l => true)
on p.addrid equals a.id
select new {p.id, p.name, a.fullAddress }).OrderBy(l => l.id).ToList();
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]
var db = new MSSqlDBContext();
(from p in db.Set().FromSql($"select * from {nameof(Person)} ")
join a in db.Set().Where(l => true)
on p.addrid equals a.id into alist
from a in alist.DefaultIfEmpty()
select new {p.id, p.name, fullAddress = a == null? null: a.fullAddress }).OrderBy(l => l.id).ToList();
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]
(from p in db.Set().Where(l => true) join a in db.Set().Where(l => true) on p.addrid equals a.id into alist from a in alist.DefaultIfEmpty() select new {p.id, p.name, fullAddress = a == null? null: a.fullAddress }).OrderBy(l => l.id).ToList();
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]
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
public static IListSqlQuery (DbContext db, string sql, params object[] parameters) where T: new() { //Note: Do not use or call Dispose on the conn obtained by GetDbConnection, otherwise DbContext can no longer be used in the future, and an exception will be thrown var conn = db.Database.GetDbConnection(); try { conn.Open(); using (var command = conn.CreateCommand()) { command.CommandText = sql; command.Parameters.AddRange(parameters); var propts = typeof(T).GetProperties(); var rtnList = new List (); T model; object val; using (var reader = command.ExecuteReader()) { while (reader.Read()) { model = new T(); foreach (var l in propts) { val = reader[l.Name]; if (val == DBNull.Value) { l.SetValue(model, null); } else { l.SetValue(model, val); } } rtnList.Add(model); } } return rtnList; } } finally { conn.Close(); } }
var db = new MSSqlDBContext();
string name = "tom";
var list = SqlQuery(db,
$" select p.id, p.name, a.fullAddress, a.lat, a.lon "+
$" from (select * from {nameof(Person)} where {nameof(name)}[email protected]{nameof(name)}) as p "+
$" left join {nameof(Address)} as a on p.addrid = a.id ",
new[] {new SqlParameter(nameof(name), name) });
exec sp_executesql N'select p.id, p.name, a.fullAddress, a.lat, a.lon from (select * from Person where [email protected]) as p left join Address as a on p.addrid = a.id',N'@name nvarchar(3)',@name=N'tom'