public virtual ODataSqlQuery BuildSqlQuery <TDto>(ODataQueryOptions <TDto> odataQuery, string tableName)
where TDto : class
{
if (odataQuery == null)
{
throw new ArgumentNullException(nameof(odataQuery));
}
TypeInfo dtoType = typeof(TDto).GetTypeInfo();
ODataSqlQueryParts sqlQueryParts = BuildSqlQueryParts(odataQuery);
if (sqlQueryParts.Skip != null)
{
if (sqlQueryParts.Top == null)
{
throw new BadRequestException("$top is not provided while there is a $skip");
}
if (sqlQueryParts.OrderByClause == null)
{
throw new BadRequestException("$orderby is not provided while there is a $skip");
}
}
string whereClause = sqlQueryParts.WhereClause == null ? "" : $"where {sqlQueryParts.WhereClause}";
string offsetClause = sqlQueryParts.Skip == null ? "" : $"offset {sqlQueryParts.Skip} rows";
string topClause = "", fetchRowClause = "";
if (sqlQueryParts.Top != null)
{
if (sqlQueryParts.Skip != null)
{
fetchRowClause = $"fetch next {sqlQueryParts.Top} rows only";
}
else
{
topClause = $"top({sqlQueryParts.Top})";
}
}
string orderByClause = sqlQueryParts.OrderByClause == null ? "" : $"order by {sqlQueryParts.OrderByClause}";
string select = $"select {topClause} {sqlQueryParts.SelectionClause} from {tableName} as [{dtoType.Name}] {whereClause} {orderByClause} {offsetClause} {fetchRowClause}";
string selectCount = $"select count_big(1) from {tableName} as [{dtoType.Name}] {whereClause}";
return(new ODataSqlQuery
{
Parts = sqlQueryParts,
SelectQuery = select,
SelectTotalCountQuery = selectCount
});
}
public virtual IQueryable <TestModel> TestSqlBuilder(ODataQueryOptions <TestModel> odataQuery)
{
IDependencyResolver dependencyResolver = Request.GetOwinContext().GetDependencyResolver();
IValueChecker valueChecker = dependencyResolver.Resolve <IValueChecker>();
ODataSqlQueryParts sqlParts = odataQuery;
valueChecker.CheckValue(sqlParts.WhereClause);
valueChecker.CheckValue(sqlParts.OrderByClause);
valueChecker.CheckValue(sqlParts.Top);
valueChecker.CheckValue(sqlParts.Skip);
valueChecker.CheckValue(sqlParts.Parameters.Values.ToArray());
IODataSqlBuilder odataSqlBuilder = dependencyResolver.Resolve <IODataSqlBuilder>();
ODataSqlQuery sql = odataSqlBuilder.BuildSqlQuery(GetODataQueryOptions(), tableName: "Test.TestModels");
valueChecker.CheckValue(sql.SelectQuery);
valueChecker.CheckValue(sql.SelectTotalCountQuery);
valueChecker.CheckValue(sql.Parts.GetTotalCountFromDb);
return(new TestModel[] { }.AsQueryable());
}
