public void ConvertEqualsPropertyAndInt()
{
var sql = ExpressionToSql.Convert <ChildObject>(x => x.Position == 1);
sql.ShouldEqual("Position = 1");
}
public void ConvertEqualsPropertyAndString()
{
var sql = ExpressionToSql.Convert <ChildObject>(x => x.Name == "1");
sql.ShouldEqual("Name = '1'");
}
public DaoContext()
{
expressionToSql = new ExpressionToSql();
}
public void ConvertEqualsOfTwoProperties()
{
var sql = ExpressionToSql.Convert <ChildObject>(x => x.Name == x.Name);
sql.ShouldEqual("Name = Name");
}
//条件查询实体
public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition) where T : class, new()
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT * From {0} Where {1} ", typeof(T).GetMappingAttributeName(), ExpressionToSql.GetWhereByLambda(condition));
DbParameter[] dbParameter = null;
return(this.FindList <T>(sb.ToString(), dbParameter));
}
public int FindCount <T>(Expression <Func <T, bool> > condition) where T : new()
{
string strSql = "Select Count(1) From {0} where {1}".FormatWith(typeof(T).GetMappingAttributeName(), ExpressionToSql.GetWhereByLambda(condition));
return(this.ExecuteScalar(strSql).ToInt());
}
public void ConvertStaticMethodCall()
{
var sql = ExpressionToSql.Convert <ChildObject>(x => StaticExample.Method());
sql.ShouldEqual("'someValue'");
}
static void Main(string[] args)
{
Console.Title = "Expression2SqlTest";
//通过静态属性DatabaseType或者静态方法Init均可配置数据库类型
ExpressionToSql.DbSqlParser = new SQLServerSqlParser();
ExpressionToSql.Init(new SQLServerSqlParser());
Printf(
ExpressionToSql.Select <UserInfo>(),
"查询单表所有字段"
);
Printf(
ExpressionToSql.Select <UserInfo>(u => u.Id),
"查询单表单个字段"
);
Printf(
ExpressionToSql.Select <UserInfo>(u => new { u.Id, u.Name }),
"查询单表多个字段"
);
Printf(
ExpressionToSql.Select <UserInfo>(u => u.Id).
Where(u => u.Name.Like("b")),
"查询单表，带where Like条件"
);
Printf(
ExpressionToSql.Select <UserInfo>(u => u.Id).
Where(u => u.Name.LikeLeft("b")),
"查询单表，带where LikeLeft条件"
);
Printf(
ExpressionToSql.Select <UserInfo>(u => u.Id).
Where(u => u.Name.LikeRight("b")),
"查询单表，带where LikeRight条件"
);
Printf(
ExpressionToSql.Select <UserInfo>(u => u.Name).
Where(u => u.Id.In(1, 2, 3)),
"查询单表，带where in条件，写法一"
);
int[] aryId = { 1, 2, 3 };
Printf(
ExpressionToSql.Select <UserInfo>(u => u.Name).
Where(u => u.Id.In(aryId)),
"查询单表，带where in条件，写法二"
);
Printf(
ExpressionToSql.Select <UserInfo>(u => u.Name).
Where(u => u.Name.In(new string[] { "a", "b" })),
"查询单表，带where in条件，写法三"
);
Printf(
ExpressionToSql.Select <UserInfo>(u => u.Id).
Where(
u => u.Name == "b" &&
u.Id > 2 &&
u.Name != null &&
u.Id > int.MinValue &&
u.Id < int.MaxValue &&
u.Id.In(1, 2, 3) &&
u.Name.Like("a") &&
u.Name.LikeLeft("b") &&
u.Name.LikeRight("c") ||
u.Id == null
),
"查询单表，带多个where条件"
);
Printf(
ExpressionToSql.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
Join <Account>((u, a) => u.Id == a.UserId),
"多表Join关联查询"
);
Printf(
ExpressionToSql.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
InnerJoin <Account>((u, a) => u.Id == a.UserId),
"多表InnerJoin关联查询"
);
Printf(
ExpressionToSql.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
LeftJoin <Account>((u, a) => u.Id == a.UserId),
"多表LeftJoin关联查询"
);
Printf(
ExpressionToSql.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
RightJoin <Account>((u, a) => u.Id == a.UserId),
"多表RightJoin关联查询"
);
Printf(
ExpressionToSql.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
FullJoin <Account>((u, a) => u.Id == a.UserId),
"多表FullJoin关联查询"
);
Printf(
ExpressionToSql.Select <UserInfo, Account, Student, Class, City, Country>((u, a, s, d, e, f) =>
new { u.Id, a.Name, StudentName = s.Name, ClassName = d.Name, e.CityName, CountryName = f.Name }).
Join <Account>((u, a) => u.Id == a.UserId).
LeftJoin <Account, Student>((a, s) => a.Id == s.AccountId).
RightJoin <Student, Class>((s, c) => s.Id == c.UserId).
InnerJoin <Class, City>((c, d) => c.CityId == d.Id).
FullJoin <City, Country>((c, d) => c.CountryId == d.Id).
Where(u => u.Id != null),
"多表复杂关联查询"
);
Printf(
ExpressionToSql.Count <UserInfo>(u => u.Id).
GroupBy(u => u.Name),
"GroupBy分组查询"
);
Printf(
ExpressionToSql.Select <UserInfo>().
OrderBy(u => u.Id),
"OrderBy排序"
);
Printf(
ExpressionToSql.Max <UserInfo>(u => u.Id),
"返回一列中的最大值。NULL 值不包括在计算中。"
);
Printf(
ExpressionToSql.Min <UserInfo>(u => u.Id),
"返回一列中的最小值。NULL 值不包括在计算中。"
);
Printf(
ExpressionToSql.Avg <UserInfo>(u => u.Id),
"返回数值列的平均值。NULL 值不包括在计算中。"
);
Printf(
ExpressionToSql.Count <UserInfo>(),
"返回表中的记录数"
);
Printf(
ExpressionToSql.Count <UserInfo>(u => u.Id),
"返回指定列的值的数目（NULL 不计入）"
);
Printf(
ExpressionToSql.Sum <UserInfo>(u => u.Id),
"返回数值列的总数（总额）。"
);
Printf(
ExpressionToSql.Delete <UserInfo>(),
"全表删除"
);
Printf(
ExpressionToSql.Delete <UserInfo>().
Where(u => u.Id == null),
"根据where条件删除指定表记录"
);
Printf(
ExpressionToSql.Update <UserInfo>(() => new { Name = "", Sex = 1, Email = "*****@*****.**" }),
"全表更新"
);
Printf(
ExpressionToSql.Update <UserInfo>(() => new { Name = "", Sex = 1, Email = "*****@*****.**" }).
Where(u => u.Id == 1),
"根据where条件更新指定表记录"
);
//to be continued...
}
public void ConvertConst()
{
var sql = ExpressionToSql.Convert <ChildObject>(x => someValue);
sql.ShouldEqual("'someValue'");
}
public void ConvertStaticMemberReference()
{
var sql = ExpressionToSql.Convert <ChildObject>(x => StaticExample.Value);
sql.ShouldEqual("'someValue'");
}
public void ConvertPropertyToPropertyName()
{
var sql = ExpressionToSql.Convert <ChildObject>(x => x.Name);
sql.ShouldEqual("Name");
}
protected override ISqlBuilder Visit(ConstantExpression expression, ExpressionToSql parser)
{
return(new SqlValueBuilder(expression.Value));
}
/// <summary>
/// Sets the where clause for this one-to-many relationship.
/// Note: This only supports simple cases, use the string overload for more complex clauses.
/// </summary>
public T Where(Expression <Func <TChild, bool> > where)
{
var sql = ExpressionToSql.Convert(where);
return(Where(sql));
}
public void ConvertGreaterEquals()
{
var sql = ExpressionToSql.Convert <ChildObject>(x => x.Position >= 1);
sql.ShouldEqual("Position >= 1");
}
public void ConvertEnumMemberReferenceMethodCall()
{
var sql = ExpressionToSql.Convert <ChildObject>(x => Something.Else.ToString());
sql.ShouldEqual("'Else'");
}
public void ConvertNot()
{
var sql = ExpressionToSql.Convert <ChildObject>(x => x.Position != 1);
sql.ShouldEqual("Position != 1");
}
public void ConvertEnumMemberReference()
{
var sql = ExpressionToSql.Convert <ChildObject>(x => Something.Else);
sql.ShouldEqual("10");
}
public int Delete <T>(Expression <Func <T, bool> > condition) where T : class, new()
{
string sql = $"Delete From {typeof(T).GetMappingAttributeName()} Where {ExpressionToSql.GetWhereByLambda<T>(condition)}";
return(ExecuteNonQuery(sql));
}
public void ConvertIntToValue()
{
var sql = ExpressionToSql.Convert <ChildObject>(x => 1);
sql.ShouldEqual("1");
}
public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition, string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class, new()
{
StringBuilder sb = new StringBuilder();
string strSql = string.Format("SELECT * From {0} Where {1} ", typeof(T).GetMappingAttributeName(), ExpressionToSql.GetWhereByLambda(condition));//test1
sb.AppendFormat(strSql);
if (pageIndex == 0)
{
pageIndex = 1;
}
int num = (pageIndex - 1) * pageSize;
int num1 = (pageIndex) * pageSize;
string OrderBy = "";
if (!string.IsNullOrEmpty(orderField))
{
if (orderField.ToUpper().IndexOf("ASC") + orderField.ToUpper().IndexOf("DESC") > 0)
{
OrderBy = " Order By " + orderField;
}
else
{
OrderBy = " Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
}
}
else
{
OrderBy = " order by (select 0 )";
}
sb.Append(OrderBy);
sb.Append(" limit " + num + "," + pageSize + "");
total = Convert.ToInt32(this.FindObject("Select Count(1) From (" + strSql + ") As ttt"));
return(ConvertExtension.IDataReaderToList <T>(DatabaseCommon.ExecuteDataReader(this.dbConnection, sb.ToString())));
}
public void ConvertStringToValue()
{
var sql = ExpressionToSql.Convert <ChildObject>(x => "1");
sql.ShouldEqual("'1'");
}
public static SqlScalarExpression VisitBuiltinFunctionCall(MethodCallExpression methodCallExpression, TranslationContext context)
{
Type declaringType;
// Method could be an extension method
if (methodCallExpression.Method.IsStatic && methodCallExpression.Method.IsExtensionMethod())
{
if (methodCallExpression.Arguments.Count < 1)
{
// Extension methods should has at least 1 argument, this should never happen
// Throwing ArgumentException instead of assert
throw new ArgumentException();
}
declaringType = methodCallExpression.Arguments[0].Type;
if (methodCallExpression.Method.DeclaringType.GeUnderlyingSystemType() == typeof(TypeCheckFunctionsExtensions))
{
return(TypeCheckFunctions.Visit(methodCallExpression, context));
}
}
else
{
declaringType = methodCallExpression.Method.DeclaringType;
}
// Check order matters, some extension methods work for both strings and arrays
// Math functions
if (declaringType == typeof(Math))
{
return(MathBuiltinFunctions.Visit(methodCallExpression, context));
}
// String functions
if (declaringType == typeof(string))
{
return(StringBuiltinFunctions.Visit(methodCallExpression, context));
}
// Array functions
if (declaringType.IsEnumerable())
{
return(ArrayBuiltinFunctions.Visit(methodCallExpression, context));
}
// Spatial functions
if (typeof(Geometry).IsAssignableFrom(declaringType) ||
methodCallExpression.Method.DeclaringType == typeof(GeometryOperationExtensions))
{
return(SpatialBuiltinFunctions.Visit(methodCallExpression, context));
}
// ToString with Objects (String and Guid only)
if (methodCallExpression.Method.Name == "ToString" &&
methodCallExpression.Arguments.Count == 0 &&
methodCallExpression.Object != null &&
(methodCallExpression.Object.Type == typeof(Guid) || methodCallExpression.Object.Type == typeof(string)))
{
return(ExpressionToSql.VisitScalarExpression(methodCallExpression.Object, context));
}
throw new DocumentQueryException(string.Format(CultureInfo.CurrentCulture, ClientResources.MethodNotSupported, methodCallExpression.Method.Name));
}
static void Main(string[] args)
{
Console.Title = "Expression2SqlTest";
ExpressionToSql <UserInfo> userInfoSql = new ExpressionToSql <UserInfo>(new MySQLSqlParser());
Printf(
userInfoSql.Select().Where(u => u.Id != 1),
"查询单表，带where条件，实例类"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>().
Where(u => u.Name == "张三"),
"SQLServer静态类"
);
Printf(
ExpressionToSqlMySQL.Select <UserInfo>().
Where(u => u.Name == "张三"),
"MySQL静态类"
);
Printf(
ExpressionToSqlSQLite.Select <UserInfo>().
Where(u => u.Name == "张三"),
"SQLite静态类"
);
Printf(
ExpressionToSqlOracle.Select <UserInfo>().
Where(u => u.Name == "张三"),
"Oracle静态类"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(),
"查询单表所有字段"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id),
"查询单表指定字段"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => new { u.Id, u.Name }),
"查询单表多个字段"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>().
Where(u => u.Name == "张三"),
"查询单表，带where条件，实参赋值"
);
string parameterValue = "李四";
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>().
Where(u => u.Name == parameterValue),
"查询单表，带where条件，形参赋值"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
Where(u => u.Name.Like("b")),
"查询单表，带where Like条件"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
Where(u => u.Name.LikeLeft("b")),
"查询单表，带where LikeLeft条件"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
Where(u => u.Name.LikeRight("b")),
"查询单表，带where LikeRight条件"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Name).
Where(u => u.Id.In(1, 2, 3, 4, 5)),
"查询单表，带where in条件，写法一"
);
int[] aryId = { 1, 2, 3 };
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Name).
Where(u => u.Id.In(aryId)),
"查询单表，带where in条件，写法二"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Name).
Where(u => u.Name.In(new string[] { "a", "b" })),
"查询单表，带where in条件，写法三"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
Where(
u => u.Name == "b" &&
u.Id > 2 &&
u.Name != null &&
u.Id > int.MinValue &&
u.Id < int.MaxValue &&
u.Id.In(1, 2, 3) &&
u.Name.Like("a") &&
u.Name.LikeLeft("b") &&
u.Name.LikeRight("c") ||
u.Id == null
),
"查询单表，带多个where条件"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
Where(
u => u.Name == "b" &&
(u.Id == 100 || u.Id != null) &&
(u.Id == 50 || u.Id == null)
),
"查询单表，带多个where条件，括号优先级"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
Join <Account>((u, a) => u.Id == a.UserId),
"多表Join关联查询"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
InnerJoin <Account>((u, a) => u.Id == a.UserId),
"多表InnerJoin关联查询"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
LeftJoin <Account>((u, a) => u.Id == a.UserId),
"多表LeftJoin关联查询"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
RightJoin <Account>((u, a) => u.Id == a.UserId),
"多表RightJoin关联查询"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
FullJoin <Account>((u, a) => u.Id == a.UserId),
"多表FullJoin关联查询"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo, Account, Student, Class, City, Country>((u, a, s, d, e, f) =>
new { u.Id, a.Name, StudentName = s.Name, ClassName = d.Name, e.CityName, CountryName = f.Name }).
Join <Account>((u, a) => u.Id == a.UserId).
LeftJoin <Account, Student>((a, s) => a.Id == s.AccountId).
RightJoin <Student, Class>((s, c) => s.Id == c.UserId).
InnerJoin <Class, City>((c, d) => c.CityId == d.Id).
FullJoin <City, Country>((c, d) => c.CountryId == d.Id).
Where(u => u.Id != null),
"多表复杂关联查询"
);
Printf(
ExpressionToSqlSQLServer.Count <UserInfo>(u => u.Id).
GroupBy(u => u.Name),
"GroupBy分组查询"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>().
OrderBy(u => u.Id),
"OrderBy排序"
);
Printf(
ExpressionToSqlSQLServer.Max <UserInfo>(u => u.Id),
"返回一列中的最大值。NULL 值不包括在计算中。"
);
Printf(
ExpressionToSqlSQLServer.Min <UserInfo>(u => u.Id),
"返回一列中的最小值。NULL 值不包括在计算中。"
);
Printf(
ExpressionToSqlSQLServer.Avg <UserInfo>(u => u.Id),
"返回数值列的平均值。NULL 值不包括在计算中。"
);
Printf(
ExpressionToSqlSQLServer.Count <UserInfo>(),
"返回表中的记录数"
);
Printf(
ExpressionToSqlSQLServer.Count <UserInfo>(u => u.Id),
"返回指定列的值的数目（NULL 不计入）"
);
Printf(
ExpressionToSqlSQLServer.Sum <UserInfo>(u => u.Id),
"返回数值列的总数（总额）。"
);
Printf(
ExpressionToSqlSQLServer.Insert <UserInfo>(() => new { Name = "张三", Sex = 1, Email = "*****@*****.**" }),
"插入一条记录"
);
Printf(
ExpressionToSqlSQLServer.Delete <UserInfo>(),
"全表删除"
);
Printf(
ExpressionToSqlSQLServer.Delete <UserInfo>().
Where(u => u.Id == null),
"根据where条件删除指定表记录"
);
Printf(
ExpressionToSqlSQLServer.Update <UserInfo>(() => new { Name = "", Sex = 1, Email = "*****@*****.**" }),
"全表更新"
);
Printf(
ExpressionToSqlSQLServer.Update <UserInfo>(() => new { Name = "", Sex = 1, Email = "*****@*****.**" }).
Where(u => u.Id == 1),
"根据where条件更新指定表记录"
);
//to be continued...
}
