EFCore下怎么跑sql语句

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

EFCore下怎么跑sql语句 兴致来了，多写⼀篇吧。

所有转netcore的⼩伙伴们都发现了： ef core跟以前的ef差距⾮常⼤，view（视图）⽆法通过dbfirst⽣成了，存储过程也⼀样（虽然我现在开始转codefirst了）。 然⽽，如果真的想直接执⾏sql语句怎么办？ 我们发现context下的Database属性跟以前也不⼀样了，只能做些事务操作，没有执⾏sql了。可以执⾏sql的变成了每张具体的表（DbSet）下⾯的FromSql⽅法了（需要显式引⽤FrameworkCore命名空间）。 但是这个⽅法存在问题，只能返回该表类型的结果，⽆法返回任意类型。 so，跟⼤家⼀样，我去⽹上搜搜解决⽅案。查到了⼀个⽅案。 然⽽，为了响应ef本⾝跨数据库种类的设计要求，我觉得应该做⼀个可以⾃⾏根据数据库类型判断需要执⾏什么语句的组件，所以我给我们亲爱的dbcontext写了⼀个扩展： ⾸先请⾃觉nuget拉⼀下包 onal。 整个扩展的⼤体思路是： 针对⼀次查询，将各种数据库的查询语句封装到⼀起，在运⾏时⾃动判断当前连接的数据库环境，执⾏相应的语句。这样不破坏依赖注⼊的原则，需要的是编程⼈员根据可能连接的数据库种类，写对应库的查询语句。 另外这⾥还⽤了⼀个神奇的技术（我⾃⼰早就在使⽤了），以⽅便查询参数的操作。就是使⽤匿名类型来提供查询语句参数，如"select *from abc where id between @start and @end"语句，只需要提供参数 new {start = 1, end=100} 作为参数传⼊即可。不⽤再去new⼀个parameter，每次填⼀堆。

