重寫ExpressionVisitor完成LINQ查詢Where運算符表達式樹解析生成Sql腳本（Oracle版本）
眾所周知，ORM是一種為了解決面向?qū)ο缶幊膛c關(guān)系數(shù)據(jù)庫存在的互不匹配的現(xiàn)象的技術(shù)，其目標是基于面向?qū)ο缶幊陶Z言（如C#、Java等）持久化類及映射關(guān)系完成對數(shù)據(jù)庫操作（一般為讀操作與寫操作，也就是常說的增刪改查）。其中一個關(guān)鍵點則是如何生成關(guān)系數(shù)據(jù)庫能夠識別的Sql，此處只討論C#ORM實現(xiàn)中讀操作生成Sql，也就是如何完成C#中LINQ查詢到Sql的轉(zhuǎn)換。LINQ中定義了大量大查詢操作符，而基于數(shù)據(jù)庫查詢一般都需要使用Where子句，故在此我們進一步縮小討論范圍至LINQ的Where操作符。
針對某一持久化類T，執(zhí)行Where查詢的入?yún)⒖捎?code>Func<T,bool>表示，也就是我們需要基于入?yún)?code>Func<T,bool>生成對應查詢Sql語句。但基于以下兩點：
Func<T,bool>解析較為復雜，而.NET Framework中LINQ查詢涉及的各自操作對應的表達式樹被較為完整的定義，可通過重寫ExpressionVisitor實現(xiàn)解析轉(zhuǎn)換生成查詢Sql；.Where函數(shù)可接收Func<T,bool>或Expression<Func<T,bool>>入?yún)ⅲ唧w取決于數(shù)據(jù)源為IEnumerable<T>還是IQueryable<T>，考慮數(shù)據(jù)集成查詢定義接口為IQueryable<T>及后期兼容性，故將解析入?yún)⒃O(shè)置為表達式樹。
我們調(diào)整入?yún)?code>Expression<Func<T,bool>>，完成將其轉(zhuǎn)換為對應Sql查詢語句。
映射關(guān)系
基于自定義Attribute作用于持久化類屬性或持久化類建立面向?qū)ο缶幊陶Z言至關(guān)系型數(shù)據(jù)庫的映射關(guān)系
- 持久化類字段或?qū)傩杂成渲翑?shù)據(jù)庫字段，包括字段名、數(shù)據(jù)類型、是否可空、是否主鍵、默認值、字段備注等內(nèi)容。
using System;
namespace FXY.Code.ORM.Mapping
{
public class DbColumnMappingAttribute : Attribute
{
public string ProtertyName;
public string ColumnName;
public string DataType;
//public bool AllowNull;
//public bool IsKey;
//public string DefaultValue;
//public string DeafultFuncValue;
}
}
- 持久化類映射至數(shù)據(jù)庫表，包括數(shù)據(jù)庫名（用戶名）、表名、表備注、字段映射集合（表包含多個字段）等
using System;
using System.Collections.Generic;
namespace FXY.Code.ORM.Mapping
{
public class DbTableMappingAttribute : Attribute
{
public string EntityName;
public string TableName;
public string TableDescribtion;
public string DataBaseName;
public List<DbColumnMappingAttribute> DbColumns;
public Type EntityType;
}
}
基于持久化類獲取數(shù)據(jù)庫映射結(jié)果接口定義與默認實現(xiàn)
- 接口定義
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
namespace FXY.Code.ORM.Mapping
{
public interface IDataBaseMapping
{
/// <summary>
/// 獲取數(shù)據(jù)庫名稱
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <returns></returns>
string GetDbName<TSource>();
/// <summary>
/// 獲取數(shù)據(jù)庫名稱
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
string GetDbName(Type type);
/// <summary>
/// 獲取數(shù)據(jù)庫表名
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <returns></returns>
string GetDbTableName<TSource>();
/// <summary>
/// 獲取數(shù)據(jù)庫表名
/// </summary>
/// <typeparam name="type"></typeparam>
/// <returns></returns>
string GetDbTableName(Type type);
/// <summary>
/// 獲取數(shù)據(jù)庫字段名
/// </summary>
/// <param name="memberInfo"></param>
/// <returns></returns>
string GetColumnName(MemberInfo memberInfo);
}
}
觀察接口定義入?yún)⒓昂罄m(xù)默認實現(xiàn)可發(fā)現(xiàn)：入?yún)⒕鶠?code>System.Reflection命名空間下類，故獲取數(shù)據(jù)庫映射結(jié)果涉及運行時反射使用，存在一定程度的性能損失；
該處只考慮實現(xiàn)，暫不考慮性能，可以考慮將映射規(guī)則持久化（推薦使用XML或JSON等格式，且為文件格式保存，能夠?qū)崿F(xiàn)快速加載；不建議使用數(shù)據(jù)庫存儲，依賴度太高且讀取耗時長）及修改或自定義實現(xiàn)IDataBaseMapping替換默認實現(xiàn)DataBaseMapping，避免運行時反射調(diào)用的性能損失。
- 默認實現(xiàn)
using System;
using System.Data.Linq.Mapping;
using System.Reflection;
namespace FXY.Code.ORM.Mapping
{
/// <summary>
/// 數(shù)據(jù)庫與實體映射類
/// </summary>
public class DataBaseMapping : IDataBaseMapping
{
/// <summary>
/// 獲取數(shù)據(jù)庫名稱
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <returns></returns>
public virtual string GetDbName<TSource>()
{
return string.Empty;
}
/// <summary>
/// 獲取數(shù)據(jù)庫表名
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <returns></returns>
public virtual string GetDbTableName<TSource>()
{
return GetDbTableName(typeof(TSource));
}
/// <summary>
/// 獲取數(shù)據(jù)庫字段名
/// </summary>
/// <param name="memberInfo"></param>
/// <returns></returns>
public virtual string GetColumnName(MemberInfo memberInfo)
{
var columnAttribute = memberInfo.GetAttribute<DbColumnMappingAttribute>();
return (columnAttribute?.ColumnName ?? memberInfo.Name).ToUpper();
}
public string GetDbName(Type type)
{
var tableAttribute = type.GetAttribute<DbTableMappingAttribute>();
if (tableAttribute == null)
{
throw new NotSupportedException("實體尚未設(shè)置DbTableMappingAttribute特性，請先設(shè)置或重寫該方法。");
}
else
{
return tableAttribute.DataBaseName.ToUpper();
}
}
public string GetDbTableName(Type type)
{
var tableAttribute = type.GetAttribute<DbTableMappingAttribute>();
if (tableAttribute == null)
{
throw new NotSupportedException("實體尚未設(shè)置DbTableMappingAttribute特性，請先設(shè)置或重寫該方法。");
}
else
{
return tableAttribute.TableName.ToUpper();
}
}
}
}
擴展類，查看源碼
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
namespace FXY.Code.ORM.Mapping
{
public static class MappingExpression
{
public static object GetValue(this PropertyInfo propertyInfo, object obj)
{
return propertyInfo.GetValue(obj, null);
}
public static TAttribute GetAttribute<TAttribute>(this MemberInfo memberInfo)
where TAttribute : Attribute
{
var attributes = memberInfo.GetCustomAttributes(typeof(TAttribute), true);
if (attributes != null && attributes.Length > 0)
{
return attributes[0] as TAttribute;
}
return null;
}
public static TAttribute GetAttribute<TAttribute>(this Type type)
where TAttribute : Attribute
{
var attributes = type.GetCustomAttributes(typeof(TAttribute), true);
TAttribute attribute = attributes != null && attributes.Length > 0 ? attributes[0] as TAttribute : null;
return attribute;
}
public static void Add(this StringBuilder sb, string str)
{
}
public static IEnumerable<T> LastSkip<T>(this IEnumerable<T> equatable, int count)
{
return equatable.Take(equatable.Count() - count);
}
public static string RemoveFirstAndLastChar(this string str, char skip = '\'')
{
var list = str as IEnumerable<char>;
if (list?.FirstOrDefault() == skip)
{
list = list.Skip(1);
}
if (list?.LastOrDefault() == skip)
{
list = list.LastSkip(1);
}
return new string(list.ToArray());
}
}
}
表達式樹解析
using FXY.Code.ORM.Mapping;
using System;
using System.Collections.Generic;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text.RegularExpressions;
namespace FXY.Code.ORM.ExpressionVisit
{
public class WhereExpressionVisitor : ExpressionVisitor
{
/// <summary>
/// 是否啟用數(shù)據(jù)庫字段與實體屬性之間的映射關(guān)系。
/// </summary>
private bool useColumnAttributeMap;
/// <summary>
/// 是否啟用參數(shù)化
/// </summary>
private bool useDbParamter;
/// <summary>
/// 是否生成完整的查詢語句
/// </summary>
private bool generateFullQuery;
/// <summary>
/// 參數(shù)化字典
/// </summary>
private Dictionary<string, object> dbParameters = new Dictionary<string, object>();
/// <summary>
/// 參數(shù)化關(guān)鍵字，如oracle為":Id"格式
/// </summary>
private string dbParamterKey = "";
/// <summary>
/// 是否啟用表別名
/// </summary>
private bool useAlias;
private IDataBaseMapping dataBaseMapper;
/// <summary>
/// 構(gòu)造函數(shù)
/// </summary>
/// <param name="useColumnAttributeMap">是否啟用<see cref="DbColumnMappingAttribute"/>特性建立屬性與數(shù)據(jù)庫字段之間的映射關(guān)系。</param>
/// <param name="generateFullQuery">是否生成完成的數(shù)據(jù)庫查詢語句，false不會返回SELECT * FROM {TABLE} WHERE部分。</param>
/// <param name="useDbParamter">是否啟用參數(shù)化</param>
/// <param name="dbParamterKey">參數(shù)化</param>
public WhereExpressionVisitor(bool useColumnAttributeMap,
bool generateFullQuery,
bool useDbParamter,
string dbParamterKey,
bool useAlias,
IDataBaseMapping dataBaseMapping = null)
{
this.useColumnAttributeMap = useColumnAttributeMap;
this.generateFullQuery = generateFullQuery;
this.useDbParamter = useDbParamter;
this.dbParamterKey = dbParamterKey;
this.useAlias = useAlias;
this.dataBaseMapper = dataBaseMapping ?? new DataBaseMapping();
}
/// 構(gòu)造函數(shù)
/// </summary>
public WhereExpressionVisitor()
: base()
{
}
public void SetDataBaseMapper<T>() where T : DataBaseMapping, new()
{
this.dataBaseMapper = new T();
}
/// <summary>
/// 獲取參數(shù)化字典
/// </summary>
/// <returns></returns>
public Dictionary<string, object> GetDbParamter() => dbParameters;
/// <summary>
/// 將表達式樹解析為數(shù)據(jù)查詢語句
/// </summary>
/// <typeparam name="TSource">實體</typeparam>
/// <param name="expression">表達式樹</param>
/// <returns></returns>
public string ToSql<TSource>(Expression<Func<TSource, bool>> expression)
{
string result = "";
dbParameters.Clear();
if (generateFullQuery)
{
string dbName = dataBaseMapper.GetDbName<TSource>();
string tableName = dataBaseMapper.GetDbTableName<TSource>();
if (!string.IsNullOrWhiteSpace(dbName))
{
tableName = $"{dbName}.{tableName}";
};
result += $@"SELECT * FROM {tableName}";
if (useAlias)
{
LambdaExpression lambdaExpression = expression;
string tableParamterName = lambdaExpression.Parameters[0].Name;
result += $@" {tableParamterName}";
}
result += $@" WHERE ";
}
result += Visit(expression.Body);
return result;
}
public new string Visit(Expression node)
{
if (node is BinaryExpression binaryExpression)
{
return VisitBinary(binaryExpression);
}
else if (node is ConditionalExpression conditionalExpression)
{
return VisitConditional(conditionalExpression);
}
else if (node is ConstantExpression constantExpression)
{
return VisitConstant(constantExpression);
}
else if (node is MemberExpression memberExpression)
{
return VisitMember(memberExpression);
}
else if (node is ParameterExpression parameterExpression)
{
return VisitParameter(parameterExpression);
}
else if (node is MethodCallExpression methodCallExpression)
{
return VisitMethodCall(methodCallExpression);
}
else if (node is UnaryExpression unaryExpression)
{
return VisitUnary(unaryExpression);
}
else if (node is NewExpression newExpression)
{
return VisitNew(newExpression);
}
throw new NotSupportedException();
}
protected new string VisitBinary(BinaryExpression node)
{
if (node == null)
{
return string.Empty;
}
switch (node.NodeType)
{
/*條件布爾運算*/
case ExpressionType.AndAlso: return $"({Visit(node.Left)}) AND ({Visit(node.Right)})";
case ExpressionType.OrElse: return $"({Visit(node.Left)}) OR ({Visit(node.Right)})";
/*比較運算*/
case ExpressionType.GreaterThan: return $"{Visit(node.Left)}>{Visit(node.Right)}";
case ExpressionType.GreaterThanOrEqual: return $"{Visit(node.Left)}>={Visit(node.Right)}";
case ExpressionType.Equal: return $"{Visit(node.Left)}={Visit(node.Right)}";
case ExpressionType.NotEqual: return $"{Visit(node.Left)}<>{Visit(node.Right)}";
case ExpressionType.LessThan: return $"{Visit(node.Left)}<{Visit(node.Right)}";
case ExpressionType.LessThanOrEqual: return $"{Visit(node.Left)}<={Visit(node.Right)}";
/*算術(shù)運算*/
case ExpressionType.Add: return Visit(Expression.Call(node.Method, node.Left, node.Right));
case ExpressionType.AddChecked: return Visit(Expression.Call(node.Method, node.Left, node.Right));
case ExpressionType.Divide: return Visit(Expression.Call(node.Method, node.Left, node.Right));
case ExpressionType.Modulo: return Visit(Expression.Call(node.Method, node.Left, node.Right));
case ExpressionType.Multiply: return Visit(Expression.Call(node.Method, node.Left, node.Right));
case ExpressionType.MultiplyChecked: return Visit(Expression.Call(node.Method, node.Left, node.Right));
case ExpressionType.Subtract: return Visit(Expression.Call(node.Method, node.Left, node.Right));
case ExpressionType.SubtractChecked: return Visit(Expression.Call(node.Method, node.Left, node.Right));
///*移位運算*/
//case ExpressionType.LeftShift:
//case ExpressionType.RightShift:
/*合并運算*/
case ExpressionType.Coalesce: return $" NVL({Visit(node.Left)},{Visit(node.Right)})";
///*數(shù)組索引操作*/
//case ExpressionType.ArrayIndex:
default: throw new NotSupportedException($"二元表達式樹節(jié)點類型[{node.NodeType}]暫不支持解析。");
}
}
protected new string VisitConditional(ConditionalExpression node)
{
if (node.Test is BinaryExpression b)
{
return $" (CASE {Visit(b.Left)} WHEN {Visit(b.Right)} THEN {Visit(node.IfTrue)} ELSE {Visit(node.IfFalse)} END)";
}
throw new NotSupportedException();
}
protected new string VisitConstant(ConstantExpression node)
{
if (node == null
|| node.Value == null)
{
throw new NotSupportedException($"常量表達式樹解析失敗，不允許為空。");
}
Type type = node.Value.GetType();
string result = "";
if (type == typeof(string)
|| type == typeof(char))
{
result = $"'{node.Value}'";
}
else if (type == typeof(short)
|| type == typeof(int)
|| type == typeof(long)
|| type == typeof(float)
|| type == typeof(double)
|| type == typeof(decimal))
{
result = $"{node.Value}";
}
else if (type == typeof(DateTime))
{
DateTime.TryParse(node.Value.ToString(), out DateTime datetime);
result = $"TO_DATE('{datetime.ToString("yyyy-MM-dd HH:mm:ss")}','yyyy-MM-dd hh24:mi:ss')";
}
else
{
throw new NotSupportedException($"暫不支持[{type.FullName}]常量類型的解析。");
}
if (useDbParamter)
{
string paramName = $"{dbParamterKey}Param{dbParameters.Count}";
object paramValue = result;
dbParameters.Add(paramName, paramValue);
result = paramName;
}
return result;
}
protected new string VisitMember(MemberExpression node)
{
if (node == null)
{
return string.Empty;
}
if (node.Expression is ConstantExpression constantExpression)
{
object value = null;
if (node.Member is PropertyInfo propertyInfo)
{
value = propertyInfo.GetValue(constantExpression.Value);
}
if (node.Member is FieldInfo fieldInfo)
{
value = fieldInfo.GetValue(constantExpression.Value);
}
return Visit(Expression.Constant(value));
}
if (node.Expression is ParameterExpression parameterExpression)
{
string result = "";
if (useAlias)
{
result += $"{Visit(parameterExpression)}.";
}
string columnName = !useColumnAttributeMap
? node.Member.Name
: dataBaseMapper.GetColumnName(node.Member);
result += $"{columnName.ToUpper()}";
return result;
}
throw new NotSupportedException($"暫不支持[{node.Expression.ToString()}]MemberExpression的解析。");
}
protected new string VisitMethodCall(MethodCallExpression node)
{
switch (node.Method.ReturnType.Name)
{
case "Boolean": return VisitMethodCallReturnBoolean(node);
case "String": return VisitMethodCallReturnString(node);
default: return "";
}
}
protected new string VisitParameter(ParameterExpression node)
{
return useAlias ? $"{node.Name}" : string.Empty;
}
protected new string VisitUnary(UnaryExpression node)
{
return Visit(node.Operand);
}
protected new string VisitNew(NewExpression node)
{
if (node.Type == typeof(DateTime))
{
int hour = 0;
int minute = 0;
int second = 0;
int.TryParse(node.Arguments[0].ToString(), out int year);
int.TryParse(node.Arguments[1].ToString(), out int month);
int.TryParse(node.Arguments[2].ToString(), out int day);
if (node.Arguments.Count > 3)
{
int.TryParse(node.Arguments[3].ToString(), out hour);
int.TryParse(node.Arguments[4].ToString(), out minute);
int.TryParse(node.Arguments[5].ToString(), out second);
}
DateTime dateTime = new DateTime(year, month, day, hour, minute, second);
return $"TO_DATE('{dateTime.ToString("yyyy-MM-dd HH:mm:ss")}','yyyy-MM-dd hh24:mi:ss')";
}
return string.Empty;
}
protected new string VisitBlock(BlockExpression node) { throw new NotSupportedException(); }
protected new string VisitDebugInfo(DebugInfoExpression node) { throw new NotSupportedException(); }
protected new string VisitDefault(DefaultExpression node) { throw new NotSupportedException(); }
protected new string VisitDynamic(DynamicExpression node) { throw new NotSupportedException(); }
protected new string VisitExtension(Expression node) { throw new NotSupportedException(); }
protected new string VisitGoto(GotoExpression node) { throw new NotSupportedException(); }
protected new string VisitIndex(IndexExpression node) { throw new NotSupportedException(); }
protected new string VisitInvocation(InvocationExpression node) { throw new NotSupportedException(); }
protected new string VisitLabel(LabelExpression node) { throw new NotSupportedException(); }
protected new string VisitLambda<T>(Expression<T> node) { throw new NotSupportedException(); }
protected new string VisitListInit(ListInitExpression node) { throw new NotSupportedException(); }
protected new string VisitLoop(LoopExpression node) { throw new NotSupportedException(); }
protected new string VisitMemberInit(MemberInitExpression node) { throw new NotSupportedException(); }
protected new string VisitNewArray(NewArrayExpression node) { throw new NotSupportedException(); }
protected new string VisitRuntimeVariables(RuntimeVariablesExpression node) { throw new NotSupportedException(); }
protected new string VisitSwitch(SwitchExpression node) { throw new NotSupportedException(); }
protected new string VisitTry(TryExpression node) { throw new NotSupportedException(); }
protected new string VisitTypeBinary(TypeBinaryExpression node) { throw new NotSupportedException(); }
#region VisitMethodCall By Return Type
public virtual string VisitMethodCallReturnBoolean(MethodCallExpression node)
{
switch (node.Method.Name)
{
case "StartsWith":
return $"{Visit(node.Object)} LIKE { string.Join("", Visit(node.Arguments[0]).LastSkip(1))}%'";
case "EndsWith":
return $"{Visit(node.Object)} LIKE '%{ string.Join("", Visit(node.Arguments[0]).Skip(1).LastSkip(1))}'";
case "Equals":
return $"{Visit(node.Object)}={string.Join("", Visit(node.Arguments[0]))}";
case "Contains":
return $"{ Visit(node.Object)} LIKE '%{ string.Join("", Visit(node.Arguments[0]).Skip(1).LastSkip(1))}%'";
default:
return string.Empty;
}
}
public virtual string VisitMethodCallReturnString(MethodCallExpression node)
{
switch (node.Method.Name)
{
case "Concat":
return $"'{string.Join("", node.Arguments.Select(p => Visit(p).RemoveFirstAndLastChar()))}'";
case "Format":
var array1 = Regex.Split(node.Arguments[0].ToString().Substring(1), "{[0-9]+}");
var array2 = node.Arguments.Skip(1).ToList();
if (node.Arguments.Count > 1 && node.Arguments[1] is NewArrayExpression newArrayExpression)
{
array2 = newArrayExpression.Expressions.ToList();
}
return $@"'{string.Join("",
array2.Select(p => array1[array2.IndexOf(p)] + Visit(p).RemoveFirstAndLastChar()))}'";
case "ToLower": return $"LOWER({Visit(node.Arguments[0])})";
case "ToUpper": return $"UPPER({Visit(node.Arguments[0])})";
default: return string.Empty;
}
}
#endregion
}
}
擴展輔助類
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
namespace FXY.Code.ORM.ExpressionVisit
{
public static class ExtensionExpression
{
public static object GetValue(this PropertyInfo propertyInfo, object obj)
{
return propertyInfo.GetValue(obj, null);
}
public static TAttribute GetAttribute<TAttribute>(this MemberInfo memberInfo)
where TAttribute : Attribute
{
var attributes = memberInfo.GetCustomAttributes(typeof(TAttribute), true);
if (attributes != null && attributes.Length > 0)
{
return attributes[0] as TAttribute;
}
return null;
}
public static TAttribute GetAttribute<TAttribute>(this Type type)
where TAttribute : Attribute
{
var attributes = type.GetCustomAttributes(typeof(TAttribute), true);
TAttribute attribute = attributes != null && attributes.Length > 0 ? attributes[0] as TAttribute : null;
return attribute;
}
public static void Add(this StringBuilder sb, string str)
{
}
public static IEnumerable<T> LastSkip<T>(this IEnumerable<T> equatable, int count)
{
return equatable.Take(equatable.Count() - count);
}
public static string RemoveFirstAndLastChar(this string str, char skip = '\'')
{
var list = str as IEnumerable<char>;
if (list?.FirstOrDefault() == skip)
{
list = list.Skip(1);
}
if (list?.LastOrDefault() == skip)
{
list = list.LastSkip(1);
}
return new string(list.ToArray());
}
}
}
單元測試類
單元測試持久化類
using FXY.Code.ORM.Mapping;
namespace FXY.Code.ORM.Entity
{
[DbTableMapping(DataBaseName = "TEST", TableName = "PATIENT_INFO_1")]
public class PatientInfo1
{
[DbColumnMapping(ColumnName = "PATIENT_ID", DataType = "VARCHAR(30)")]
public string ID { get; set; }
[DbColumnMapping(ColumnName = "PATIENT_NAME", DataType = "VARCHAR(30)")]
public string Name { get; set; }
}
//[DbTableMapping(DataBaseName = "TEST", TableName = "PATIENT_INFO_2")]
//public class PatientInfo2
//{
// [DbColumnMapping(ColumnName = "PATIENT_ID", DataType = "VARCHAR(30)")]
// public string ID { get; set; }
// [DbColumnMapping(ColumnName = "PATIENT_NAME", DataType = "VARCHAR(30)")]
// public string Name { get; set; }
// [DbColumnMapping(ColumnName = "PATIENT_AGE", DataType = "NUMBER(36)")]
//public int Age { get; set; }
//}
//[DbTableMapping(DataBaseName = "TEST", TableName = "PATIENT_INFO_3")]
//public class PatientInfo3
//{
// [DbColumnMapping(ColumnName = "PATIENT_ID", DataType = "VARCHAR(30)")]
// public string ID { get; set; }
// [DbColumnMapping(ColumnName = "PATIENT_NAME", DataType = "VARCHAR(30)")]
// public string Name { get; set; }
// //[DbColumnMapping(ColumnName = "PATIENT_AGE", DataType = "NUMBER(36)")]
// //public int Age { get; set; }
//}
}
基本流程測試
測試用例中所謂參數(shù)，是基于：
- 是否啟用基于ColumnAttribute特性建立屬性與數(shù)據(jù)庫字段之間映射關(guān)系；
- 是否生成完成的數(shù)據(jù)庫查詢語句，false不會返回SELECT * FROM {TABLE} WHERE部分;
- 是否啟用參數(shù)化；
- 是否啟用表別名；
SingleParamVisitorUnitTest：單參數(shù)基本流程測試
using FXY.Code.ORM.ExpressionVisit;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
namespace FXY.Code.ORM.ExpressionVisit.Test
{
[TestClass]
public class SingleParamVisitorUnitTest
{
private WhereExpressionVisitor visitor = new WhereExpressionVisitor();
#region 單參數(shù)測試
[TestMethod]
public void BaseUseColumnMap()
{
visitor = new WhereExpressionVisitor(true, false, false, "", false);
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == "20"), "PATIENT_ID='20'");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Age > 20 && p.Sex == 1),
"(PATIENT_AGE>20) AND (PATIENT_SEX=1)");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Age > 20
&& p.Sex == 1
&& p.TelePhone.StartsWith("135")),
"((PATIENT_AGE>20) AND (PATIENT_SEX=1)) AND (PATIENT_TELEPHONE LIKE '135%')");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Age > 20
&& p.Sex == 1
&& (p.TelePhone.StartsWith("135") || p.Province.Contains("云南"))),
"((PATIENT_AGE>20) AND (PATIENT_SEX=1)) AND ((PATIENT_TELEPHONE LIKE '135%') OR (PATIENT_PROVINCE LIKE '%云南%'))");
}
[TestMethod]
public void BaseGenetateFullQuery()
{
visitor = new WhereExpressionVisitor(false, true, false, "", false);
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == "20"),
"SELECT * FROM PATIENT WHERE ID='20'");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Age > 20 && p.Sex == 1),
"SELECT * FROM PATIENT WHERE (AGE>20) AND (SEX=1)");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Age > 20
&& p.Sex == 1
&& p.TelePhone.StartsWith("135")),
"SELECT * FROM PATIENT WHERE ((AGE>20) AND (SEX=1)) AND (TELEPHONE LIKE '135%')");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Age > 20
&& p.Sex == 1
&& (p.TelePhone.StartsWith("135") || p.Province.Contains("云南"))),
"SELECT * FROM PATIENT WHERE ((AGE>20) AND (SEX=1)) AND ((TELEPHONE LIKE '135%') OR (PROVINCE LIKE '%云南%'))");
}
[TestMethod]
public void BaseUseDbParamter()
{
visitor = new WhereExpressionVisitor(false, false, true, ":", false);
{
string sql = visitor.ToSql<PatientInfo>(p => p.ID == "20");
var dbPramaters = visitor.GetDbParamter();
Assert.AreEqual(sql, "ID=:Param0");
Assert.IsTrue(dbPramaters != null && dbPramaters.Count == 1 && dbPramaters[":Param0"].ToString() == "'20'");
}
{
//Assert.ThrowsException<NotSupportedException>(() =>
//{
// visitor.ToSql<PatientInfo>(p => p.Age > 20
// && p.Sex == 1
// && (p.TelePhone.StartsWith("135") || p.Province.Contains("云南")));
//});
string sql = visitor.ToSql<PatientInfo>(p => p.Age > 20 && p.Sex == 1);
var dbPramaters = visitor.GetDbParamter();
Assert.AreEqual(sql, "(AGE>:Param0) AND (SEX=:Param1)");
Assert.IsTrue(dbPramaters != null
&& dbPramaters.Count == 2
&& Convert.ToInt32(dbPramaters[":Param0"].ToString()) == 20
&& Convert.ToInt32(dbPramaters[":Param1"].ToString()) == 1);
}
}
[TestMethod]
public void BaseUseAlias()
{
visitor = new WhereExpressionVisitor(false, false, false, "", true);
/*單條件*/
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.Age > 20), "p.AGE>20");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.Age >= 20), "p.AGE>=20");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.Age == 20), "p.AGE=20");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.Age != 20), "p.AGE<>20");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.Age < 20), "p.AGE<20");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.Age <= 20), "p.AGE<=20");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.TelePhone.StartsWith("135")), "p.TELEPHONE LIKE '135%'");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.TelePhone.EndsWith("135")), "p.TELEPHONE LIKE '%135'");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.TelePhone.Contains("135")), "p.TELEPHONE LIKE '%135%'");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.TelePhone.Equals("w")), "p.TELEPHONE='w'");
/*多條件*/
Assert.AreEqual(
visitor.ToSql<PatientInfo>(
p => p.Age > 20 && p.Sex == 1),
"(p.AGE>20) AND (p.SEX=1)");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(
p => p.Age > 20 && p.Sex == 1 && p.TelePhone.StartsWith("135")),
"((p.AGE>20) AND (p.SEX=1)) AND (p.TELEPHONE LIKE '135%')");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(
p => p.Age > 20 && p.Sex == 1 && (p.TelePhone.StartsWith("135") || p.Province.Contains("云南"))),
"((p.AGE>20) AND (p.SEX=1)) AND ((p.TELEPHONE LIKE '135%') OR (p.PROVINCE LIKE '%云南%'))");
}
#endregion
}
}
TwoParamVisitorUnitTest：兩參數(shù)流程流程測試
using FXY.Code.ORM.ExpressionVisit;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
namespace FXY.Code.ORM.ExpressionVisit.Test
{
[TestClass]
public class TwoParamVisitorUnitTest
{
private WhereExpressionVisitor visitor = new WhereExpressionVisitor();
#region 兩參數(shù)測試
[TestMethod]
public void BaseUseColumnMapAndFullQuery()
{
visitor = new WhereExpressionVisitor(true, true, false, "", false);
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == "20"), "SELECT * FROM PATIENT WHERE PATIENT_ID='20'");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Age > 20 && p.Sex == 1),
"SELECT * FROM PATIENT WHERE (PATIENT_AGE>20) AND (PATIENT_SEX=1)");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Age > 20
&& p.Sex == 1
&& p.TelePhone.StartsWith("135")),
"SELECT * FROM PATIENT WHERE ((PATIENT_AGE>20) AND (PATIENT_SEX=1)) AND (PATIENT_TELEPHONE LIKE '135%')");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Age > 20
&& p.Sex == 1
&& (p.TelePhone.StartsWith("135") || p.Province.Contains("云南"))),
"SELECT * FROM PATIENT WHERE ((PATIENT_AGE>20) AND (PATIENT_SEX=1)) AND ((PATIENT_TELEPHONE LIKE '135%') OR (PATIENT_PROVINCE LIKE '%云南%'))");
}
[TestMethod]
public void BaseUseColumnMapAndDbParamter()
{
visitor = new WhereExpressionVisitor(true, false, true, ":", false);
{
string sql = visitor.ToSql<PatientInfo>(p => p.ID == "20");
var dbPramaters = visitor.GetDbParamter();
Assert.AreEqual(sql, "PATIENT_ID=:Param0");
Assert.IsTrue(dbPramaters != null && dbPramaters.Count == 1 && dbPramaters[":Param0"].ToString() == "'20'");
}
{
//Assert.ThrowsException<NotSupportedException>(() =>
//{
// visitor.ToSql<PatientInfo>(p => p.Age > 20
// && p.Sex == 1
// && (p.TelePhone.StartsWith("135") || p.Province.Contains("云南")));
//});
string sql = visitor.ToSql<PatientInfo>(p => p.Age > 20 && p.Sex == 1);
var dbPramaters = visitor.GetDbParamter();
Assert.AreEqual(sql, "(PATIENT_AGE>:Param0) AND (PATIENT_SEX=:Param1)");
Assert.IsTrue(dbPramaters != null
&& dbPramaters.Count == 2
&& Convert.ToInt32(dbPramaters[":Param0"].ToString()) == 20
&& Convert.ToInt32(dbPramaters[":Param1"].ToString()) == 1);
}
}
[TestMethod]
public void BaseUseColumnMapAndAlias()
{
visitor = new WhereExpressionVisitor(true, false, false, "", true);
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == "20"), "p.PATIENT_ID='20'");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Age > 20 && p.Sex == 1),
"(p.PATIENT_AGE>20) AND (p.PATIENT_SEX=1)");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Age > 20
&& p.Sex == 1
&& p.TelePhone.StartsWith("135")),
"((p.PATIENT_AGE>20) AND (p.PATIENT_SEX=1)) AND (p.PATIENT_TELEPHONE LIKE '135%')");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Age > 20
&& p.Sex == 1
&& (p.TelePhone.StartsWith("135") || p.Province.Contains("云南"))),
"((p.PATIENT_AGE>20) AND (p.PATIENT_SEX=1)) AND ((p.PATIENT_TELEPHONE LIKE '135%') OR (p.PATIENT_PROVINCE LIKE '%云南%'))");
}
[TestMethod]
public void BaseGenetateFullQueryAndDbParamter()
{
visitor = new WhereExpressionVisitor(false, true, true, ":", false);
{
string sql = visitor.ToSql<PatientInfo>(p => p.ID == "20");
var dbPramaters = visitor.GetDbParamter();
Assert.AreEqual(sql, "SELECT * FROM PATIENT WHERE ID=:Param0");
Assert.IsTrue(dbPramaters != null && dbPramaters.Count == 1 && dbPramaters[":Param0"].ToString() == "'20'");
}
{
//Assert.ThrowsException<NotSupportedException>(() =>
//{
// visitor.ToSql<PatientInfo>(p => p.Age > 20
// && p.Sex == 1
// && (p.TelePhone.StartsWith("135") || p.Province.Contains("云南")));
//});
string sql = visitor.ToSql<PatientInfo>(p => p.Age > 20 && p.Sex == 1);
var dbPramaters = visitor.GetDbParamter();
Assert.AreEqual(sql, "SELECT * FROM PATIENT WHERE (AGE>:Param0) AND (SEX=:Param1)");
Assert.IsTrue(dbPramaters != null
&& dbPramaters.Count == 2
&& Convert.ToInt32(dbPramaters[":Param0"].ToString()) == 20
&& Convert.ToInt32(dbPramaters[":Param1"].ToString()) == 1);
}
}
[TestMethod]
public void BaseGenetateFullQueryAndUseAlias()
{
visitor = new WhereExpressionVisitor(false, true, false, "", true);
/*不啟用字段映射，不生成完整查詢語句，不使用參數(shù)化，使用表別名，單條件*/
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.Age > 20), "SELECT * FROM PATIENT p WHERE p.AGE>20");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.Age >= 20), "SELECT * FROM PATIENT p WHERE p.AGE>=20");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.Age == 20), "SELECT * FROM PATIENT p WHERE p.AGE=20");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.Age != 20), "SELECT * FROM PATIENT p WHERE p.AGE<>20");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.Age < 20), "SELECT * FROM PATIENT p WHERE p.AGE<20");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.Age <= 20), "SELECT * FROM PATIENT p WHERE p.AGE<=20");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.TelePhone.StartsWith("135")), "SELECT * FROM PATIENT p WHERE p.TELEPHONE LIKE '135%'");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.TelePhone.EndsWith("135")), "SELECT * FROM PATIENT p WHERE p.TELEPHONE LIKE '%135'");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.TelePhone.Contains("135")), "SELECT * FROM PATIENT p WHERE p.TELEPHONE LIKE '%135%'");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.TelePhone.Equals("w")), "SELECT * FROM PATIENT p WHERE p.TELEPHONE='w'");
/*不啟用字段映射，不生成完整查詢語句，不使用參數(shù)化，使用表別名，多條件*/
Assert.AreEqual(
visitor.ToSql<PatientInfo>(
p => p.Age > 20 && p.Sex == 1),
"SELECT * FROM PATIENT p WHERE (p.AGE>20) AND (p.SEX=1)");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(
p => p.Age > 20 && p.Sex == 1 && p.TelePhone.StartsWith("135")),
"SELECT * FROM PATIENT p WHERE ((p.AGE>20) AND (p.SEX=1)) AND (p.TELEPHONE LIKE '135%')");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(
p => p.Age > 20 && p.Sex == 1 && (p.TelePhone.StartsWith("135") || p.Province.Contains("云南"))),
"SELECT * FROM PATIENT p WHERE ((p.AGE>20) AND (p.SEX=1)) AND ((p.TELEPHONE LIKE '135%') OR (p.PROVINCE LIKE '%云南%'))");
}
[TestMethod]
public void BaseUseDbParamterAndUseAlias()
{
visitor = new WhereExpressionVisitor(false, false, true, ":", true);
{
string sql = visitor.ToSql<PatientInfo>(p => p.ID == "20");
var dbPramaters = visitor.GetDbParamter();
Assert.AreEqual(sql, "p.ID=:Param0");
Assert.IsTrue(dbPramaters != null && dbPramaters.Count == 1 && dbPramaters[":Param0"].ToString() == "'20'");
}
{
//Assert.ThrowsException<NotSupportedException>(() =>
//{
// visitor.ToSql<PatientInfo>(p => p.Age > 20
// && p.Sex == 1
// && (p.TelePhone.StartsWith("135") || p.Province.Contains("云南")));
//});
string sql = visitor.ToSql<PatientInfo>(p => p.Age > 20 && p.Sex == 1);
var dbPramaters = visitor.GetDbParamter();
Assert.AreEqual(sql, "(p.AGE>:Param0) AND (p.SEX=:Param1)");
Assert.IsTrue(dbPramaters != null
&& dbPramaters.Count == 2
&& Convert.ToInt32(dbPramaters[":Param0"].ToString()) == 20
&& Convert.ToInt32(dbPramaters[":Param1"].ToString()) == 1);
}
}
#endregion
}
}
ThreeParamVisitorUnitTest：三參數(shù)基本流程測試
using FXY.Code.ORM.ExpressionVisit;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace FXY.Code.ORM.ExpressionVisit.Test
{
[TestClass]
public class ThreeParamVisitorUnitTest
{
private WhereExpressionVisitor visitor = new WhereExpressionVisitor();
#region 三參數(shù)測試
[TestMethod]
public void BaseUseColumnMapAndFullQueryAndDbParamter()
{
visitor = new WhereExpressionVisitor(true, true, true, ":", false);
string sql = visitor.ToSql<PatientInfo>(p => p.ID == "20");
var dbPramaters = visitor.GetDbParamter();
Assert.AreEqual(sql, "SELECT * FROM PATIENT WHERE PATIENT_ID=:Param0");
Assert.IsTrue(dbPramaters != null && dbPramaters.Count == 1 && dbPramaters[":Param0"].ToString() == "'20'");
}
[TestMethod]
public void BaseUseColumnMapAndFullQueryAndAlias()
{
visitor = new WhereExpressionVisitor(true, true, false, "", true);
string sql = visitor.ToSql<PatientInfo>(p => p.ID == "20");
Assert.AreEqual(sql, "SELECT * FROM PATIENT p WHERE p.PATIENT_ID='20'");
}
[TestMethod]
public void BaseUseFullQueryAndDbParamterAndAlias()
{
visitor = new WhereExpressionVisitor(false, true, true, ":", true);
string sql = visitor.ToSql<PatientInfo>(p => p.ID == "20");
var dbPramaters = visitor.GetDbParamter();
Assert.AreEqual(sql, "SELECT * FROM PATIENT p WHERE p.ID=:Param0");
Assert.IsTrue(dbPramaters != null && dbPramaters.Count == 1 && dbPramaters[":Param0"].ToString() == "'20'");
}
[TestMethod]
public void BaseUseColumnMapAndDbParamterAndAlias()
{
visitor = new WhereExpressionVisitor(true, false, true, ":", true);
string sql = visitor.ToSql<PatientInfo>(p => p.ID == "20");
var dbPramaters = visitor.GetDbParamter();
Assert.AreEqual(sql, "p.PATIENT_ID=:Param0");
Assert.IsTrue(dbPramaters != null && dbPramaters.Count == 1 && dbPramaters[":Param0"].ToString() == "'20'");
}
#endregion
}
}
FourParamVisitorUnitTest：四參數(shù)基本流程測試
using FXY.Code.ORM.ExpressionVisit;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace FXY.Code.ORM.ExpressionVisit.Test
{
[TestClass]
public class FourParamVisitorUnitTest
{
private WhereExpressionVisitor visitor = new WhereExpressionVisitor();
#region 四參數(shù)測試
[TestMethod]
public void BaseUseColumnMapAndFullQueryAndDbParamterAndAlias()
{
visitor = new WhereExpressionVisitor(true, true, true, ":", true);
string sql = visitor.ToSql<PatientInfo>(p => p.ID == "20");
var dbPramaters = visitor.GetDbParamter();
Assert.AreEqual(sql, "SELECT * FROM PATIENT p WHERE p.PATIENT_ID=:Param0");
Assert.IsTrue(dbPramaters != null && dbPramaters.Count == 1 && dbPramaters[":Param0"].ToString() == "'20'");
}
#endregion
}
}
其他測試
CoalesceUnitTest：二元空值運算符，格式為：a??b
using FXY.Code.ORM.ExpressionVisit;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace FXY.Code.ORM.ExpressionVisit.Test
{
[TestClass]
public class CoalesceUnitTest
{
private WhereExpressionVisitor visitor = new WhereExpressionVisitor(true, false, false, "", false);
[TestMethod]
public void CoalesceTest()
{
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.City == (p.Province ?? "成都")),
"PATIENT_CITY= NVL(PATIENT_PROVINCE,'成都')");
}
}
}
ConditionalExpressionUnitTest：三元條件運算符，語法為：條件表達式？表達式1：表達式2
using FXY.Code.ORM.ExpressionVisit.Test;
using FXY.Code.ORM.ExpressionVisit;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace FXY.Code.ORM.ExpressionVisit.Test
{
[TestClass]
public class ConditionalExpressionUnitTest
{
private WhereExpressionVisitor visitor = new WhereExpressionVisitor(true, false, false, "", false);
[TestMethod]
public void BaseTest()
{
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Age == (p.Sex == 1 ? 22 : 20)),
"PATIENT_AGE= (CASE PATIENT_SEX WHEN 1 THEN 22 ELSE 20 END)");
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.City == (p.Sex == 1 ? "成都" : "昆明")),
"PATIENT_CITY= (CASE PATIENT_SEX WHEN 1 THEN '成都' ELSE '昆明' END)");
}
}
}
DateTimeCompareUnitTest：時間比較
using FXY.Code.ORM.ExpressionVisit;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
namespace FXY.Code.ORM.ExpressionVisit.Test
{
[TestClass]
public class DateTimeCompareUnitTest
{
private WhereExpressionVisitor visitor = new WhereExpressionVisitor(true, false, false, "", false);
[TestMethod]
public void BaseDatetieCompareByNew1()
{
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Birthday > new DateTime(1900, 01, 01)),
"PATIENT_BIRTHDAY>TO_DATE('1900-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss')");
}
[TestMethod]
public void BaseDatetieCompareByNew2()
{
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Birthday > new DateTime(1900, 01, 01, 2, 3, 4)),
"PATIENT_BIRTHDAY>TO_DATE('1900-01-01 02:03:04','yyyy-MM-dd hh24:mi:ss')");
}
[TestMethod]
public void BaseDatetieCompareByConstant1()
{
DateTime dateTime = new DateTime(1900, 01, 01);
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Birthday > dateTime),
"PATIENT_BIRTHDAY>TO_DATE('1900-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss')");
}
[TestMethod]
public void BaseDatetieCompareByConstant2()
{
DateTime dateTime = new DateTime(1900, 01, 01, 2, 3, 4);
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Birthday > dateTime),
"PATIENT_BIRTHDAY>TO_DATE('1900-01-01 02:03:04','yyyy-MM-dd hh24:mi:ss')");
}
[TestMethod]
public void BaseDateTimeCanNull()
{
DateTime? dateTime = new DateTime(1900, 01, 01, 2, 3, 4);
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Birthday > dateTime),
"PATIENT_BIRTHDAY>TO_DATE('1900-01-01 02:03:04','yyyy-MM-dd hh24:mi:ss')");
dateTime = null;
Assert.ThrowsException<NotSupportedException>(() =>
{
Assert.AreEqual(
visitor.ToSql<PatientInfo>(p => p.Birthday > dateTime),
"PATIENT_BIRTHDAY>TO_DATE('1900-01-01 02:03:04','yyyy-MM-dd hh24:mi:ss')");
});
}
}
}
StringConcatVisitorUnitTest：字符串連接測試
using FXY.Code.ORM.ExpressionVisit;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace FXY.Code.ORM.ExpressionVisit.Test
{
[TestClass]
public class StringConcatVisitorUnitTest
{
private WhereExpressionVisitor visitor = new WhereExpressionVisitor();
[TestMethod]
public void OneStringConcat()
{
visitor = new WhereExpressionVisitor(true, true, false, "", true);
string id = "1";
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == "0_" + id),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='0_1'");
}
[TestMethod]
public void TwoStringConcat()
{
visitor = new WhereExpressionVisitor(true, true, false, "", true);
string id1 = "1";
string id2 = "2";
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == "0_" + id1 + "_" + id2),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='0_1_2'");
}
[TestMethod]
public void ThreeStringConcat()
{
visitor = new WhereExpressionVisitor(true, true, false, "", true);
string id1 = "1";
string id2 = "2";
string id3 = "3";
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == "0_" + id1 + "_" + id2 + "_" + id3),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='0_1_2_3'");
}
[TestMethod]
public void FourStringConcat()
{
visitor = new WhereExpressionVisitor(true, true, false, "", true);
string id1 = "1";
string id2 = "2";
string id3 = "3";
string id4 = "4";
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == "0_" + id1 + "_" + id2 + "_" + id3 + "_" + id4),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='0_1_2_3_4'");
}
[TestMethod]
public void OneObjectConcat()
{
visitor = new WhereExpressionVisitor(true, true, false, "", true);
object id = "1";
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == "0_" + id),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='0_1'");
}
[TestMethod]
public void TwoObjectConcat()
{
visitor = new WhereExpressionVisitor(true, true, false, "", true);
object id1 = "1";
object id2 = "2";
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == "0_" + id1 + "_" + id2),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='0_1_2'");
}
[TestMethod]
public void OneIntConcat()
{
visitor = new WhereExpressionVisitor(true, true, false, "", true);
int id = 1;
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == "0_" + id),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='0_1'");
}
[TestMethod]
public void TwoIntConcat()
{
visitor = new WhereExpressionVisitor(true, true, false, "", true);
int id1 = 1;
int id2 = 2;
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == "0_" + id1 + "_" + id2),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='0_1_2'");
}
}
}
StringFormatVisitorUnitTest：字符串內(nèi)插測試
using FXY.Code.ORM.ExpressionVisit;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace FXY.Code.ORM.ExpressionVisit.Test
{
[TestClass]
public class StringFormatVisitorUnitTest
{
private WhereExpressionVisitor visitor = new WhereExpressionVisitor();
[TestMethod]
public void ExtensionUseVariableConstant()
{
visitor = new WhereExpressionVisitor(true, true, false, "", true);
string id = "1";
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == id),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='1'");
}
[TestMethod]
public void ExtensionStringInterpolation()
{
visitor = new WhereExpressionVisitor(true, true, false, "", true);
string id = "1";
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == $"1_{id}"),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='1_1'");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == $"1_{id}_{id}"),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='1_1_1'");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == $"1_{id}_{id}_{id}"),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='1_1_1_1'");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == $"1_{id}_{id}_{id}_{id}"),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='1_1_1_1_1'");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == $"1_{id}_{id}_{id}_{id}_{id}"),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='1_1_1_1_1_1'");
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == $"1_{id}_{id}_{id}_{id}_{id}_{id}"),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='1_1_1_1_1_1_1'");
}
}
}
StringJoinVisitorUnitTest：string.Join函數(shù)測試
using FXY.Code.ORM.ExpressionVisit;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Collections.Generic;
namespace FXY.Code.ORM.ExpressionVisit.Test
{
[TestClass]
public class StringJoinVisitorUnitTest
{
private WhereExpressionVisitor visitor = new WhereExpressionVisitor();
public void StringJoin()
{
visitor = new WhereExpressionVisitor(true, true, false, "", true);
var list = new List<string>() { "1", "2", "3" };
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == string.Join(",", list)),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='1,2,3'");
}
public void IntJoin()
{
visitor = new WhereExpressionVisitor(true, true, false, "", true);
var list = new List<int>() { 1, 2, 3 };
Assert.AreEqual(visitor.ToSql<PatientInfo>(p => p.ID == string.Join(",", list)),
"SELECT * FROM PATIENT p WHERE p.PATIENT_ID='1,2,3'");
}
}
}
單元測試結(jié)果
浙公網(wǎng)安備 33010602011771號