.NET Core C#系列之XiaoFeng.Data.IQueryableX ORM框架
- 2022 年 10 月 24 日
- 筆記
當前對象操作數據庫寫法和EF Core極度類似，因為現在大部分程序員都懶得去寫SQL，再一個就是項目作大了或其它原因要改數據庫，每次改數據庫，那麼寫的SQL語句大部分要作調整，相當麻煩，並且寫SQL時字段極容易寫錯，所以就出現了好多ORM框架，極大的提高了程序員的開發效率。
接下來我們講一下 XiaoFeng.Data.IQueryableX的用法。
我們在上一節 數據庫操作 中有講過 ConnectionConfig 數據庫連接配置對象，不明白的同學可以這節看。本節繼續用當前配置對象。
建立數據庫連接配置對象，FayElf.Web可為配置名稱，也可以為連接串server=.;data=testdata;uid=da;pwd=123; 後邊參數為數據庫驅動。
var ConnConfig = new ConnectionConfig("FayElf.Web", DbProviderType.SqlServer);
使用 IQueryableX 可以有兩種使用方式，下邊模型默認就是數據庫中的字段名稱，也就是它們兩個名稱要一一對應。如果不一樣，則可以用 XiaoFeng.Column 屬性去配置數據庫中表字段名稱，下邊分別舉例說明。
通過 DataHelperX 創建一個 IQueryableX 對象 然後進行 IQueryableX 運算 去查數據庫，IQueryableX和EF Core中的 IQueryable 非常類似，但運行效率比 IQueryable 快很多，並支持多數據庫切換。
1.繼承了 XiaoFeng.Model.Entity 實體模型的，我們暫且叫A方案，如下邊模型
/// <summary>
/// 分類模型
/// </summary>
[Table("Category","分類", "CategoryId",ModelType.Model,"FayElf.Web",0)]
public class Category : Entity<Category>
{
#region 構造器
/// <summary>
/// 無參構造器
/// </summary>
public Category()
{
}
#endregion
#region 屬性
/// <summary>
/// 分類名稱
/// </summary>
[Column(Name="Names")]
public string Name { get; set; }
/// <summary>
/// 分類說明
/// </summary>
public string Description { get; set; }
/// <summary>
/// 分類ID
/// </summary>
public int CategoryId { get; set; }
/// <summary>
/// 父節點ID
/// </summary>
public int ParentId { get; set; }
/// <summary>
/// 添加日期
/// </summary>
public DateTime AddDate { get; set; }
#endregion
#region 方法
#endregion
}
2.沒有繼續XiaoFeng.Model.Entity實體模型的，我們暫且叫B方案，如下模型
/// <summary>
/// 用戶模型
/// </summary>
public class UserModel
{
#region 構造器
/// <summary>
/// 無參構造器
/// </summary>
public UserModel()
{
}
#endregion
#region 屬性
/// <summary>
/// Id
/// </summary>
public long Id { get; set; }
/// <summary>
/// 帳號
/// </summary>
public string Account { get; set; }
/// <summary>
/// 密碼
/// </summary>
public string Password { get; set; }
/// <summary>
/// 性別
/// </summary>
public int Sex { get; set; }
/// <summary>
/// 出生日期
/// </summary>
public DateTime BirthDay { get; set; }
/// <summary>
/// 添加日期
/// </summary>
public DateTime AddDate { get; set; }
#endregion
#region 方法
#endregion
}
首先我們先舉實例講解一下 A方案 的使用方法
//首選實例化一個 Category 實例
var category = Category.Create;
//查詢
var category1 = category.Find(a => a.CategoryId == 2);
var category2 = category.Where(a => a.CategoryId == 2).ToEntity();
//上邊兩種方式是一樣的 都是提取 CategoryId 等於 2 的一條數據
//列表
var category3 = category.Where(a => a.CategoryId == 3).ToList();
var category4 = category.ToList(a => a.CategoryId == 3);
//上邊兩種方式是一樣的 都是提取 CategoryId 等於 3 的列表數據
//刪除
//當前語句是刪除 CateogryId 等於 4 的這條記錄
var delete5 = category.Where(a => a.CategoryId == 4).Delete();
//當前語句是刪除查從庫中查出來的一條數據給刪除掉
var delete6 = category1.Delete();
//插入
new Category
{
Name="a", ParentId=0
}.Insert();
//更新
//當前語句是更新字段 為 Name ParentId 的字段值 條件是 CategoryId 等於 5 的記錄
var update5 = category.Where(a => a.CategoryId == 5).Update(a => a.Name == "aaa" && a.ParentId == 10);
//當前語句是更新 字段 Name ParentId的值 數據是上邊查詢出來的一條記錄
category1.Name = "bbbb";
category1.ParentId = 3;
var update6 = category.Update();
//聯表 下邊用法就是 通過 左關聯 兩張表 on 條件是 第一張表的 ParentId 與第二張表的 CategoryId 相同 然後字段顯示為 Name,CategoryId,ParentName 的列表
var join1 = category.QueryableX.Join<Category, object[]>((a, b) => new object[] { JoinType.Left, a.ParentId == b.CategoryId }).Select((a, b) => new
{
a.Name,
a.CategoryId,
ParentName = b.Name
}).ToList();
//更多表關聯 有兩種方法 第一種是 兩張表關聯的結果和第三張表關聯，再關聯的結果與第四經表關聯 依次類推
//第二種方法就是 假設一共有5張表關聯那麼直接實例化 DataHelperX時直接關聯5張表，建議用第一種方法。
接着我們講 B 方案用法
//創建一個帶數據庫的 IQueryableX 對象，下邊所有的操作都是基於當前對象來操作。
var data = new DataHelperX<UserModel>(Conn);
//查詢 下邊三種寫法 運行後 結果是一樣的
var userModel1 = data.Where(a => a.Account == "jacky" && a.Password == "admin123").ToEntity();
var userModel2 = data.Where(a => a.Account == "jacky").Where(a => a.Password == "admin123").ToEntity();
var userModel3 = data.First(a => a.Account == "jacky" && a.Password == "admin123");
//列表
var userList1 = data.Where(a => a.Sex == 1 || a.Account == "jacky").ToList();
//關聯查詢
var userList2 = data.Where(a => a.Sex == 1).Join<UserModel, object[]>((a, b) => new object[] { a.Account == b.Password }).Select((a, b) => new
{
a.Account,
a.Password,
ParentName = b.Account
}).ToList();
//插入
var userModel4 = new UserModel { Account = "jacky", Password = "admin" };
var insert1 = data.Insert(userModel4);
//如果想返回自增長ID則後邊輸入接收參數即可如下
var insert2 = data.Insert(userModel4, out var ID);
//批量插入
var insert3 = data.Inserts(new List<UserModel> { userModel4,userModel4 });
//更新
var update1 = data.Where(a => a.Id == 1).Update(a => a.Password == "adminadmin");
//刪除
var delete1 = data.Where(a => a.Id == 1).Delete();
下邊我們說一下 內部函數用法，函數擴展了基本上SQL中用到的函數，以及C#語言中的字符串處理函數的大部分，內部函數擴展方法如下：
/// <summary>
/// 擴展SQL語法
/// Version : 2.0.0
/// Author : jacky
/// Email : [email protected]
/// QQ : 7092734
/// Site : www.zhuovi.com
/// Create Time : 2017/12/20 9:40:00
/// Update Time : 2018/01/19 13:40:00
/// </summary>
public static partial class PrototypeHelper
{
#region 擴展SQL 語法
#region 擴展SQL 字段加值+
/// <summary>
/// 字段加值
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="value">值</param>
/// <returns></returns>
public static T AddSQL<T>(this T obj, T value) { return obj; }
#endregion
#region 擴展SQL 字段減值-
/// <summary>
/// 字段減值
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="value">值</param>
/// <returns></returns>
public static T SubtractSQL<T>(this T obj, T value) { return obj; }
#endregion
#region 擴展SQL 字段乘值*
/// <summary>
/// 字段乘值
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static T MultiplySQL<T>(this T obj, T value) { return obj; }
#endregion
#region 擴展SQL 字段除值/
/// <summary>
/// 字段除值
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static T DivideSQL<T>(this T obj, T value) { return obj; }
#endregion
#region 擴展SQL Between
/// <summary>
/// 擴展SQL Between
/// </summary>
/// <param name="obj">對象</param>
/// <param name="startValue">開始值</param>
/// <param name="endValue">結束值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Boolean BetweenSQL(this object obj, object startValue, object endValue) { return true; }
#endregion
#region 擴展SQL ISNULL
/// <summary>
/// 擴展SQL ISNULL
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="defaultValue">默認值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static T IsNullSQL<T>(this T obj, T defaultValue) { return defaultValue; }
#endregion
#region 擴展SQL in 語法
/// <summary>
/// 擴展SQL in語法
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="array">數組織</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static bool InSQL<T>(this T obj, IEnumerable<T> array) { return true; }
/// <summary>
/// 擴展SQL in語法
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="array">數組織</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static bool InSQL<T>(this T obj, T[] array) { return true; }
/// <summary>
/// 擴展SQL in語法
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <typeparam name="T1">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="queryableX">IQueryableX對象</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Boolean InSQL<T, T1>(this T obj, IQueryableX<T1> queryableX) { return true; }
/// <summary>
/// 擴展SQL in語法
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="array">數組</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static bool InSQL<T>(this T obj, object[] array) { return true; }
/// <summary>
/// 擴展SQL not in語法
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="array">數組</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static bool NotInSQL<T>(this T obj, IEnumerable<T> array) { return true; }
/// <summary>
/// 擴展SQL not in語法
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="array">數組</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static bool NotInSQL<T>(this T obj, T[] array) { return true; }
/// <summary>
/// 擴展SQL not in語法
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <typeparam name="T1">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="queryableX">IQueryableX對象</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Boolean NotInSQL<T, T1>(this T obj, IQueryableX<T1> queryableX) { return true; }
/// <summary>
/// 擴展SQL not in語法
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="array">數組</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static bool NotInSQL<T>(this T obj, object[] array) { return true; }
#endregion
#region 擴展SQL like 語法
/// <summary>
/// 擴展SQL like 語法
/// </summary>
/// <param name="str">字符串</param>
/// <param name="likeStr">子串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static bool LikeSQL(this object str, string likeStr) { return true; }
/// <summary>
/// 擴展SQL not like 語法
/// </summary>
/// <param name="str">字符串</param>
/// <param name="likeStr">子串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static bool NotLikeSQL(this string str, string likeStr) { return true; }
/// <summary>
/// 擴展SQL like 語法
/// </summary>
/// <param name="str">字符串</param>
/// <param name="likeStr">子串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static bool LikeSQLX(this object str, string likeStr) { return true; }
/// <summary>
/// 擴展SQL not like 語法
/// </summary>
/// <param name="str">字符串</param>
/// <param name="likeStr">子串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static bool NotLikeSQLX(this string str, string likeStr) { return true; }
#endregion
#region 擴展SQL DateAdd 語法
/// <summary>
/// 擴展SQL DateAdd 語法
/// </summary>
/// <param name="date">日期</param>
/// <param name="val">值</param>
/// <param name="format">格式 年yy,yyyy 季度qq,q 月mm,m 年中的日dy,y 日dd,d 周wk,ww 星期dw,w 小時hh 分鐘mi,n 秒ss,s 毫秒ms 微秒mcs 納秒ns</param>
/// <returns></returns>
public static DateTime DateAddSQL(this DateTime date, long val, string format)
{
return date;
}
/// <summary>
/// 擴展SQL DateAdd 語法
/// </summary>
/// <param name="date">日期</param>
/// <param name="val">值</param>
/// <param name="format">格式 年yy,yyyy 季度qq,q 月mm,m 年中的日dy,y 日dd,d 周wk,ww 星期dw,w 小時hh 分鐘mi,n 秒ss,s 毫秒ms 微秒mcs 納秒ns</param>
/// <returns></returns>
public static DateTime DateAddSQL(this DateTime? date, long val, string format)
{
return date.GetValueOrDefault();
}
#endregion
#region 擴展SQL DateDiff 語法
/// <summary>
/// 擴展SQL DateDiff 語法
/// </summary>
/// <param name="firstDate">第一個時間</param>
/// <param name="SecondDate">第二個時間</param>
/// <param name="format">格式 年yy,yyyy 季度qq,q 月mm,m 年中的日dy,y 日dd,d 周wk,ww 星期dw,w 小時hh 分鐘mi,n 秒ss,s 毫秒ms 微秒mcs 納秒ns</param>
/// <returns></returns>
public static int DateDiffSQL(this DateTime? firstDate, DateTime? SecondDate, string format) { return 1; }
/// <summary>
/// 擴展SQL DateDiff 語法
/// </summary>
/// <param name="firstDate">第一個時間</param>
/// <param name="SecondDate">第二個時間</param>
/// <param name="format">格式 年yy,yyyy 季度qq,q 月mm,m 年中的日dy,y 日dd,d 周wk,ww 星期dw,w 小時hh 分鐘mi,n 秒ss,s 毫秒ms 微秒mcs 納秒ns</param>
/// <returns></returns>
public static int DateDiffSQL(this DateTime firstDate, DateTime SecondDate, string format) { return 1; }
#endregion
#region 擴展SQL DatePart 語法
/// <summary>
/// 擴展SQL DatePart 語法
/// </summary>
/// <param name="Date">當前時間</param>
/// <param name="format">格式 年yy,yyyy 季度qq,q 月mm,m 年中的日dy,y 日dd,d 周wk,ww 星期dw,w 小時hh 分鐘mi,n 秒ss,s 毫秒ms 微秒mcs 納秒ns</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static int DatePartSQL(this DateTime? Date, string format) { return 0; }
/// <summary>
/// 擴展SQL DatePart 語法
/// </summary>
/// <param name="Date">當前時間</param>
/// <param name="format">格式 年yy,yyyy 季度qq,q 月mm,m 年中的日dy,y 日dd,d 周wk,ww 星期dw,w 小時hh 分鐘mi,n 秒ss,s 毫秒ms 微秒mcs 納秒ns</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static int DatePartSQL(this DateTime Date, string format) { return 0; }
#endregion
#region 擴展SQL DateFormat 語法
/// <summary>
/// 擴展SQL DateFormat 語法
/// </summary>
/// <param name="Date">當前時間</param>
/// <param name="format"></param>
/// <returns></returns>
public static string DateFormatSQL(this DateTime? Date, string format) { return string.Empty; }
/// <summary>
/// 擴展SQL DateFormat 語法
/// </summary>
/// <param name="Date">當前時間</param>
/// <param name="format"></param>
/// <returns></returns>
public static string DateFormatSQL(this DateTime Date, string format) { return string.Empty; }
#endregion
#region 擴展SQL Charindex 語法
/// <summary>
/// 擴展SQL Charindex 語法
/// </summary>
/// <param name="str">字符串</param>
/// <param name="IndexStr">子串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static int CharIndexSQL(this string str, string IndexStr) { return 1; }
#endregion
#region 擴展SQL Patindex語法
/// <summary>
/// 擴展SQL Patindex語法
/// </summary>
/// <param name="str">字符串</param>
/// <param name="IndexStr">子串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static int PatindexSQL(this string str, string IndexStr) { return 1; }
#endregion
#region 擴展SQL Abs 算法
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static int AbsSQL(this int value) { return 1; }
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static int AbsSQL(this int? value) { return 1; }
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Int16 AbsSQL(this Int16? value) { return 1; }
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Int16 AbsSQL(this Int16 value) { return 1; }
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Int64 AbsSQL(this Int64? value) { return 1; }
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Int64 AbsSQL(this Int64 value) { return 1; }
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Double AbsSQL(this Double? value) { return 1; }
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Double AbsSQL(this Double value) { return 1; }
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static decimal AbsSQL(this decimal? value) { return 1; }
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static decimal AbsSQL(this decimal value) { return 1; }
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static float AbsSQL(this float? value) { return 1; }
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static float AbsSQL(this float value) { return 1; }
#endregion
#region 擴展SQL Floor 算法
/// <summary>
/// 擴展SQL Floor 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Double FloorSQL(this Double? value) { return 1; }
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Double FloorSQL(this Double value) { return 1; }
/// <summary>
/// 擴展SQL Floor 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static decimal FloorSQL(this decimal? value) { return 1; }
/// <summary>
/// 擴展SQL Floor 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static decimal FloorSQL(this decimal value) { return 1; }
/// <summary>
/// 擴展SQL Floor 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static float FloorSQL(this float? value) { return 1; }
/// <summary>
/// 擴展SQL Floor 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static float FloorSQL(this float value) { return 1; }
#endregion
#region 擴展SQL Ceiling 算法
/// <summary>
/// 擴展SQL Ceiling 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Double CeilingSQL(this Double? value) { return 1; }
/// <summary>
/// 擴展SQL Ceiling 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Double CeilingSQL(this Double value) { return 1; }
/// <summary>
/// 擴展SQL Ceiling 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static decimal CeilingSQL(this decimal? value) { return 1; }
/// <summary>
/// 擴展SQL Ceiling 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static decimal CeilingSQL(this decimal value) { return 1; }
/// <summary>
/// 擴展SQL Ceiling 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static float CeilingSQL(this float? value) { return 1; }
/// <summary>
/// 擴展SQL Ceiling 算法
/// </summary>
/// <param name="value">值</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static float CeilingSQL(this float value) { return 1; }
#endregion
#region 擴展SQL Round 算法
/// <summary>
/// 擴展SQL Round 算法
/// </summary>
/// <param name="value">值</param>
/// <param name="len">小數點保留長度</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Double RoundSQL(this Double? value, int len) { return 1; }
/// <summary>
/// 擴展SQL Round 算法
/// </summary>
/// <param name="value">值</param>
/// <param name="len">小數點保留長度</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static Double RoundSQL(this Double value, int len) { return 1; }
/// <summary>
/// 擴展SQL Round 算法
/// </summary>
/// <param name="value">值</param>
/// <param name="len">小數點保留長度</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static decimal RoundSQL(this decimal? value, int len) { return 1; }
/// <summary>
/// 擴展SQL Round 算法
/// </summary>
/// <param name="value">值</param>
/// <param name="len">小數點保留長度</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static decimal RoundSQL(this decimal value, int len) { return 1; }
/// <summary>
/// 擴展SQL Abs 算法
/// </summary>
/// <param name="value">值</param>
/// <param name="len">小數點保留長度</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static float RoundSQL(this float? value, int len) { return 1; }
/// <summary>
/// 擴展SQL Round 算法
/// </summary>
/// <param name="value">值</param>
/// <param name="len">小數點保留長度</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static float RoundSQL(this float value, int len) { return 1; }
#endregion
#region 擴展SQL Left
/// <summary>
/// 擴展SQL Left
/// </summary>
/// <param name="str">字符串</param>
/// <param name="num">第幾位</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static string LeftSQL(this string str, int num) { return ""; }
#endregion
#region 擴展SQL Right
/// <summary>
/// 擴展SQL Right
/// </summary>
/// <param name="str">字符串</param>
/// <param name="num">第幾位</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static string RightSQL(this string str, int num) { return ""; }
#endregion
#region 擴展SQL Len
/// <summary>
/// 擴展SQL Len
/// </summary>
/// <param name="str">字符串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static int LengthSQL(this string str) { return 1; }
#endregion
#region 擴展SQL Replace
/// <summary>
/// 擴展SQL Replace
/// </summary>
/// <param name="str">字符串</param>
/// <param name="oldString">原字符串</param>
/// <param name="replaceString">新字符串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static string ReplaceSQL(this string str, string oldString, string replaceString) { return ""; }
#endregion
#region 擴展SQL Replicate
/// <summary>
/// 擴展SQL Replicate
/// </summary>
/// <param name="str">字符串</param>
/// <param name="num">重複次數</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static string ReplicateSQL(this string str, int num) { return ""; }
#endregion
#region 擴展SQL Reverse
/// <summary>
/// 擴展SQL Reverse
/// </summary>
/// <param name="str">字符串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static string ReverseSQL(this string str) { return ""; }
#endregion
#region 擴展SQL Stuff
/// <summary>
/// 擴展SQL Stuff
/// </summary>
/// <param name="str">字符串</param>
/// <param name="strat">開始位置</param>
/// <param name="length">長度</param>
/// <param name="replaceString">替換字符串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static string StuffSQL(this string str, int strat, int length, string replaceString) { return ""; }
#endregion
#region 擴展SQL Substring
/// <summary>
/// 擴展SQL Substring
/// </summary>
/// <param name="str">字符串</param>
/// <param name="start">開始位置</param>
/// <param name="length">長度</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static string SubstringSQL(this string str, int start, int length) { return ""; }
#endregion
#region 擴展SQL Trim
/// <summary>
/// 擴展SQL Ltrim
/// </summary>
/// <param name="str">字符串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static string LTrimSQL(this string str) { return ""; }
/// <summary>
/// 擴展SQL Rtrim
/// </summary>
/// <param name="str">字符串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static string RTrimSQL(this string str) { return ""; }
/// <summary>
/// 擴展SQL Trim
/// </summary>
/// <param name="str">字符串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static string TrimSQL(this string str) { return ""; }
#endregion
#region 擴展SQL Lower
/// <summary>
/// 擴展SQL Lower
/// </summary>
/// <param name="str">字符串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static string LowerSQL(this string str) { return ""; }
#endregion
#region 擴展SQL Upper
/// <summary>
/// 擴展SQL Upper
/// </summary>
/// <param name="str">字符串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static string UpperSQL(this string str) { return ""; }
#endregion
#region 擴展SQL Count
/// <summary>
/// 擴展SQL Count
/// </summary>
/// <param name="str">字符串</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static int CountSQL(this object str) { return 0; }
#endregion
#region 擴展SQL MAX
/// <summary>
/// 擴展SQL MAX
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">字符串</param>
/// <returns></returns>
public static T MaxSQL<T>(this T obj) { return obj; }
#endregion
#region 擴展SQL MIN
/// <summary>
/// 擴展SQL MIN
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">字符串</param>
/// <returns></returns>
public static T MinSQL<T>(this T obj) { return obj; }
#endregion
#region 擴展SQL SUM
/// <summary>
/// 擴展SQL SUM
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">字符串</param>
/// <returns></returns>
public static T SumSQL<T>(this T obj) { return obj; }
#endregion
#region 擴展SQL AVG
/// <summary>
/// 擴展SQL AVG
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">字符串</param>
/// <returns></returns>
public static T AvgSQL<T>(this T obj) { return obj; }
#endregion
#region 設置字段別名
/// <summary>
/// 設置字段別名
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="ColumnName">別名</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:刪除未使用的參數", Justification = "<掛起>")]
public static T As<T>(this T obj, string ColumnName) { return obj; }
#endregion
#region 轉換類型
/// <summary>
/// 轉換類型
/// </summary>
/// <typeparam name="T">類型</typeparam>
/// <param name="obj">對象</param>
/// <param name="dataType">目標類型</param>
/// <returns></returns>
public static T CastSQL<T>(this T obj,string dataType) { return obj; }
#endregion
#region SQL參數化調整
/// <summary>
/// SQL參數化調整
/// </summary>
/// <param name="_">SQL語句</param>
/// <param name="dbProvider">數據庫驅動</param>
/// <returns></returns>
public static string SQLFormat(this string _, DbProviderType dbProvider)
{
switch (dbProvider)
{
case DbProviderType.Dameng:
return _.ReplacePattern(@"@((Sub_(\d+_)?)?ParamName\d+)", "?");
case DbProviderType.Oracle:
return _.ReplacePattern(@"@((Sub_(\d+_)?)?ParamName\d+)", ":$1");
}
return _;
}
#endregion
#endregion
}
下邊舉例演示
//內部方法
//查詢出分類名稱前邊包含 字符串 a 的數據
var category5 = category.Where(a => a.Name.StartsWith("a")).ToList();
//查詢出分類名第2位開始4個長度的字符是abcd的數據
var category6 = category.Where(a => a.Name.Substring(2, 4) == "abcd").ToList();
//查詢 出分類表中添加時間比現在時間少3個時小的 數據
var category7 = category.Where(a => a.AddDate.DateDiffSQL(DateTime.Now, "hh") == 3).ToList();
到這裡 IQueryableX 基本使用基本上講完了，以後還有擴展的 IQueryableQ 它是操作SQL體用的 比如 下邊的SQL
if(exists(select top 1 from TestTb where ID = 1))
delete from testTb where ID = 1;
以後會用一節 專一講 IQueryableQ的使用方法。
開源地址：//github.com/zhuovi/XiaoFeng
語雀地址：//www.yuque.com/fayelf/xiaofeng
開源不易，多多支持~
