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‘
            
            相关文章
            相关标签/搜索
            2020王中王资料一肖中2018年香港开奖日期表2018香港历史开奖结果香港最快开奖现场直播 阜新市| 封丘县| 彭阳县| 绥宁县| 塘沽区| 靖宇县| 苏尼特右旗| 墨竹工卡县| 合作市| 林甸县| 梨树县| 阿巴嘎旗| 邻水| 忻城县| 呼伦贝尔市| 奎屯市| 通化县| 井冈山市| 家居| 乌海市| 卢湾区| 新巴尔虎右旗| 柘城县| 郑州市| 旺苍县| 大同市| 思南县| 云霄县| 刚察县| 民勤县| 瑞昌市| 巴里| 贵州省| 铜鼓县| 孟津县| 三门峡市| 遵义县| 方城县| 株洲县| 四川省| 丹凤县| 道真| 志丹县| 南投县| 东丰县| 体育| 都兰县| 水富县| 灵川县| 仪征市| 沈阳市| 十堰市| 寻乌县| 麻城市| 东乌| 鹿邑县| 安顺市| 南投市| 朝阳县| 克什克腾旗| 宁海县| 葵青区| 台山市| 滁州市| 泽州县| 徐州市| 安化县| 祥云县| 赤峰市| 荆门市| 乡城县| 巴楚县| 龙海市| 西乌珠穆沁旗| 定边县| 福建省| 蓝田县| 沽源县| 游戏| 高安市| 江安县| 嘉义县| 山东| 河南省| 福州市| 淮安市| 高平市| 通榆县| 五莲县| 隆尧县| 莱阳市| 大洼县| 内江市| 天镇县| 木兰县| 蛟河市| 鲁山县| 彭山县| 高唐县| 南宁市| 喀什市| 遂溪县| 讷河市| 克山县| 特克斯县| 南陵县| 郸城县| 晋州市| 镇赉县| 大方县| 阳西县| 南部县| 子洲县| 达州市| 襄城县| 兰西县| 亚东县| 南川市| 枣庄市| 汽车| 包头市| 武陟县| 祥云县| 房山区| 新乐市| 阜新市| 敖汉旗| 阳城县| 城步| 泰兴市| 绥中县| 临夏县| 靖安县| 师宗县| 光泽县| 囊谦县| 兴国县| 广元市| 华蓥市| 万源市| 竹山县| 石狮市| 广德县| 霸州市| 汉寿县| 榆社县| 上饶县| 墨玉县| 清水河县| 敖汉旗| 万宁市| 岳普湖县| 华宁县| 库伦旗| 准格尔旗| 玉溪市| 岑溪市| 南京市| 福泉市| 扬州市| 眉山市| 樟树市| 德令哈市| 仪征市| 通山县| 瑞丽市| 搜索| 永安市| 兴和县| 太谷县| 武乡县| 迭部县| 绍兴县| 东乡族自治县| 泸州市| 泰州市| 高雄县| 淮阳县| 湄潭县| 米易县| 安西县| 蕲春县| 祁阳县| 金寨县| 章丘市| 巴林右旗| 镇远县| 甘孜县| 安庆市| 绥江县| 寻乌县| 大荔县| 肃南| 赫章县| 西城区| 台中县| 新绛县| 曲周县| 万荣县| 庄河市| 深泽县| 资阳市| 连江县| 东乌| 富锦市| 汶川县| 铁岭县| 改则县| 大石桥市| 宁阳县| 陆丰市| 宜良县| 板桥市| 屯门区| 佳木斯市| 广丰县| 铁岭市| 时尚| 将乐县| 沙雅县| 连州市| 定襄县| 和平县| 卓尼县| 盐源县| 乌什县| 大洼县| 将乐县| 武穴市| 江北区| 微山县| 绥中县| 健康| 乌拉特后旗| 白银市| 玉林市| 大化| 永宁县| 通河县| 无棣县| 吴旗县| 辽源市| 磐石市| 台北县| 靖远县| 莫力| 光山县| 凤阳县| 嘉定区| 文安县| 邹平县| 麟游县| 阜阳市| 东乡县| 化州市| 自贡市| 井研县| 宜君县| 开远市| 武平县| 阿鲁科尔沁旗| 电白县| 盱眙县| 洱源县| 牡丹江市| 邓州市| 内丘县| 谷城县| 习水县| 舟曲县| 凤阳县| 依安县| 丰镇市| 丹阳市| 石台县| 长春市| 乌拉特前旗| 柏乡县| 泾川县| 巴中市| 武宁县| 达尔| 红河县| 津南区| 安仁县| 澎湖县| 梅河口市| 土默特左旗| 安远县| 霍州市| 保德县| 卓资县| 普格县| 祁门县| 都江堰市| 井陉县| 宁德市| 永川市| 石嘴山市| 乌拉特前旗| 镇平县| 定襄县| 南召县| 滨州市| 南通市| 佛坪县| 当涂县| 玛曲县| 金华市| 丹巴县| 屯留县| 沅江市| 天等县| 凤冈县| 宜宾县| 宜兰市| 镇沅| 南溪县| 墨竹工卡县| 晴隆县| 泸州市| 菏泽市| 垫江县| 德兴市| 云安县| 清河县| 昭平县| 靖西县| 浦县| 沾化县| 镇安县| 富宁县| 乌什县| 安多县| 凭祥市| 无为县| 宜兴市| 寿光市| 旅游| 呼和浩特市| 女性| 西华县| 黄大仙区| 教育| 巧家县| 闵行区| 黎城县| 阜城县| 卢氏县| 漳州市| 宁河县| 和平区| 阆中市| 方山县| 象州县| 天祝| 保山市| 阿合奇县| 阿克| 金沙县| 精河县| 五峰| 日照市| 江口县| 建始县| 绩溪县| 永登县| 遂川县| 泽普县| 平武县| 南安市| 从江县| 鹤岗市| 裕民县| 合肥市| 南康市| 筠连县| 芦山县| 徐闻县| 葫芦岛市| 山丹县| 福安市| 新和县| 保山市| 柳河县| 贵港市| 兰坪| 肃南| 东安县| 田东县| 开远市| 山阳县| 广灵县| 博客| 台江县| 琼海市| 祁门县| 东丰县| 南投县| 英德市| 文水县| 巴楚县| 宁安市| 泾川县| 吴江市| 日照市| 淅川县| 开封市| 凉城县| 孟连| 井研县| 曲阜市| 岳阳市| 托克托县| 武隆县| 铁岭市| 天等县| 深泽县| 海门市| 毕节市| 呼伦贝尔市| 汉源县| 水城县| 黄浦区| 神池县| 嘉黎县| 怀集县| 池州市| 阿鲁科尔沁旗| 武强县| 波密县| 宜都市| 抚顺县| 平顶山市| 乐都县| 深州市| 建阳市| 武城县| 南澳县| 富宁县| 大竹县| 高青县| 庆安县| 岢岚县| 佛坪县| 星子县| 富川| 永康市| 吴忠市| 铜陵市| 贡山| 吉木萨尔县| 上虞市| 依安县| 平原县| 荔浦县| 柘荣县| 土默特左旗| 大洼县| 贡山| 蓬莱市| 通辽市| 阳新县| 马关县| 义乌市| 宜宾市| 大余县| 重庆市| 仙游县| 民县| 辽阳市| 漳平市| 沛县| 怀安县| 方山县| 太仓市| 微山县| 龙游县| 吉木萨尔县| 平昌县| 全南县| 临夏县| 红安县| 鄂州市| 增城市| 北海市| 濮阳市| 岳阳市| 云浮市| 上杭县| 威宁| 汕头市| 陈巴尔虎旗| 化德县| 镇巴县| 安阳市| 田东县| 洱源县| 澄江县| 密山市| 青田县| 阜阳市| 太仆寺旗| 遂川县| 威海市| 玉屏| 攀枝花市| 寿光市| 东乌珠穆沁旗| 弥渡县| 陈巴尔虎旗| 珠海市| 平泉县| 凉城县| 东方市| 大荔县| 荥经县| 彭阳县| 白河县| 新宁县| 青岛市| 台安县| 峨眉山市| 望江县| 滦南县| 蒙城县| 错那县| 泰州市| 昌乐县| 新竹县| 泉州市| 林周县| 民乐县| 元江| 灵山县| 始兴县| 涡阳县| 托克托县| 鄂尔多斯市| 东港市| 贺州市| 沧州市| 松阳县| 甘谷县| 云霄县| 隆昌县| 达拉特旗| 从化市| 安宁市| 工布江达县| 忻州市| 河曲县| 墨江| 石嘴山市| 牟定县| 香格里拉县| 搜索| 登封市| 东城区| 乳源| 进贤县| 江城| 北安市| 潜江市| 息烽县| 固原市| 侯马市| 拜泉县| 卢湾区| 五大连池市| 兰考县| 休宁县| 昌乐县| 鸡东县| 白沙| 永定县| 嘉禾县| 扎兰屯市| 冕宁县| 进贤县| 米脂县| 临夏县| 外汇| 财经| 城口县| 永仁县| 四会市| 革吉县| 榆中县| 隆回县| 南城县| 秦皇岛市| 正安县| 涞源县| 博白县| 磐安县| 伊川县| 江北区| 乌兰县| 房山区| 乌恰县| 南郑县| 自贡市| 黄平县| 沂源县| 阜平县| 兴业县| 河北省| 莱西市| 兴义市| 尤溪县| 江永县| 玉溪市| 奇台县| 宁夏| http://hz0j4r7vo.fun http://hz0j4r8vo.fun http://m.jx1870izpactv.fun http://www.jx1870interviewv.fun http://wap.jx1870handv.fun http://m.jx1870gunv.fun http://www.jx1870hidev.fun http://www.jx1870hopev.fun http://m.jx1870indexv.fun http://wap.jx1870headv.fun http://www.jx1870filzv.fun http://wap.jx1870launchv.fun http://wap.hz0j1r3vo.fun http://m.hz0j3r6vo.fun http://jx1870enhancev.fun http://m.hz0j3r5vo.fun http://m.jx1870figurev.fun http://jx1870hatev.fun