众所周知，ORM是一种为了解决面向对象编程与关系数据库存在的互不匹配的现象的技术，其目标是基于面向对象编程语言（如C#、Java等）持久化类及映射关系完成对数据库操作（一般为读操作与写操作，也就是常说的增删改查）。其中一个关键点则是如何生成关系数据库能够识别的Sql，此处只讨论C#ORM实现中读操作生成Sql，也就是如何完成C#中LINQ查询到Sql的转换。LINQ中定义了大量大查询操作符，而基于数据库查询一般都需要使用Where子句，故在此我们进一步缩小讨论范围至LINQ的Where操作符。
针对某一持久化类T，执行Where查询的入参可用Func<T,bool>表示，也就是我们需要基于入参Func<T,bool>生成对应查询Sql语句。但基于以下两点：
Func<T,bool>解析较为复杂，而.NET Framework中LINQ查询涉及的各自操作对应的表达式树被较为完整的定义，可通过重写ExpressionVisitor实现解析转换生成查询Sql；.Where函数可接收Func<T,bool>或Expression<Func<T,bool>>入参，具体取决于数据源为IEnumerable<T>还是IQueryable<T>，考虑数据集成查询定义接口为IQueryable<T>及后期兼容性，故将解析入参设置为表达式树。
我们调整入参为Expression<Func<T,bool>>，完成将其转换为对应Sql查询语句。
映射关系
基于自定义Attribute作用于持久化类属性或持久化类建立面向对象编程语言至关系型数据库的映射关系
- 持久化类字段或属性映射至数据库字段，包括字段名、数据类型、是否可空、是否主键、默认值、字段备注等内容。
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;
}
}
- 持久化类映射至数据库表，包括数据库名（用户名）、表名、表备注、字段映射集合（表包含多个字段）等
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;
}
}
基于持久化类获取数据库映射结果接口定义与默认实现
- 接口定义
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>
/// 获取数据库名称
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <returns></returns>
string GetDbName<TSource>();
/// <summary>
/// 获取数据库名称
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
string GetDbName(Type type);
/// <summary>
/// 获取数据库表名
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <returns></returns>
string GetDbTableName<TSource>();
/// <summary>
/// 获取数据库表名
/// </summary>
/// <typeparam name="type"></typeparam>
/// <returns></returns>
string GetDbTableName(Type type);
/// <summary>
/// 获取数据库字段名
/// </summary>
/// <param name="memberInfo"></param>
/// <returns></returns>
string GetColumnName(MemberInfo memberInfo);
}
}
观察接口定义入参及后续默认实现可发现：入参均为System.Reflection命名空间下类，故获取数据库映射结果涉及运行时反射使用，存在一定程度的性能损失；
该处只考虑实现，暂不考虑性能，可以考虑将映射规则持久化（推荐使用XML或JSON等格式，且为文件格式保存，能够实现快速加载；不建议使用数据库存储，依赖度太高且读取耗时长）及修改或自定义实现IDataBaseMapping替换默认实现DataBaseMapping，避免运行时反射调用的性能损失。
- 默认实现
using System;
using System.Data.Linq.Mapping;
using System.Reflection;
namespace FXY.Code.ORM.Mapping
{
/// <summary>
/// 数据库与实体映射类
/// </summary>
public class DataBaseMapping : IDataBaseMapping
{
/// <summary>
/// 获取数据库名称
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <returns></returns>
public virtual string GetDbName<TSource>()
{
return string.Empty;
}
/// <summary>
/// 获取数据库表名
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <returns></returns>
public virtual string GetDbTableName<TSource>()
{
return GetDbTableName(typeof(TSource));
}
/// <summary>
/// 获取数据库字段名
/// </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("实体尚未设置DbTableMappingAttribute特性，请先设置或重写该方法。");
}
else
{
return tableAttribute.DataBaseName.ToUpper();
}
}
public string GetDbTableName(Type type)
{
var tableAttribute = type.GetAttribute<DbTableMappingAttribute>();
if (tableAttribute == null)
{
throw new NotSupportedException("实体尚未设置DbTableMappingAttribute特性，请先设置或重写该方法。");
}
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>
/// 是否启用数据库字段与实体属性之间的映射关系。
/// </summary>
private bool useColumnAttributeMap;
/// <summary>
/// 是否启用参数化
/// </summary>
private bool useDbParamter;
/// <summary>
/// 是否生成完整的查询语句
/// </summary>
private bool generateFullQuery;
/// <summary>
/// 参数化字典
/// </summary>
private Dictionary<string, object> dbParameters = new Dictionary<string, object>();
/// <summary>
/// 参数化关键字，如oracle为":Id"格式
/// </summary>
private string dbParamterKey = "";
/// <summary>
/// 是否启用表别名
/// </summary>
private bool useAlias;
private IDataBaseMapping dataBaseMapper;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="useColumnAttributeMap">是否启用<see cref="DbColumnMappingAttribute"/>特性建立属性与数据库字段之间的映射关系。</param>
/// <param name="generateFullQuery">是否生成完成的数据库查询语句，false不会返回SELECT * FROM {TABLE} WHERE部分。</param>
/// <param name="useDbParamter">是否启用参数化</param>
/// <param name="dbParamterKey">参数化</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();
}
/// 构造函数
/// </summary>
public WhereExpressionVisitor()
: base()
{
}
public void SetDataBaseMapper<T>() where T : DataBaseMapping, new()
{
this.dataBaseMapper = new T();
}
/// <summary>
/// 获取参数化字典
/// </summary>
/// <returns></returns>
public Dictionary<string, object> GetDbParamter() => dbParameters;
/// <summary>
/// 将表达式树解析为数据查询语句
/// </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)}";
/*算术运算*/
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)})";
///*数组索引操作*/
//case ExpressionType.ArrayIndex:
default: throw new NotSupportedException($"二元表达式树节点类型[{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; }
//}
}
基本流程测试
测试用例中所谓参数，是基于：
- 是否启用基于ColumnAttribute特性建立属性与数据库字段之间映射关系；
- 是否生成完成的数据库查询语句，false不会返回SELECT * FROM {TABLE} WHERE部分;
- 是否启用参数化；
- 是否启用表别名；
SingleParamVisitorUnitTest：单参数基本流程测试
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 单参数测试
[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：两参数流程流程测试
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 两参数测试
[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);
/*不启用字段映射，不生成完整查询语句，不使用参数化，使用表别名，单条件*/
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'");
/*不启用字段映射，不生成完整查询语句，不使用参数化，使用表别名，多条件*/
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：三参数基本流程测试
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 三参数测试
[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：四参数基本流程测试
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 四参数测试
[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：字符串内插测试
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函数测试
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'");
}
}
}
单元测试结果
