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");
}