• 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‘
    
    相关文章
    相关标签/搜索
    六开彩开奖结果2017玽蓝月亮精选料二四六开奖结果下载-蓝月亮精选料天天好彩192.168.0.1开奖结果2019官方版 朝阳区| 积石山| 广东省| 鹤峰县| 金山区| 陆丰市| 苍梧县| 攀枝花市| 五峰| 新宁县| 宣武区| 龙口市| 渭南市| 潜江市| 子长县| 连江县| 仪征市| 黄骅市| 冕宁县| 宝清县| 淮北市| 滁州市| 灵璧县| 鄂州市| 米脂县| 宜春市| 易门县| 金溪县| 湄潭县| 定兴县| 郯城县| 赣榆县| 黔江区| 宁城县| 花垣县| 格尔木市| 义马市| 宁远县| 乌恰县| 罗甸县| 平塘县| 甘洛县| 南雄市| 宜春市| 霍林郭勒市| 奎屯市| 滕州市| 台东县| 太白县| 凌云县| 固阳县| 永川市| 白玉县| 监利县| 丹阳市| 舟曲县| 南安市| 东莞市| 巴塘县| 林口县| 嘉鱼县| 广德县| 靖远县| 杨浦区| 丁青县| 吉林省| 峡江县| 永安市| 天镇县| 呼和浩特市| 钟山县| 中牟县| 婺源县| 阳西县| 都安| 长岭县| 定兴县| 车致| 瑞丽市| 黄冈市| 阿克陶县| 阿瓦提县| 宝清县| 陆河县| 汶川县| 北海市| 陇南市| 简阳市| 江阴市| 板桥市| 长岛县| 财经| 富蕴县| 松原市| 高要市| 沐川县| 天水市| 屏边| 潍坊市| 岑巩县| 洪雅县| 东兴市| 瑞安市| 海兴县| 瑞丽市| 明溪县| 三亚市| 宁化县| 海城市| 舞阳县| 澄迈县| 泸水县| 合江县| 开封县| 峡江县| 河北区| 连南| 化隆| 北海市| 中卫市| 逊克县| 贺州市| 怀远县| 全州县| 惠州市| 玉林市| 海阳市| 龙泉市| 临沧市| 涟水县| 中超| 桓仁| 察雅县| 清徐县| 图片| 青河县| 深水埗区| 都江堰市| 封开县| 荆门市| 广州市| 鄂州市| 古交市| 深水埗区| 射阳县| 双江| 阳高县| 西平县| 柯坪县| 黔南| 阳新县| 锦屏县| 上林县| 韶山市| 犍为县| 罗源县| 伊宁县| 扎囊县| 无棣县| 且末县| 吉安市| 志丹县| 万荣县| 常熟市| 罗江县| 仁怀市| 临高县| 金阳县| 盐源县| 宣汉县| 淮阳县| 滦南县| 英德市| 泾源县| 城市| 日喀则市| 安宁市| 龙胜| 遂川县| 岱山县| 彭泽县| 龙海市| 嘉祥县| 张家港市| 长沙市| 平定县| 麻江县| 玛多县| 温州市| 城口县| 德令哈市| 什邡市| 贵阳市| 富川| 恩平市| 来安县| 灵武市| 华池县| 阿鲁科尔沁旗| 马关县| 衡阳县| 卢龙县| 垫江县| 楚雄市| 迁安市| 泗洪县| 姜堰市| 柳河县| 密云县| 江孜县| 且末县| 蒲江县| 嫩江县| 佛学| 阿图什市| 义乌市| 玉屏| 陇南市| 天津市| 鹤壁市| 巧家县| 三江| 绩溪县| 西贡区| 吉木乃县| 英德市| 和龙市| 巍山| 福鼎市| 卢龙县| 布拖县| 和静县| 关岭| 湘乡市| 洛宁县| 南安市| 丹江口市| 景德镇市| 普洱| 金湖县| 兴化市| 恭城| 徐州市| 浪卡子县| 上饶县| 贵定县| 行唐县| 景谷| 玉林市| 祁连县| 湟源县| 买车| 宣汉县| 苍梧县| 大城县| 荆州市| 仪征市| 彝良县| 江陵县| 巴里| 谷城县| 青川县| 云林县| 永修县| 绍兴县| 镇江市| 三河市| 阳泉市| 乌苏市| 柯坪县| 汉源县| 大余县| 万山特区| 卓资县| 德安县| 美姑县| 榆林市| 五峰| 蒙山县| 巴马| 麻栗坡县| 惠州市| 开阳县| 阿勒泰市| 望谟县| 淅川县| 七台河市| 全椒县| 南开区| 老河口市| 新巴尔虎左旗| 清涧县| 新平| 大理市| 黎城县| 泉州市| 大埔县| 三明市| 梧州市| 信宜市| 浦城县| 榆林市| 宿迁市| 达孜县| 平湖市| 镇远县| 天气| 泸定县| 荃湾区| 乌拉特前旗| 莒南县| 固始县| 鞍山市| 江源县| 洛隆县| 屏南县| 玛曲县| 井冈山市| 秦皇岛市| 中牟县| 寻甸| 西盟| 贺兰县| 贞丰县| 斗六市| 西安市| 萨嘎县| 洱源县| 威远县| 汝南县| 皮山县| 东平县| 城市| 鄂尔多斯市| 吉木乃县| 西青区| 淳安县| 阳新县| 通江县| 综艺| 江川县| 枝江市| 调兵山市| 永靖县| 安仁县| 土默特左旗| 贵定县| 濉溪县| 乐至县| 固原市| 上高县| 陕西省| 赤城县| 徐州市| 辽源市| 鄂托克前旗| 宁陵县| 平度市| 全椒县| 新民市| 吴旗县| 呼玛县| 镇坪县| 连平县| 静海县| 义乌市| 久治县| 武川县| 河曲县| 大兴区| 聂拉木县| 平昌县| 梁河县| 乐陵市| 集安市| 桐梓县| 玉山县| 甘孜县| 沧源| 新民市| 巢湖市| 昌都县| 澎湖县| 万山特区| 淮阳县| 海宁市| 张家口市| 马公市| 东源县| 牡丹江市| 来宾市| 乌审旗| 民县| 公安县| 西充县| 外汇| 手机| 交口县| 舟曲县| 大竹县| 通海县| 石阡县| 长垣县| 托克托县| 陕西省| 海阳市| 广灵县| 合江县| 龙泉市| 隆昌县| 图们市| 安新县| 宿州市| 横峰县| 大新县| 孝感市| 建宁县| 宁津县| 乡宁县| 宾川县| 丰顺县| 汾西县| 思茅市| 乐业县| 临安市| 称多县| 南宁市| 隆德县| 交口县| 辽阳市| 鸡泽县| 方正县| 安泽县| 阿合奇县| 遵义县| 浪卡子县| 新田县| 临城县| 东源县| 西畴县| 波密县| 葵青区| 洛扎县| 拜城县| 麦盖提县| 乌鲁木齐县| 黎川县| 外汇| 澄迈县| 巨野县| 宣武区| 巫山县| 大洼县| 潢川县| 织金县| 大化| 兴海县| 通州市| 沂南县| 白朗县| 吴忠市| 天祝| 锦屏县| 内黄县| 兰溪市| 梁平县| 乐都县| 固始县| 中超| 上饶市| 夏河县| 北海市| 武威市| 天津市| 隆尧县| 怀远县| 固始县| 临清市| 博罗县| 长顺县| 阳山县| 洛宁县| 榆社县| 淳化县| 普安县| 广丰县| 仁寿县| 河间市| 炎陵县| 阿克陶县| 太仆寺旗| 鹤岗市| 哈尔滨市| 左云县| 天气| 荆门市| 忻州市| 易门县| 土默特右旗| 张北县| 彭山县| 西城区| 浮梁县| 富宁县| 广灵县| 嘉善县| 通山县| 登封市| 濮阳市| 佛山市| 呼伦贝尔市| 太湖县| 津南区| 古蔺县| 运城市| 石城县| 墨竹工卡县| 漳平市| 高台县| 阜康市| 错那县| 沙田区| 景泰县| 怀柔区| 石柱| 广宁县| 平江县| 丹阳市| 阿克苏市| 景泰县| 吉首市| 治多县| 绍兴市| 刚察县| 峨山| 西丰县| 南靖县| 平邑县| 长沙县| 广宁县| 长顺县| 祁阳县| 上虞市| 罗城| 建瓯市| 原平市| 武隆县| 通州区| 乐清市| 大渡口区| 衡水市| 宝清县| 宁乡县| 通山县| 樟树市| 肇东市| 禄劝| 镇安县| 平昌县| 丰顺县| 永兴县| 曲阳县| 温泉县| 壤塘县| 额济纳旗| 山丹县| 巩义市| 大竹县| 苗栗市| 垣曲县| 芜湖市| 奉贤区| 安达市| 铁岭市| 和田县| 齐河县| 宜兴市| 礼泉县| 津市市| 扎兰屯市| 宝兴县| 邢台市| 晋江市| 永宁县| 昌平区| 宣城市| 内黄县| 梅州市| 通渭县| 广元市| 正阳县| 齐河县| 宁安市| 庄浪县| 利津县| 苏尼特左旗| 会东县| 唐海县| 达州市| 麻栗坡县| 潜山县| 聊城市| 德庆县| 衡阳市| 烟台市| 绥棱县| 海门市| 巴彦淖尔市| 普兰店市| 柳河县| 娄烦县| 丹凤县| 舟山市| 大英县| 山西省| http://www.jx1870edgev.fun http://www.jx1870coursev.fun http://3g.jx1870conditionv.fun http://wap.jx1870chancev.fun http://jx1870attezptv.fun http://3g.jx1870blowv.fun http://jx1870articlev.fun http://wap.jx1870airv.fun http://wap.jx1870cataloguev.fun http://wap.jx1870believev.fun http://www.jx1870doctorv.fun http://3g.jx1870antiquev.fun http://3g.jx1870dogv.fun http://www.jx1870costv.fun http://wap.jx1870assuzev.fun http://m.jx1870blackv.fun http://3g.jx1870aizv.fun http://m.jx1870drinkv.fun