废话够多了，直接上代码了：using ;using FrameworkCore;using lient;using System;using c;using ;using ;using ent;using ;namespace { ///

/// 数据库查询语句 ///

public class DbContextSqlQueryCommand { ///

/// 使⽤不含参数的查询语句 ///

/// public DbContextSqlQueryCommand(string query) { Query = query; } ///

/// 使⽤包含参数的查询语句 ///

/// public DbContextSqlQueryCommand(string query, object @params) { Query = query; Parameters = @params; } ///

/// 查询语句 ///

/// public string Query { get; set; } ///

/// 参数 ///

public object Parameters { get; set; } } ///

/// 数据库查询语句集合 ///

public class DbContextSqlQueryCommands { ///

/// 数据库为SqlServer时使⽤的查询语句 ///

public DbContextSqlQueryCommand Sql { get; set; } ///

/// 数据库为MySql时使⽤的查询语句 ///

public DbContextSqlQueryCommand MySql { get; set; } ///

/// 数据库为InMemory时使⽤的查询语句 ///

public DbContextSqlQueryCommand InMemory { get; set; } ///

/// 数据库为Sqlite时使⽤的查询语句 ///

public DbContextSqlQueryCommand Sqlite { get; set; } } ///

/// 数据库类型 ///

public enum DbContextType { InMemory = 0, SqlServer = 1, MySql = 2, Sqlite = 3, } ///

/// EF上下⽂扩展 ///

public static class DbContextExtensions { //拼接参数 private static void combineParams(DbContextType type, ref DbCommand command, object @params = null) { if (@params != null) { Type paramType; string prefix; switch (type) { case ry: throw new Exception("未实现的数据库类型"); case ver: paramType = typeof(SqlParameter); prefix = "@"; break; case : paramType = typeof(MySqlParameter); prefix = "@"; break; break; case : paramType = typeof(SqliteParameter); prefix = "@"; break; default: throw new Exception("未实现的数据库类型"); } foreach (var param in @e().GetProperties()) { var paramItem = Instance(paramType, $"{prefix}{}", (object)ue(@params)); (paramItem); } } } //创建命令（同时返回连接符） private static DbCommand createCommand(DbContext context, DbContextSqlQueryCommands commands, out DbConnection connection) { var conn = onnection(); connection = conn; (); var cmd = Command(); if ( != null && te()) { dText = ; combineParams(, ref cmd, ters); } else if( != null && l()) { dText = ; combineParams(, ref cmd, ters); } else if ( != null && erver()) { dText = ; combineParams(ver, ref cmd, ters); } else if (ry != null) { throw new NotImplementedException(); } return cmd; } ///

/// 执⾏sql语句，返回受影响⾏数 ///

/// EF上下⽂ /// 数据库查询语句集合 /// 受影响⾏数 public static int Exec(this DbContext context, DbContextSqlQueryCommands commands) { var command = createCommand(context, commands, out var conn); var rsl = eNonQuery(); (); return rsl; } ///

/// 查询数据库 ///

/// EF上下⽂ /// 数据库查询语句集合 /// 数据DataTable public static DataTable Query(this DbContext context, DbContextSqlQueryCommands commands) { { var command = createCommand(context, commands, out var conn); var reader = eReader(); DataTable dt = new DataTable(); (reader); (); (); return dt; } ///

/// 查询数据库，返回多个查询结果集 ///

/// EF上下⽂ /// 数据库查询语句集合 /// 数据DataSet public static DataSet QuerySet(this DbContext context, DbContextSqlQueryCommands commands) { var dt = Query(context, commands); var ds = new DataSet(); (dt); return ds; } ///

/// 查询数据库，返回IEnumerable的强类型数据 ///

/// 查询结果类型 /// EF上下⽂ /// 数据库查询语句集合 /// IEnumerable的强类型数据 public static IEnumerable Query(this DbContext context, DbContextSqlQueryCommands commands) { var dt = Query(context, commands); return erable(); } ///

/// 查询数据库，返回第⼀条数据 ///

/// EF上下⽂ /// 数据库查询语句集合 /// 查询到的第⼀条数据或null public static DataRow QueryOne(this DbContext context, DbContextSqlQueryCommands commands) { var dt = Query(context, commands); return > 0 ? [0] : null; } ///

/// 查询数据库，返回第⼀条强类型数据 ///

/// 查询结果类型 /// EF上下⽂ /// 数据库查询语句集合 /// 查询到的第⼀条强类型数据 public static T QueryOne(this DbContext context, DbContextSqlQueryCommands commands) { var dr = QueryOne(context, commands); return ct(); } ///

/// 查询数据库，返回唯⼀数据 ///

/// EF上下⽂ /// EF上下⽂ /// 数据库查询语句集合 /// 查询到的唯⼀数据 public static object QueryObject(this DbContext context, DbContextSqlQueryCommands commands) { var command = createCommand(context, commands, out var conn); var rsl = eScalar(); (); return rsl; } ///

/// 查询数据库，返回唯⼀强类型数据 ///

/// 查询结果类型 /// EF上下⽂ /// 数据库查询语句集合 /// 查询到的唯⼀强类型数据 public static T QueryObject(this DbContext context, DbContextSqlQueryCommands commands) { return (T)QueryObject(context, commands); } }} 这⾥并未提供ToObject和ToEnumerable等扩展⽅法的实现，这些⽅法只是简单的将DataTable或者DataRow转换成强类型，利⽤反射可以轻松做到，有兴趣的朋友可以⾃⼰实现⼀下。 或者maybe我会在后⾯的⽂章⾥贴⼀下。

这个扩展是针对DbContext的扩展，暴露的⽅法⼤体上是：Exec 返回影响⾏数， Query 返回查询的列表，可以通过泛型重载Query直接返回强类型的IEnumerable接⼝对象，QueryOne返回⼀⾏，同理QueryOne返回强类型对象。QueryObject返回object，同理QueryObject返回简单类型值。

具体⽤法⽰例：var list = (new DbContextSqlQueryCommands { //调⽤存储过程 Sql = new DbContextSqlQueryCommand(@"exec GetXXXProcedure @ids", new { ids = (",", cids), }) }); so, that's it. 好好⼯作，天天向太阳……

----------------------------------------华丽的分割线------------------------------------- 补充⼀句，此扩展依赖 的⼀堆，包括.SqlServer， .Sqlite，.InMemory等，其中Mysql微软未提供官⽅库，因⽽⽤了这个第三⽅库。 另外⼀点就是，上述代码其实未实现InMemory的库的操作，主要是我还没搞清InMemory的库到底怎么⽤-----------------------------------------我是最新分割线-------------------------------------------有朋友问我ToEnumerable和ToObject的实现，这⾥贴⼀下吧 ///

///

/// 数据相关扩展 ///

public static class DataExtensions { //从属性列表为DataTable创建列 private static void createColumns(DataTable table, PropertyInfo[] piArr) { (); foreach (var pi in piArr) { (, tyType); } } //⽤obj的属性填充row private static void fillDataRow(DataRow row, object obj, PropertyInfo[] piArr) { foreach (var pi in piArr) { row[] = ue(obj); } } //⽤row的栏填充obj public static void fillObject(T obj, DataRow row, PropertyInfo[] piArr) { foreach (var pi in piArr) { try { ue(obj, row[]); } catch { } } } ///

/// 从类型为DataTable创建Columns ///

/// DataTable对象 /// 作为创建模板的类型 public static void CreateColumsFromType(this DataTable table, Type type) { PropertyInfo[] piArr = perties(); createColumns(table, piArr); } ///

/// 从object为DataTable创建Columns ///

/// DataTable对象 /// 作为创建模板的object public static void CreateColumsFromObject(this DataTable table, object obj) { CreateColumsFromType(table, e()); } ///

/// 将DataRow转换为强类型 ///

/// 要转换为的强类型 /// 要转换的DataRow对象 /// 转换后的强类型对象 /// 转换后的强类型对象 public static T ToObject(this DataRow row) { if (row == null) { return default(T); } var obj = Instance(); PropertyInfo[] piArr = typeof(T).GetProperties(); fillObject(obj, row, piArr); return obj; } ///

/// 将对象转换为DataRow对象 ///

/// 要转换的对象 /// 转换后的DataRow对象 public static DataRow ToDataRow(this object obj) { if (obj == null) { return null; } PropertyInfo[] piArr = e().GetProperties(); DataTable dt = new DataTable(); createColumns(dt, piArr); DataRow row = (); fillDataRow(row, obj, piArr); return row; } ///

/// 将对象转换为属于指定DataTable的DataRow对象 ///

/// 属于的table /// 要转换的对象 /// 转换后的属于指定DataTable的DataRow对象 public static DataRow ToDataRow(this object obj, DataTable table) { if (obj == null) { return null; } PropertyInfo[] piArr = e().GetProperties(); createColumns(table, piArr); DataRow row = (); fillDataRow(row, obj, piArr); return row; } ///

/// 将DataTable转换为IEnumerable的强类型对象 ///

/// 要转换为的强类型 /// 要转换的DataTable对象 /// 转换后的IEnumerable的强类型对象 public static IEnumerable ToEnumerable(this DataTable table) { List list = new List(); PropertyInfo[] piArr = typeof(T).GetProperties(); PropertyInfo[] piArr = typeof(T).GetProperties(); foreach (DataRow row in ) { var obj = Instance(); fillObject(obj, row, piArr); (obj); } return erable(); }

///

/// 将IEnumerable的强类型对象转换为DataTable ///

/// 要转换的强类型 /// 要转换的IEnumerable的强类型对象 /// 转换后的DataTable对象 public static DataTable ToDataTable(this IEnumerable objArr) { DataTable dt = new DataTable(); PropertyInfo[] piArr = typeof(T).GetProperties(); createColumns(dt, piArr); foreach (var obj in objArr) { DataRow row = (); fillDataRow(row, obj, piArr); (row); } return dt; } ///

/// 将DataSet转换为IEnumerable的IEnumerable的强类型对象 ///

/// 要转换为的强类型 /// 要转换的DataSet对象 /// 转换后的IEnumerable的IEnumerable的强类型对象 public static IEnumerable> ToEnumerableEnumerable(this DataSet set) { List> rsl = new List>(); PropertyInfo[] piArr = typeof(T).GetProperties(); foreach (DataTable dt in ) { List list = new List(); foreach (DataRow row in ) { var obj = Instance(); fillObject(obj, row, piArr); (obj); } (erable()); } return erable>(); } }

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

EFCore下怎么跑sql语句 兴致来了，多写⼀篇吧。

所有转netcore的⼩伙伴们都发现了： ef core跟以前的ef差距⾮常⼤，view（视图）⽆法通过dbfirst⽣成了，存储过程也⼀样（虽然我现在开始转codefirst了）。 然⽽，如果真的想直接执⾏sql语句怎么办？ 我们发现context下的Database属性跟以前也不⼀样了，只能做些事务操作，没有执⾏sql了。可以执⾏sql的变成了每张具体的表（DbSet）下⾯的FromSql⽅法了（需要显式引⽤FrameworkCore命名空间）。 但是这个⽅法存在问题，只能返回该表类型的结果，⽆法返回任意类型。 so，跟⼤家⼀样，我去⽹上搜搜解决⽅案。查到了⼀个⽅案。 然⽽，为了响应ef本⾝跨数据库种类的设计要求，我觉得应该做⼀个可以⾃⾏根据数据库类型判断需要执⾏什么语句的组件，所以我给我们亲爱的dbcontext写了⼀个扩展： ⾸先请⾃觉nuget拉⼀下包 onal。 整个扩展的⼤体思路是： 针对⼀次查询，将各种数据库的查询语句封装到⼀起，在运⾏时⾃动判断当前连接的数据库环境，执⾏相应的语句。这样不破坏依赖注⼊的原则，需要的是编程⼈员根据可能连接的数据库种类，写对应库的查询语句。 另外这⾥还⽤了⼀个神奇的技术（我⾃⼰早就在使⽤了），以⽅便查询参数的操作。就是使⽤匿名类型来提供查询语句参数，如"select *from abc where id between @start and @end"语句，只需要提供参数 new {start = 1, end=100} 作为参数传⼊即可。不⽤再去new⼀个parameter，每次填⼀堆。

废话够多了，直接上代码了：using ;using FrameworkCore;using lient;using System;using c;using ;using ;using ent;using ;namespace { ///

/// 数据库查询语句 ///

public class DbContextSqlQueryCommand { ///

/// 使⽤不含参数的查询语句 ///

/// public DbContextSqlQueryCommand(string query) { Query = query; } ///

/// 使⽤包含参数的查询语句 ///

/// public DbContextSqlQueryCommand(string query, object @params) { Query = query; Parameters = @params; } ///

/// 查询语句 ///

///

public string Query { get; set; } ///

/// 参数 ///

public object Parameters { get; set; } } ///

/// 数据库查询语句集合 ///

public class DbContextSqlQueryCommands { ///

/// 数据库为SqlServer时使⽤的查询语句 ///

public DbContextSqlQueryCommand Sql { get; set; } ///

/// 数据库为MySql时使⽤的查询语句 ///

public DbContextSqlQueryCommand MySql { get; set; } ///

/// 数据库为InMemory时使⽤的查询语句 ///

public DbContextSqlQueryCommand InMemory { get; set; } ///

/// 数据库为Sqlite时使⽤的查询语句 ///

public DbContextSqlQueryCommand Sqlite { get; set; } } ///

/// 数据库类型 ///

public enum DbContextType { InMemory = 0, SqlServer = 1, MySql = 2, Sqlite = 3, } ///

/// EF上下⽂扩展 ///

public static class DbContextExtensions { //拼接参数 private static void combineParams(DbContextType type, ref DbCommand command, object @params = null) { if (@params != null) { Type paramType; string prefix; switch (type) { case ry: throw new Exception("未实现的数据库类型"); case ver: paramType = typeof(SqlParameter); prefix = "@"; break; case : paramType = typeof(MySqlParameter); prefix = "@"; break; break; case : paramType = typeof(SqliteParameter); prefix = "@"; break; default: throw new Exception("未实现的数据库类型"); } foreach (var param in @e().GetProperties()) { var paramItem = Instance(paramType, $"{prefix}{}", (object)ue(@params)); (paramItem); } } } //创建命令（同时返回连接符） private static DbCommand createCommand(DbContext context, DbContextSqlQueryCommands commands, out DbConnection connection) { var conn = onnection(); connection = conn; (); var cmd = Command(); if ( != null && te()) { dText = ; combineParams(, ref cmd, ters); } else if( != null && l()) { dText = ; combineParams(, ref cmd, ters); } else if ( != null && erver()) { dText = ; combineParams(ver, ref cmd, ters); } else if (ry != null) { throw new NotImplementedException(); } return cmd; } ///

/// 执⾏sql语句，返回受影响⾏数 ///

/// EF上下⽂ /// 数据库查询语句集合 /// 受影响⾏数 public static int Exec(this DbContext context, DbContextSqlQueryCommands commands) { var command = createCommand(context, commands, out var conn); var rsl = eNonQuery(); (); return rsl; } ///

/// 查询数据库 ///

/// EF上下⽂ /// 数据库查询语句集合 /// 数据DataTable public static DataTable Query(this DbContext context, DbContextSqlQueryCommands commands) { { var command = createCommand(context, commands, out var conn); var reader = eReader(); DataTable dt = new DataTable(); (reader); (); (); return dt; } ///

/// 查询数据库，返回多个查询结果集 ///

/// EF上下⽂ /// 数据库查询语句集合 /// 数据DataSet public static DataSet QuerySet(this DbContext context, DbContextSqlQueryCommands commands) { var dt = Query(context, commands); var ds = new DataSet(); (dt); return ds; } ///

/// 查询数据库，返回IEnumerable的强类型数据 ///

/// 查询结果类型 /// EF上下⽂ /// 数据库查询语句集合 /// IEnumerable的强类型数据 public static IEnumerable Query(this DbContext context, DbContextSqlQueryCommands commands) { var dt = Query(context, commands); return erable(); } ///

/// 查询数据库，返回第⼀条数据 ///

/// EF上下⽂ /// 数据库查询语句集合 /// 查询到的第⼀条数据或null public static DataRow QueryOne(this DbContext context, DbContextSqlQueryCommands commands) { var dt = Query(context, commands); return > 0 ? [0] : null; } ///

/// 查询数据库，返回第⼀条强类型数据 ///

/// 查询结果类型 /// EF上下⽂ /// 数据库查询语句集合 /// 查询到的第⼀条强类型数据 public static T QueryOne(this DbContext context, DbContextSqlQueryCommands commands) { var dr = QueryOne(context, commands); return ct(); } ///

/// 查询数据库，返回唯⼀数据 ///

/// EF上下⽂ /// EF上下⽂ /// 数据库查询语句集合 /// 查询到的唯⼀数据 public static object QueryObject(this DbContext context, DbContextSqlQueryCommands commands) { var command = createCommand(context, commands, out var conn); var rsl = eScalar(); (); return rsl; } ///

/// 查询数据库，返回唯⼀强类型数据 ///

/// 查询结果类型 /// EF上下⽂ /// 数据库查询语句集合 /// 查询到的唯⼀强类型数据 public static T QueryObject(this DbContext context, DbContextSqlQueryCommands commands) { return (T)QueryObject(context, commands); } }} 这⾥并未提供ToObject和ToEnumerable等扩展⽅法的实现，这些⽅法只是简单的将DataTable或者DataRow转换成强类型，利⽤反射可以轻松做到，有兴趣的朋友可以⾃⼰实现⼀下。 或者maybe我会在后⾯的⽂章⾥贴⼀下。

这个扩展是针对DbContext的扩展，暴露的⽅法⼤体上是：Exec 返回影响⾏数， Query 返回查询的列表，可以通过泛型重载Query直接返回强类型的IEnumerable接⼝对象，QueryOne返回⼀⾏，同理QueryOne返回强类型对象。QueryObject返回object，同理QueryObject返回简单类型值。

具体⽤法⽰例：var list = (new DbContextSqlQueryCommands { //调⽤存储过程 Sql = new DbContextSqlQueryCommand(@"exec GetXXXProcedure @ids", new { ids = (",", cids), }) }); so, that's it. 好好⼯作，天天向太阳……

----------------------------------------华丽的分割线------------------------------------- 补充⼀句，此扩展依赖 的⼀堆，包括.SqlServer， .Sqlite，.InMemory等，其中Mysql微软未提供官⽅库，因⽽⽤了这个第三⽅库。 另外⼀点就是，上述代码其实未实现InMemory的库的操作，主要是我还没搞清InMemory的库到底怎么⽤-----------------------------------------我是最新分割线-------------------------------------------有朋友问我ToEnumerable和ToObject的实现，这⾥贴⼀下吧 ///

///

/// 数据相关扩展 ///

public static class DataExtensions { //从属性列表为DataTable创建列 private static void createColumns(DataTable table, PropertyInfo[] piArr) { (); foreach (var pi in piArr) { (, tyType); } } //⽤obj的属性填充row private static void fillDataRow(DataRow row, object obj, PropertyInfo[] piArr) { foreach (var pi in piArr) { row[] = ue(obj); } } //⽤row的栏填充obj public static void fillObject(T obj, DataRow row, PropertyInfo[] piArr) { foreach (var pi in piArr) { try { ue(obj, row[]); } catch { } } } ///

/// 从类型为DataTable创建Columns ///

/// DataTable对象 /// 作为创建模板的类型 public static void CreateColumsFromType(this DataTable table, Type type) { PropertyInfo[] piArr = perties(); createColumns(table, piArr); } ///

/// 从object为DataTable创建Columns ///

/// DataTable对象 /// 作为创建模板的object public static void CreateColumsFromObject(this DataTable table, object obj) { CreateColumsFromType(table, e()); } ///

/// 将DataRow转换为强类型 ///

/// 要转换为的强类型 /// 要转换的DataRow对象 /// 转换后的强类型对象 /// 转换后的强类型对象 public static T ToObject(this DataRow row) { if (row == null) { return default(T); } var obj = Instance(); PropertyInfo[] piArr = typeof(T).GetProperties(); fillObject(obj, row, piArr); return obj; } ///

/// 将对象转换为DataRow对象 ///

/// 要转换的对象 /// 转换后的DataRow对象 public static DataRow ToDataRow(this object obj) { if (obj == null) { return null; } PropertyInfo[] piArr = e().GetProperties(); DataTable dt = new DataTable(); createColumns(dt, piArr); DataRow row = (); fillDataRow(row, obj, piArr); return row; } ///

/// 将对象转换为属于指定DataTable的DataRow对象 ///

/// 属于的table /// 要转换的对象 /// 转换后的属于指定DataTable的DataRow对象 public static DataRow ToDataRow(this object obj, DataTable table) { if (obj == null) { return null; } PropertyInfo[] piArr = e().GetProperties(); createColumns(table, piArr); DataRow row = (); fillDataRow(row, obj, piArr); return row; } ///

/// 将DataTable转换为IEnumerable的强类型对象 ///

/// 要转换为的强类型 /// 要转换的DataTable对象 /// 转换后的IEnumerable的强类型对象 public static IEnumerable ToEnumerable(this DataTable table) { List list = new List(); PropertyInfo[] piArr = typeof(T).GetProperties(); PropertyInfo[] piArr = typeof(T).GetProperties(); foreach (DataRow row in ) { var obj = Instance(); fillObject(obj, row, piArr); (obj); } return erable(); }

///

/// 将IEnumerable的强类型对象转换为DataTable ///

/// 要转换的强类型 /// 要转换的IEnumerable的强类型对象 /// 转换后的DataTable对象 public static DataTable ToDataTable(this IEnumerable objArr) { DataTable dt = new DataTable(); PropertyInfo[] piArr = typeof(T).GetProperties(); createColumns(dt, piArr); foreach (var obj in objArr) { DataRow row = (); fillDataRow(row, obj, piArr); (row); } return dt; } ///

/// 将DataSet转换为IEnumerable的IEnumerable的强类型对象 ///

/// 要转换为的强类型 /// 要转换的DataSet对象 /// 转换后的IEnumerable的IEnumerable的强类型对象 public static IEnumerable> ToEnumerableEnumerable(this DataSet set) { List> rsl = new List>(); PropertyInfo[] piArr = typeof(T).GetProperties(); foreach (DataTable dt in ) { List list = new List(); foreach (DataRow row in ) { var obj = Instance(); fillObject(obj, row, piArr); (obj); } (erable()); } return erable>(); } }