public void ORM_TableQuery_Paging() { var f = QueryFactory.Instance; var source = f.Table(RF.Concrete<ArticleRepository>()); var pk = source.Column(Entity.IdProperty); var query = f.Query( from: source, where: f.Constraint(pk, PropertyOperator.Greater, 0), orderBy: new List<IOrderBy> { f.OrderBy(source.Column(Article.CodeProperty)) } ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM Article WHERE Article.Id > {0} ORDER BY Article.Code ASC"); generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(query as SqlSelect, new PagingInfo(3, 10)); var pagingSql = generator.Sql; Assert.IsTrue(pagingSql.ToString() == @"SELECT TOP 10 * FROM Article WHERE Article.Id > {0} AND Article.Id NOT IN ( SELECT TOP 20 Article.Id FROM Article WHERE Article.Id > {1} ORDER BY Article.Code ASC ) ORDER BY Article.Code ASC"); }
public void ORM_TableQuery_Paging_PageNumer1() { var f = QueryFactory.Instance; var source = f.Table(RF.Concrete<ArticleRepository>()); var query = f.Query( from: source, where: f.Constraint(source.Column(Entity.IdProperty), PropertyOperator.Greater, 0), orderBy: new List<IOrderBy> { f.OrderBy(source.Column(Article.CodeProperty)) } ); var generator = new SqlServerSqlGenerator { AutoQuota = false }; f.Generate(generator, query); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM Article WHERE Article.Id > {0} ORDER BY Article.Code ASC"); //对于已经组装完成的 IQuery 对象,ModifyToPagingTree 方法同样可以为其生成相应的分页 SqlSelect 语句。 generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(query as SqlSelect, new PagingInfo(1, 10)); var pagingSql = generator.Sql; Assert.IsTrue(pagingSql.ToString() == @"SELECT TOP 10 * FROM Article WHERE Article.Id > {0} ORDER BY Article.Code ASC"); }
public void ORM_SqlTree_Select_Where_AndOr() { var select = new SqlSelect(); var table = new SqlTable { TableName = "Table1" }; select.Selection = new SqlSelectAll(); select.From = table; select.Where = new SqlBinaryConstraint { Left = new SqlColumnConstraint { Column = new SqlColumn { Table = table, ColumnName = "Column1" }, Operator = SqlColumnConstraintOperator.Equal, Value = "A" }, Opeartor = SqlBinaryConstraintType.Or, Right = new SqlBinaryConstraint { Left = new SqlBinaryConstraint { Left = new SqlColumnConstraint { Column = new SqlColumn { Table = table, ColumnName = "Column2" }, Operator = SqlColumnConstraintOperator.Equal, Value = "A2" }, Opeartor = SqlBinaryConstraintType.Or, Right = new SqlColumnConstraint { Column = new SqlColumn { Table = table, ColumnName = "Column2" }, Operator = SqlColumnConstraintOperator.Equal, Value = "B2" } }, Opeartor = SqlBinaryConstraintType.And, Right = new SqlColumnConstraint { Column = new SqlColumn { Table = table, ColumnName = "Column1" }, Operator = SqlColumnConstraintOperator.Equal, Value = "B" } } }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM Table1 WHERE Table1.Column1 = {0} OR (Table1.Column2 = {1} OR Table1.Column2 = {2}) AND Table1.Column1 = {3}"); Assert.IsTrue(sql.Parameters.Count == 4); Assert.IsTrue(sql.Parameters[0].ToString() == "A"); Assert.IsTrue(sql.Parameters[1].ToString() == "A2"); Assert.IsTrue(sql.Parameters[2].ToString() == "B2"); Assert.IsTrue(sql.Parameters[3].ToString() == "B"); }
public void ORM_SqlTree_Select_WithoutQuota() { var select = new SqlSelect(); var table = new SqlTable { TableName = "Table1" }; var column1 = new SqlColumn { Table = table, ColumnName = "Column1" }; var column2 = new SqlColumn { Table = table, ColumnName = "Column2" }; select.Selection = new SqlArray { Items = { column1, column2 } }; select.From = table; select.Where = new SqlColumnConstraint { Column = column2, Operator = SqlColumnConstraintOperator.Equal, Value = "Column2Value" }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT Table1.Column1, Table1.Column2 FROM Table1 WHERE Table1.Column2 = {0}"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].ToString() == "Column2Value"); }
public void ORM_SqlTree_Select_Star() { var select = new SqlSelect(); select.Selection = new SqlSelectAll(); select.From = new SqlTable { TableName = "Table1" }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM Table1"); }
public void ORM_SqlTree_Select_Top() { var select = new SqlSelect(); select.Selection = new SqlSelectAll(); var t = new SqlTable { TableName = "Table1" }; ; select.From = t; select.OrderBy = new SqlOrderByList { new SqlOrderBy { Column = new SqlColumn { Table = t, ColumnName = "Id", } } }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select, new PagingInfo(1, 10)); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT TOP 10 * FROM Table1 ORDER BY Table1.Id ASC"); }
public void ORM_SqlTree_Select_Paging_PageNumer1() { var table = new SqlTable { TableName = "ASN" }; var select = new SqlSelect { Selection = new SqlSelectAll(), From = table, Where = new SqlColumnConstraint { Column = new SqlColumn { Table = table, ColumnName = Entity.IdProperty.Name }, Operator = SqlColumnConstraintOperator.Greater, Value = 0 }, OrderBy = new SqlOrderByList { Items = { new SqlOrderBy { Column = new SqlColumn{ Table = table, ColumnName = "AsnCode" }, Direction = OrderDirection.Ascending } } } }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM ASN WHERE ASN.Id > {0} ORDER BY ASN.AsnCode ASC"); generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select, new PagingInfo(1, 10)); var pagingSql = generator.Sql; Assert.IsTrue(pagingSql.ToString() == @"SELECT TOP 10 * FROM ASN WHERE ASN.Id > {0} ORDER BY ASN.AsnCode ASC"); }
public void ORM_SqlTree_Select_SelectFromSelectResult() { var userTable = new SqlTable { TableName = "User" }; var subSelectRef = new SqlSubSelect { Select = new SqlSelect { Selection = new SqlSelectAll(), From = userTable, Where = new SqlColumnConstraint { Column = new SqlColumn { Table = userTable, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.Greater, Value = 0 } }, Alias = "T" }; var select = new SqlSelect(); select.Selection = new SqlSelectAll(); select.From = subSelectRef; select.Where = new SqlColumnConstraint { Column = new SqlColumn { Table = subSelectRef, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.Less, Value = 100 }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM ( SELECT * FROM User WHERE User.Id > {0} ) AS T WHERE T.Id < {1}"); }
public void ORM_SqlTree_Select_OrderBy2() { var select = new SqlSelect(); var table = new SqlTable { TableName = "Table1", Alias = "t1" }; var column1 = new SqlColumn { Table = table, ColumnName = "Column1", Alias = "c1" }; var column2 = new SqlColumn { Table = table, ColumnName = "Column2", Alias = "c2" }; select.Selection = new SqlArray { Items = { column1, column2 } }; select.From = table; select.Where = new SqlColumnConstraint { Column = column2, Operator = SqlColumnConstraintOperator.Equal, Value = "Column2Value" }; select.OrderBy = new SqlOrderByList { new SqlOrderBy { Column = column1, Direction = OrderDirection.Ascending }, new SqlOrderBy { Column = column2, Direction = OrderDirection.Descending } }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT t1.Column1 AS c1, t1.Column2 AS c2 FROM Table1 AS t1 WHERE t1.Column2 = {0} ORDER BY t1.Column1 ASC, t1.Column2 DESC"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].ToString() == "Column2Value"); }
public void ORM_SqlTree_Select_OuterJoin() { var select = new SqlSelect(); var table = new SqlTable { TableName = "Article", Alias = "a" }; var userTable = new SqlTable { TableName = "User", Alias = "u" }; select.Selection = new SqlSelectAll { Table = table }; select.From = new SqlJoin { Left = table, JoinType = SqlJoinType.LeftOuter, Right = userTable, Condition = new SqlColumnsComparisonConstraint { LeftColumn = new SqlColumn { Table = table, ColumnName = "UserId" }, RightColumn = new SqlColumn { Table = userTable, ColumnName = "Id" }, } }; select.Where = new SqlColumnConstraint { Column = new SqlColumn { Table = userTable, ColumnName = "UserName" }, Operator = SqlColumnConstraintOperator.Equal, Value = "HuQingfang" }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT a.* FROM Article AS a LEFT OUTER JOIN User AS u ON a.UserId = u.Id WHERE u.UserName = {0}"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].ToString() == "HuQingfang"); }
public void ORM_SqlTree_Select_InSubSelect_Join() { var select = new SqlSelect(); var adminTable = new SqlTable { TableName = "User", Alias = "Administrator" }; var articleTable = new SqlTable { TableName = "Article" }; var subSelect = new SqlSelect { Selection = new SqlColumn { Table = articleTable, ColumnName = "UserId" }, From = new SqlJoin { Left = articleTable, JoinType = SqlJoinType.Inner, Right = adminTable, Condition = new SqlColumnsComparisonConstraint { LeftColumn = new SqlColumn { Table = articleTable, ColumnName = "AdministratorId" }, RightColumn = new SqlColumn { Table = adminTable, ColumnName = "Id" }, } }, Where = new SqlColumnConstraint { Column = new SqlColumn { Table = adminTable, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.Equal, Value = 1 } }; var userTable = new SqlTable { TableName = "User" }; select.Selection = new SqlSelectAll(); select.From = userTable; select.Where = new SqlColumnConstraint { Column = new SqlColumn { Table = userTable, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.In, Value = subSelect }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM User WHERE User.Id IN ( SELECT Article.UserId FROM Article INNER JOIN User AS Administrator ON Article.AdministratorId = Administrator.Id WHERE Administrator.Id = {0} )"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].Equals(1)); }
public void ORM_SqlTree_Select_InSubSelect() { var select = new SqlSelect(); var articleTable = new SqlTable { TableName = "Article" }; var subSelect = new SqlSelect { Selection = new SqlColumn { Table = articleTable, ColumnName = "UserId" }, From = articleTable, Where = new SqlColumnConstraint { Column = new SqlColumn { Table = articleTable, ColumnName = "CreateDate" }, Operator = SqlColumnConstraintOperator.Equal, Value = DateTime.Today } }; var userTable = new SqlTable { TableName = "User" }; select.Selection = new SqlSelectAll(); select.From = userTable; select.Where = new SqlColumnConstraint { Column = new SqlColumn { Table = userTable, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.In, Value = subSelect }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM User WHERE User.Id IN ( SELECT Article.UserId FROM Article WHERE Article.CreateDate = {0} )"); Assert.IsTrue(sql.Parameters.Count == 1); Assert.IsTrue(sql.Parameters[0].Equals(DateTime.Today)); }
public void ORM_SqlTree_Select_Count() { var select = new SqlSelect(); select.IsCounting = true; select.From = new SqlTable { TableName = "Table1" }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); Assert.IsTrue(generator.Sql.ToString() == @"SELECT COUNT(0) FROM Table1"); }
public void ORM_SqlTree_Select_ChildrenExists() { var articleTable = new SqlTable { TableName = "Article" }; var userTable = new SqlTable { TableName = "User", Alias = "u" }; var select = new SqlSelect(); select.Selection = new SqlSelectAll(); select.From = userTable; select.Where = new SqlBinaryConstraint { Left = new SqlColumnConstraint { Column = new SqlColumn { Table = userTable, ColumnName = "Id" }, Operator = SqlColumnConstraintOperator.Greater, Value = 0 }, Opeartor = SqlBinaryConstraintType.And, Right = new SqlExistsConstraint { Select = new SqlSelect { Selection = new SqlLiteral { FormattedSql = "0" }, From = articleTable, Where = new SqlColumnsComparisonConstraint { LeftColumn = new SqlColumn { Table = articleTable, ColumnName = "UserId" }, RightColumn = new SqlColumn { Table = userTable, ColumnName = "Id" } } } } }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM User AS u WHERE u.Id > {0} AND EXISTS ( SELECT 0 FROM Article WHERE Article.UserId = u.Id )"); }
public void ORM_SqlTree_Select_Paging() { var table = new SqlTable { TableName = "ASN" }; var pk = new SqlColumn { Table = table, ColumnName = "Id" }; var select = new SqlSelect { Selection = new SqlSelectAll(), From = table, Where = new SqlColumnConstraint { Column = pk, Operator = SqlColumnConstraintOperator.Greater, Value = 0 }, OrderBy = new List<SqlOrderBy> { new SqlOrderBy { Column = new SqlColumn{ Table = table, ColumnName = "AsnCode" }, Direction = OrderDirection.Ascending } } }; var generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(select); var sql = generator.Sql; Assert.IsTrue(sql.ToString() == @"SELECT * FROM ASN WHERE ASN.Id > {0} ORDER BY ASN.AsnCode ASC"); var pagedSelect = generator.ModifyToPagingTree(select, pk, new PagingInfo(3, 10)); generator = new SqlServerSqlGenerator { AutoQuota = false }; generator.Generate(pagedSelect); var pagingSql = generator.Sql; Assert.IsTrue(pagingSql.ToString() == @"SELECT TOP 10 * FROM ASN WHERE ASN.Id > {0} AND ASN.Id NOT IN ( SELECT TOP 20 ASN.Id FROM ASN WHERE ASN.Id > {1} ORDER BY ASN.AsnCode ASC ) ORDER BY ASN.AsnCode ASC"); }