public async Task <IActionResult> Get([FromQuery] DeviceQueryFilter filter, [FromQuery] DeviceQuerySort sort, [FromQuery] DeviceQueryProjection projection, [FromQuery] DeviceQueryPaging paging, [FromQuery] DeviceQueryOptions options) { var validationResult = _service.ValidateGetDevices( User, filter, sort, projection, paging, options); if (!validationResult.Valid) { return(BadRequest(validationResult.Result)); } var result = await _service.QueryDeviceDynamic( projection, options, filter, sort, paging); if (options.single_only) { if (result == null) { return(NotFound(new AppResultBuilder().NotFound())); } return(Ok(new AppResultBuilder().Success(result.SingleResult))); } return(Ok(new AppResultBuilder().Success(result))); }
public static DynamicSql SqlFilter( this DynamicSql query, DeviceQueryFilter filter) { query = DynamicSql.DeepClone(query); var listFilters = new List <string>(); if (filter.id != null) { var paramName = query.AddAutoIncrParam(filter.id); listFilters.Add($"{nameof(Device)}.{nameof(Device.Id)}=@{paramName}"); } if (filter.name_contains != null) { var paramName = query.AddAutoIncrParam(filter.name_contains); listFilters.Add($"CHARINDEX(@{paramName}, {nameof(Device)}" + $".{nameof(Device.Name)}) > 0"); } if (filter.loc_id != null) { var paramName = query.AddAutoIncrParam(filter.loc_id); listFilters.Add($"{nameof(Device)}.{nameof(Device.LocationId)}=@{paramName}"); } if (listFilters.Any()) { var whereClause = "WHERE " + string.Join(" AND ", listFilters); query.DynamicForm = query.DynamicForm.Replace(DynamicSql.FILTER, whereClause); } return(query); }
public ValidationResult ValidateGetDevices( ClaimsPrincipal principal, DeviceQueryFilter filter, DeviceQuerySort sort, DeviceQueryProjection projection, DeviceQueryPaging paging, DeviceQueryOptions options) { return(ValidationResult.Pass()); }
public async Task <QueryResult <DeviceQueryRow> > QueryDevice( DeviceQueryFilter filter = null, DeviceQuerySort sort = null, DeviceQueryProjection projection = null, DeviceQueryPaging paging = null, DeviceQueryOptions options = null) { var conn = context.Database.GetDbConnection(); var openConn = conn.OpenAsync(); var query = DeviceQuery.CreateDynamicSql(); #region General if (filter != null) { query = query.SqlFilter(filter); } if (projection != null) { query = query.SqlJoin(projection); } DynamicSql countQuery = null; int?totalCount = null; Task <int> countTask = null; if (options != null && options.count_total) { countQuery = query.SqlCount("*"); } if (projection != null) { query = query.SqlProjectFields(projection); } #endregion await openConn; if (options != null && !options.single_only) { #region List query if (sort != null) { query = query.SqlSort(sort); } if (paging != null && (!options.load_all || !DeviceQueryOptions.IsLoadAllAllowed)) { query = query.SqlSelectPage(paging.page, paging.limit); } #endregion #region Count query if (options.count_total) { countTask = conn.ExecuteScalarAsync <int>( sql: countQuery.PreparedForm, param: countQuery.DynamicParameters); } #endregion } var queryResult = await conn.QueryAsync( sql : query.PreparedForm, types : query.GetTypesArr(), map : (objs) => ProcessMultiResults(query, objs), splitOn : string.Join(',', query.GetSplitOns()), param : query.DynamicParameters); if (options != null && options.single_only) { var single = queryResult.FirstOrDefault(); return(new QueryResult <DeviceQueryRow> { SingleResult = single }); } if (options != null && options.count_total) { totalCount = await countTask; } return(new QueryResult <DeviceQueryRow> { Results = queryResult, TotalCount = totalCount }); }