<address id="ll1nf"><listing id="ll1nf"><mark id="ll1nf"></mark></listing></address>

    <address id="ll1nf"></address>

        <sub id="ll1nf"></sub>

        <thead id="ll1nf"><var id="ll1nf"><output id="ll1nf"></output></var></thead>

        <address id="ll1nf"><dfn id="ll1nf"></dfn></address><form id="ll1nf"><listing id="ll1nf"><mark id="ll1nf"></mark></listing></form>

          <address id="ll1nf"><dfn id="ll1nf"></dfn></address>

          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

          public class MSSqlDBContext : DbContext
              {
                  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
                  {
                      optionsBuilder.UseSqlServer(@"data source=localhost;initial catalog=TestDB;Integrated Security=True;");
                  }
                  public DbSet<Person> Person { get; set; }
                  public DbSet<Address> 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的ExecuteSqlCommand和EF6的一样,执行非查询的Sql语句:

          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

          官方参考文档:https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

          简单使用

           var db = new MSSqlDBContext();
          2             var name = "tom";
          3             var list = db.Set<Person>().FromSql($"select * from {nameof(Person)} where {nameof(name)}[email protected]{nameof(name)} ", 
          4                 new SqlParameter(nameof(name), name)).ToList();
          

            生成的Sql:

          exec sp_executesql N‘select * from Person where [email protected] 
          ‘,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();
          

            

          执行存储过程

           var db = new MSSqlDBContext();
          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扩展方法配合使用

          var db = new MSSqlDBContext();
          db.Set<Person>().FromSql($"select * from {nameof(Person)} where [email protected] ", new SqlParameter("@name", "tom"))
                          .Select(l => new { l.name, l.birthday }).ToList();
          

            生成的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

          var db = new MSSqlDBContext();
                        (from p in db.Set<Person>().FromSql($"select * from {nameof(Person)} ")
                        join a in db.Set<Address>().Where(l => true)
                        on p.addrid equals a.id
                        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

          var db = new MSSqlDBContext();
                        (from p in db.Set<Person>().FromSql($"select * from {nameof(Person)} ")
                        join a in db.Set<Address>().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:(生成的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扩展方法试试:

                       (from p in db.Set<Person>().Where(l => true)
                        join a in db.Set<Address>().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();
          

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

          public static IList<T> SqlQuery<T>(DbContext db, string sql, params object[] parameters)
                      where T : new()
                  {
                      //注意:不要对GetDbConnection获取到的conn进行using或者调用Dispose,否则DbContext后续不能再进行使用了,会抛异常
                      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>();
                              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<PAModel>(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) });
          

            生成的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‘
          
          相关文章
          相关标签/搜索
          今晚一肖一码 什邡市| 兰溪市| 平果县| 科技| 牟定县| 宁安市| 舞阳县| 图木舒克市| 扎兰屯市| 黄浦区| 溧水县| 四子王旗| 灵璧县| 遂昌县| 齐齐哈尔市| 兴隆县| 句容市| 巴楚县| 榆林市| 云浮市| 长宁区| 通江县| 庄浪县| 朝阳区| 苍梧县| 仁化县| 赣州市| 明水县| 玛纳斯县| 斗六市| 盈江县| 开平市| 龙岩市| 大港区| 运城市| 渝北区| 突泉县| http://fa.hz0j1r6vo.fun http://fa.hz0j2r8vo.fun http://fa.hz0j0r8vo.fun http://fa.hz0j0r3vo.fun http://fa.hz0j0r6vo.fun