public void ORM_TableQuery_Where_AndOr_Demo()
{
var f = QueryFactory.Instance;
var table = f.Table<Book>();
var code = table.Column(Book.CodeProperty);
var name = table.Column(Book.NameProperty);
var query = f.Query(
from: table,
where: f.Or(
left: f.Constraint(code, "A"),
right: f.And(
left: f.Or(
left: f.Constraint(name, "A2"),
right: f.Constraint(name, "B2")
),
right: f.Constraint(code, "B")
)
)
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() ==
@"SELECT *
FROM Book
WHERE Book.Code = {0} OR (Book.Name = {1} OR Book.Name = {2}) AND Book.Code = {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_TableQuery_Top()
{
var f = QueryFactory.Instance;
var query = f.Query(
top: 10,
from: f.Table<Book>()
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT TOP 10 *
FROM Book");
}
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_TableQuery_Star()
{
var f = QueryFactory.Instance;
var table = f.Table<Book>();
var query = f.Query(
selection: f.SelectAll(),
from: table
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT *
FROM Book");
}
public void ORM_TableQuery_InSubSelect_Join()
{
var f = QueryFactory.Instance;
var articleSource = f.Table(RF.Concrete<ArticleRepository>());
var userSource = f.Table(RF.Concrete<BlogUserRepository>());
var adminSource = f.Table(RF.Concrete<BlogUserRepository>(), "Administrator");
var query = f.Query(
from: userSource,
where: f.Constraint(
column: userSource.Column(BlogUser.IdProperty),
op: PropertyOperator.In,
value: f.Query(
selection: articleSource.Column(Article.UserIdProperty),
from: f.Join(
left: articleSource,
right: adminSource,
condition: f.Constraint(
articleSource.Column(Article.AdministratorIdProperty),
adminSource.Column(BlogUser.IdProperty)
)
),
where: f.Constraint(adminSource.Column(BlogUser.IdProperty), 1)
)
)
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT *
FROM BlogUser
WHERE BlogUser.Id IN (
SELECT Article.UserId
FROM Article
INNER JOIN BlogUser 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_TableQuery_OuterJoin()
{
var f = QueryFactory.Instance;
var articleSource = f.Table(RF.Concrete<ArticleRepository>(), "a");
var userSource = f.Table(RF.Concrete<BlogUserRepository>(), "u");
var query = f.Query(
selection: f.SelectAll(articleSource),
from: f.Join(
left: articleSource,
joinType: JoinType.LeftOuter,
right: userSource,
condition: f.Constraint(
leftColumn: articleSource.Column(Article.UserIdProperty),
rightColumn: userSource.Column(BlogUser.IdProperty)
)
),
where: f.Constraint(userSource.Column(BlogUser.UserNameProperty), "HuQingfang")
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT a.*
FROM Article AS a
LEFT OUTER JOIN BlogUser 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_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_TableQuery_InnerJoin_TwoToSingleTable()
{
var f = QueryFactory.Instance;
var articleSource = f.Table(RF.Concrete<ArticleRepository>(), "a");
var userSource1 = f.Table(RF.Concrete<BlogUserRepository>(), "u1");
var userSource2 = f.Table(RF.Concrete<BlogUserRepository>(), "u2");
var query = f.Query(
selection: f.SelectAll(articleSource),
from: f.Join(
left: f.Join(
left: articleSource,
right: userSource1,
condition: f.Constraint(
leftColumn: articleSource.Column(Article.UserIdProperty),
rightColumn: userSource1.Column(BlogUser.IdProperty)
)
),
right: userSource2,
condition: f.Constraint(
leftColumn: articleSource.Column(Article.AdministratorIdProperty),
rightColumn: userSource2.Column(BlogUser.IdProperty)
)
),
where: f.Constraint(userSource2.Column(BlogUser.UserNameProperty), "HuQingfang")
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT a.*
FROM Article AS a
INNER JOIN BlogUser AS u1 ON a.UserId = u1.Id
INNER JOIN BlogUser AS u2 ON a.AdministratorId = u2.Id
WHERE u2.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_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_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_TableQuery_ChildrenExists()
{
var f = QueryFactory.Instance;
var articleSource = f.Table(RF.Concrete<ArticleRepository>());
var userSource = f.Table(RF.Concrete<BlogUserRepository>(), "u");
var query = f.Query(
from: userSource,
where: f.And(
f.Constraint(
userSource.Column(Entity.IdProperty),
PropertyOperator.Greater, 0
),
f.Exists(f.Query(
selection: f.Literal("0"),
from: articleSource,
where: f.Constraint(
articleSource.Column(Article.UserIdProperty),
userSource.Column(Entity.IdProperty)
)
))
)
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT *
FROM BlogUser AS u
WHERE u.Id > {0} AND EXISTS (
SELECT 0
FROM Article
WHERE Article.UserId = u.Id
)");
}
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_TableQuery_Distinct()
{
var f = QueryFactory.Instance;
var query = f.Query(
isDistinct: true,
from: f.Table<Book>()
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query);
Assert.IsTrue(generator.Sql.ToString() == @"SELECT DISTINCT *
FROM Book");
}
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_TableQuery_InSubSelect()
{
var f = QueryFactory.Instance;
var articleSource = f.Table(RF.Concrete<ArticleRepository>());
var userSource = f.Table(RF.Concrete<BlogUserRepository>());
var query = f.Query(
from: userSource,
where: f.Constraint(
column: userSource.Column(BlogUser.IdProperty),
op: PropertyOperator.In,
value: f.Query(
selection: articleSource.Column(Article.UserIdProperty),
from: articleSource,
where: f.Constraint(articleSource.Column(Article.CreateDateProperty), DateTime.Today)
)
)
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() ==
@"SELECT *
FROM BlogUser
WHERE BlogUser.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_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_TableQuery_OrderBy2()
{
var f = QueryFactory.Instance;
var table = f.Table<Book>("t1");
var code = table.Column(Book.CodeProperty, "c1");
var name = table.Column(Book.NameProperty, "c2");
var query = f.Query(
selection: f.Array(code, name),
from: table,
where: f.Constraint(name, "name"),
orderBy: new List<IOrderBy> { f.OrderBy(code), f.OrderBy(name, OrderDirection.Descending) }
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT t1.Code AS c1, t1.Name AS c2
FROM Book AS t1
WHERE t1.Name = {0}
ORDER BY t1.Code ASC, t1.Name DESC");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].ToString() == "name");
}
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_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_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_TableQuery_SelectFromSelectResult()
{
var f = QueryFactory.Instance;
var userSource = f.Table(RF.Concrete<BlogUserRepository>());
var subQuery = f.SubQuery(
query: f.Query(
from: userSource,
where: f.Constraint(
userSource.Column(Entity.IdProperty),
PropertyOperator.Greater, 0
)
),
alias: "T"
);
var query = f.Query(
from: subQuery,
where: f.Constraint(
subQuery.Column(userSource.Column(Entity.IdProperty)),
PropertyOperator.Less, 100
)
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT *
FROM (
SELECT *
FROM BlogUser
WHERE BlogUser.Id > {0}
) AS T
WHERE T.Id < {1}");
}
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_TableQuery_Top()
{
var f = QueryFactory.Instance;
var table = f.Table<Book>();
var query = f.Query(
from: table,
orderBy: new List<IOrderBy>
{
f.OrderBy(table.IdColumn)
}
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query, new PagingInfo(1, 10));
var sql = generator.Sql;
Assert.IsTrue(sql.ToString().StartsWith(@"SELECT TOP 10 *
FROM Book
ORDER BY Book.Id ASC"));
}
public void ORM_TableQuery_Alias()
{
var f = QueryFactory.Instance;
var table = f.Table<Book>("t1");
var code = table.Column(Book.CodeProperty, "c1");
var name = table.Column(Book.NameProperty, "c2");
var query = f.Query(
selection: f.Array(code, name),
from: table,
where: f.Constraint(code, "code")
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT t1.Code AS c1, t1.Name AS c2
FROM Book AS t1
WHERE t1.Code = {0}");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].ToString() == "code");
}
public void ORM_TableQuery_WithoutQuota()
{
var f = QueryFactory.Instance;
var table = f.Table<Book>();
var code = table.Column(Book.CodeProperty);
var name = table.Column(Book.NameProperty);
var query = f.Query(
selection: f.Array(code, name),
from: table,
where: f.Constraint(column: code, value: "code", op: PropertyOperator.Equal)
);
var generator = new SqlServerSqlGenerator { AutoQuota = false };
f.Generate(generator, query);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT Book.Code, Book.Name
FROM Book
WHERE Book.Code = {0}");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].ToString() == "code");
}
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");
}
