2023年6月21日发(作者：)

EFCore中执⾏Sql语句查询操作之FromSql，ExecuteSqlCommand，。。。⼀、⽬前EF Core的版本为V2.1相⽐较EF Core v1.0 ⽬前已经增加了不少功能。EF Core除了常⽤的增删改模型操作，Sql语句在不少项⽬中是不能避免的。在EF Core中上下⽂，可以返货DbConnection ，执⾏sql语句。这是最底层的操作⽅式，代码写起来还是挺多的。初次之外 EF Core中还⽀持 FromSql,ExecuteSqlCommand 连个⽅法，⽤于更⽅便的执⾏Sql语句。另外，⽬前版本的EF Core 不⽀持SqlQuery，但是我们可以⾃⼰扩展⼀个。坐等升级以后⽀持吧。l，执⾏列表查询public static IQueryable FromSql([NotNullAttribute] this IQueryable source,

[NotParameterized] RawSqlString sql,

[NotNullAttribute] params object[] parameters) where TEntity : class;这种⽅式，仅⽤于当前上线⽂中注册的 模型对象。对于上下⽂DbSet中没有定义的不起作⽤。⽰例代码1： //执⾏sql查询语句 FromSql() QLLB_SWXContext _Context = new QLLB_SWXContext(); string sql = "select * from Article where CategoryID=1;"; List

list = _l(sql).ToList(); foreach (var item in list) { ine(); }⽰例代码2：视图中的查询---创建视图，查询没有分配⾓⾊的菜单create view view_NoRoleas

select * from Sys_Navigationwhere NavID not in (select distinct NavID from Sys_Role_Nav

)//查询视图 string sql2 = "select * from view_NoRole"; List roleList = _l(sql2).ToList(); foreach (var item in roleList) { ine(); }eSqlCommand，执⾏Sql操作处理QLLB_SWXContext _Context = new QLLB_SWXContext();//执⾏数据操作sql,返回受影响的⾏数string sql = "update Sys_Role set SortValue=1 ;";int count = _eSqlCommand(sql);ine(count);3.⾃定义SqlQuery，执⾏列表查询，在上线⽂中不存的对象。⽰例代码1:QLLB_SWXContext _Context = new QLLB_SWXContext();//特别说明，⾃定义分装的不⽀持 单个值查询//不⽀持object 查询//⾃定义查询操作 SqlQuerystring sql = "select sum(ViewCount)*1.11 as allCount from Article;";TempData result = _ry(sql).FirstOrDefault();ine(nt);对象定义public class TempData{ public int CategoryID { get; set; } public string Title { get; set; } public int ArtCount { get; set; } ///

/// 求和结果 /// public decimal AllCount { get; set; }}⽰例代码2：执⾏视图查询：--定义视图，⽂章分类和对应分类的⽂章数量create view view_CateCountasselect ryID,, (select count(*) from Article where CategoryID=ryID) as ArtCount from ArticleCategory C;C#代码：//组合查询string sql2 = "select * from view_CateCount;";List tempList = _ry(sql2).ToList();foreach (var item in tempList){ ine();}SqlQuery扩展定义：using FrameworkCore;using tructure;using System;using c;using ;using ;using ent;using tion;using ;namespace { public static class DbContextExtensions { private static void CombineParams(ref DbCommand command, params object[] parameters) { if (parameters != null) { foreach (SqlParameter parameter in parameters) { if (!ns("@")) terName = $"@{terName}"; (parameter); } } } private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection dbConn, params object[] parameters) { DbConnection conn = onnection(); dbConn = conn; (); DbCommand cmd = Command(); if (erver()) { dText = sql; CombineParams(ref cmd, parameters); } return cmd; } public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters) { DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters); DbDataReader reader = eReader(); DataTable dt = new DataTable(); (reader); (); (); return dt; } public static IEnumerable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new() { DataTable dt = SqlQuery(facade, sql, parameters); return erable(); } public static IEnumerable ToEnumerable(this DataTable dt) where T : class, new() { PropertyInfo[] propertyInfos = typeof(T).GetProperties(); T[] ts = new T[]; int i = 0; foreach (DataRow row in ) { T t = new T(); foreach (PropertyInfo p in propertyInfos) { if (f() != -1 && row[] != ) ue(t, row[], null); } ts[i] = t; i++; } return ts; } }}View Code

更多：

