static void Main(string[] args)
{
Console.Title = "Expression2SqlTest";
ExpressionToSql <UserInfo> userInfoSql = new ExpressionToSql <UserInfo>(new MySQLSqlParser());
Printf(
userInfoSql.Select().Where(u => u.Id != 1),
"Instance class"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>().
Where(u => u.Name == "Jones"),
"SQLServer static class"
);
Printf(
ExpressionToSqlMySQL.Select <UserInfo>().
Where(u => u.Name == "Tom"),
"MySQL static class"
);
Printf(
ExpressionToSqlSQLite.Select <UserInfo>().
Where(u => u.Name == "Venus"),
"SQLite static class"
);
Printf(
ExpressionToSqlOracle.Select <UserInfo>().
Where(u => u.Name == "Lucy"),
"Oracle static class"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(),
"Query single table all fields"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id),
"Query single table single field"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => new { u.Id, u.Name }),
"Query single table specified field"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => new { UserId = u.Id, u.Email, UserName = u.Name }),
"Query single table specified field, take field alias"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>().
Where(u => u.Name == "Alice"),
"Query single table with multiple conditions"
);
string parameterValue = "Jack";
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>().
Where(u => u.Name == parameterValue),
"Query single table with multiple conditions"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
Where(u => u.Name.Like("b")),
"Query single table with 'like' conditions"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
Where(u => u.Name.LikeLeft("b")),
"Query single table with 'like left' conditions"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
Where(u => u.Name.LikeRight("b")),
"Query single table with 'like right' conditions"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Name).
Where(u => u.Id.In(1, 2, 3, 4, 5)),
"Query single table with 'in' conditions, mode 1"
);
int[] aryId = { 1, 2, 3 };
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Name).
Where(u => u.Id.In(aryId)),
"Query single table with 'in' conditions, mode 2"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Name).
Where(u => u.Name.In(new string[] { "a", "b" })),
"Query single table with 'in' conditions, mode 3"
);
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
),
"Query single table with multiple conditions"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
Where(
u => u.Name == "b" &&
(u.Id == 100 || u.Id != null) &&
(u.Id == 50 || u.Id == null)
),
"Query single table with multiple conditions, parentheses priority"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
Join <Account>((u, a) => u.Id == a.UserId),
"Multi-table join query"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
InnerJoin <Account>((u, a) => u.Id == a.UserId),
"Multi-table innerJoin query"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
LeftJoin <Account>((u, a) => u.Id == a.UserId),
"Multi-table leftJoin query"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
RightJoin <Account>((u, a) => u.Id == a.UserId),
"Multi-table rightJoin query"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
FullJoin <Account>((u, a) => u.Id == a.UserId),
"Multi-table fullJoin query"
);
Printf(
ExpressionToSqlSQLServer.Select <UserInfo, Account, Student, Class, City, Country>((a, b, c, d, e, f) =>
new { a.Id, b.Name, StudentName = c.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),
"More complex associated query table, the same column name alias"
);
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),
"Returns a column of the maximum value, null value is not included in the calculation"
);
Printf(
ExpressionToSqlSQLServer.Min <UserInfo>(u => u.Id),
"Returns a column of the minimum value, null value is not included in the calculation"
);
Printf(
ExpressionToSqlSQLServer.Avg <UserInfo>(u => u.Id),
"Returns the numerical average of the column, null value is not included in the calculation"
);
Printf(
ExpressionToSqlSQLServer.Count <UserInfo>(),
"Returns the record number in the table."
);
Printf(
ExpressionToSqlSQLServer.Count <UserInfo>(u => u.Id),
"Returns the value of the specified column number（null can't be counted）"
);
Printf(
ExpressionToSqlSQLServer.Sum <UserInfo>(u => u.Id),
"Returns the total number of numeric column（total amount）"
);
Printf(
ExpressionToSqlSQLServer.Insert <UserInfo>(() => new { Name = "Paul", Sex = 1, Email = "*****@*****.**" }),
"Insert a record"
);
Printf(
ExpressionToSqlSQLServer.Delete <UserInfo>(),
"A full table delete"
);
Printf(
ExpressionToSqlSQLServer.Delete <UserInfo>().
Where(u => u.Id == null),
"According to the condition delete specified table record"
);
Printf(
ExpressionToSqlSQLServer.Update <UserInfo>(() => new { Name = "Marilyn", Sex = 1, Email = "*****@*****.**" }),
"A full table updates"
);
Printf(
ExpressionToSqlSQLServer.Update <UserInfo>(() => new { Name = "Susan", Sex = 1, Email = "*****@*****.**" }).
Where(u => u.Id == 1),
"According to the condition update specified table record"
);
//to be continued...
}
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>(u => new { UserId = u.Id, u.Email, UserName = 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>((a, b, c, d, e, f) =>
new { a.Id, b.Name, StudentName = c.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...
}