public async Task <IEnumerable <CityDto> > GetAll(CancellationToken cancellationToken)
{
ODataSqlQuery odataSqlQuery = ODataSqlBuilder.BuildSqlQuery(GetODataQueryOptions(), tableName: "bit.Cities");
string connectionString = AppEnvironment.GetConfig <string>("BitChangeSetManagerDbConnectionString");
DbConnection dbConnection = await DbConnectionProvider.GetDbConnectionAsync(connectionString, true, cancellationToken);
DbTransaction dbTransaction = DbConnectionProvider.GetDbTransaction(connectionString);
IEnumerable <CityDto> cities = await dbConnection.QueryAsync <CityDto>(odataSqlQuery.SelectQuery, odataSqlQuery.Parts.Parameters, transaction : dbTransaction);
long total = odataSqlQuery.Parts.GetTotalCountFromDb == false?cities.LongCount() : ((await dbConnection.ExecuteScalarAsync <long?>(odataSqlQuery.SelectTotalCountQuery, odataSqlQuery.Parts.Parameters, transaction: dbTransaction)) ?? 0);
Request.Properties["System.Web.OData.TotalCountFunc"] = new Func <long>(() => total);
return(cities);
}
public virtual ODataSqlJsonQuery BuildSqlJsonQuery <TDto>(ODataQueryOptions <TDto> odataQueryOptions, string tableName) where TDto : class
{
ODataSqlQuery odataSqlQuery = BuildSqlQuery(odataQueryOptions, tableName: tableName);
string edmTypeFullPath = $"{odataQueryOptions.Request.GetReaderSettings().BaseUri}$metadata#{odataQueryOptions.Context.Path}";
string finalSelectCountQuery = odataSqlQuery.Parts.GetTotalCountFromDb ? $"({odataSqlQuery.SelectTotalCountQuery}) as '@odata.count' ," : "";
string sqlJsonQuery = $@"select * from (select '{edmTypeFullPath}' as '@odata.context',
{finalSelectCountQuery}
({odataSqlQuery.SelectQuery} FOR JSON PATH) as 'value') as ODataResult
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER";
return(new ODataSqlJsonQuery
{
SqlJsonQuery = sqlJsonQuery,
SqlQuery = odataSqlQuery
});
}
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());
}