2023年6月21日发(作者：)

EFCore中执⾏Sql语句查询操作之FromSql，ExecuteSqlCommand，。。。⼀、⽬前EF Core的版本为V2.1相⽐较EF Core v1.0 ⽬前已经增加了不少功能。EF Core除了常⽤的增删改模型操作，Sql语句在不少项⽬中是不能避免的。在EF Core中上下⽂，可以返货DbConnection ，执⾏sql语句。这是最底层的操作⽅式，代码写起来还是挺多的。初次之外 EF Core中还⽀持 FromSql,ExecuteSqlCommand 连个⽅法，⽤于更⽅便的执⾏Sql语句。另外，⽬前版本的EF Core 不⽀持SqlQuery，但是我们可以⾃⼰扩展⼀个。坐等升级以后⽀持吧。l，执⾏列表查询public static IQueryable FromSql([NotNullAttribute] this IQueryable source,

[NotParameterized] RawSqlString sql,

[NotNullAttribute] params object[] parameters) where TEntity : class;这种⽅式，仅⽤于当前上线⽂中注册的 模型对象。对于上下⽂DbSet中没有定义的不起作⽤。⽰例代码1： //执⾏sql查询语句 FromSql() QLLB_SWXContext _Context = new QLLB_SWXContext(); string sql = "select * from Article where CategoryID=1;"; List

list = _l(sql).ToList(); foreach (var item in list) { ine(); }⽰例代码2：视图中的查询---创建视图，查询没有分配⾓⾊的菜单create view view_NoRoleas

select * from Sys_Navigationwhere NavID not in (select distinct NavID from Sys_Role_Nav

)//查询视图 string sql2 = "select * from view_NoRole"; List roleList = _l(sql2).ToList(); foreach (var item in roleList) { ine(); }eSqlCommand，执⾏Sql操作处理QLLB_SWXContext _Context = new QLLB_SWXContext();//执⾏数据操作sql,返回受影响的⾏数string sql = "update Sys_Role set SortValue=1 ;";int count = _eSqlCommand(sql);ine(count);3.⾃定义SqlQuery，执⾏列表查询，在上线⽂中不存的对象。⽰例代码1:QLLB_SWXContext _Context = new QLLB_SWXContext();//特别说明，⾃定义分装的不⽀持 单个值查询//不⽀持object 查询//⾃定义查询操作 SqlQuerystring sql = "select sum(ViewCount)*1.11 as allCount from Article;";TempData result = _ry(sql).FirstOrDefault();ine(nt);对象定义public class TempData{ public int CategoryID { get; set; } public string Title { get; set; } public int ArtCount { get; set; } ///

/// 求和结果 /// public decimal AllCount { get; set; }}⽰例代码2：执⾏视图查询：--定义视图，⽂章分类和对应分类的⽂章数量create view view_CateCountasselect ryID,, (select count(*) from Article where CategoryID=ryID) as ArtCount from ArticleCategory C;C#代码：//组合查询string sql2 = "select * from view_CateCount;";List tempList = _ry(sql2).ToList();foreach (var item in tempList){ ine();}SqlQuery扩展定义：using FrameworkCore;using tructure;using System;using c;using ;using ;using ent;using tion;using ;namespace { public static class DbContextExtensions { private static void CombineParams(ref DbCommand command, params object[] parameters) { if (parameters != null) { foreach (SqlParameter parameter in parameters) { if (!ns("@")) terName = $"@{terName}"; (parameter); } } } private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection dbConn, params object[] parameters) { DbConnection conn = onnection(); dbConn = conn; (); DbCommand cmd = Command(); if (erver()) { dText = sql; CombineParams(ref cmd, parameters); } return cmd; } public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters) { DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters); DbDataReader reader = eReader(); DataTable dt = new DataTable(); (reader); (); (); return dt; } public static IEnumerable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new() { DataTable dt = SqlQuery(facade, sql, parameters); return erable(); } public static IEnumerable ToEnumerable(this DataTable dt) where T : class, new() { PropertyInfo[] propertyInfos = typeof(T).GetProperties(); T[] ts = new T[]; int i = 0; foreach (DataRow row in ) { T t = new T(); foreach (PropertyInfo p in propertyInfos) { if (f() != -1 && row[] != ) ue(t, row[], null); } ts[i] = t; i++; } return ts; } }}View Code

更多：