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