AspNetCoreOData
AspNetCoreOData copied to clipboard
OData
problem with $apply and $orderby
It appears the group by issues discussed in https://github.com/OData/AspNetCoreOData/issues/76 are partially fixed using efcore6, .net core 6, and latest odata which is good news. If I do just a simple group by:
/odata/Item?$apply=groupby((ComponentID),aggregate($count%20as%20GroupCount))&$count=true
it works. If I tack an $orderby on the end however, it stops working:
/odata/Item?$apply=groupby((ComponentID),aggregate($count%20as%20GroupCount))&$count=true&$orderby=ComponentID
Yields:
The query specified in the URI is not valid. The given model does not contain the type 'Microsoft.AspNetCore.OData.Query.Wrapper.AggregationWrapper'
I can see on line 54 of QueryBinderContext.cs where it is looking for that AggregationWrapper in my model, and it obviously isn't there. If I comment that out, it gets further, but then pukes with:
Instance property 'ComponentID' is not defined for type 'Microsoft.AspNetCore.OData.Query.Wrapper.AggregationWrapper'
It seems like the basic expression processing is smart enough to deal with AggregationWrapper, but the orderby expression processing code is not. This worked in 3.1 as long as your controller pulled everything into memory first using something like:
if (Request.QueryString.Value != null && Request.QueryString.Value.Contains("$apply"))
return db.Item.ToList().AsQueryable();
else
return db.Item;
That fix no longer works in latest however which has me bummed as we use $apply with $orderby a lot.
@MolallaComm Thanks for reporting this.
For the query binding refactor, we just finished query except for $apply.for $apply, it's in https://github.com/OData/AspNetCoreOData/pull/400
FWIW, I tried running:
http://localhost:64771/Products?$apply=groupby((Category),aggregate($count%20as%20GroupCount))&$count=true&$orderby=Category
against #400 from KenitoInc’s repo and still get the error:
The query specified in the URI is not valid. The given model does not contain the type 'Microsoft.AspNetCore.OData.Query.Wrapper.AggregationWrapper'.
So it doesn't appear to fully address the issue unfortunately.
I'd like to contribute to this work item because we really need this fixed :-). I looked through the code yesterday, and this seems to be non-trivial. If someone from the Odata team can point me in the right direction I'm happy to help.
I'm in the same boat as the above user; really need it fixed and would like to contribute. I started debugging the code locally and got some clues as to what's happening but am not sure as to what the best way to fix it is.
- The exception thrown from inside the QueryBinderContext constructor is because the code assumes that the clrType param passed in will be an edm type. This isn't the case when the type is a GroupBy/Aggregation wrapper. I've added a check that skips the call to
Model.GetEdmTypeReference(ElementClrType)ifif typeof(IEdmObject).IsAssignableFrom(ElementClrType)is not true. This is just a symptom of the actual problem. - The logic in OrderByQueryOption.cs seems to be the failing point. All OrderBy property nodes remain unchanged after the group by transformation and the single property access fails on type AggregateExpressionWrapper. I'm guessing we need to reconstruct the order by clause before we get here to access the "Item" property of the underlying dictionary instead.
Registering a comment just to amplify this issue. I am getting this error using OData v8.0.6/EF Core 6.0.1/.NET Core 6.0.1.
The issue still has not been resolved as of 1/29. Using AspNetCoreOData v8.0.6/EF Core 6.0.1/.NET Core 6.0.1
I get the same error when $apply and $top are given
The query specified in the URI is not valid. The given model does not contain the type 'Microsoft.AspNetCore.OData.Query.Wrapper.AggregationWrapper'
Net 6 EF 6 OData 8.0.4
.Net 6 EF 6 OData 8.0.7
Test Queries: ?$apply=groupby((Name)) ?$apply=groupby((Name),aggregate($count as COUNT))
I have API Controllers & ODataControllers both result in this error.
In the same boat as everyone else. I want to add the same exact error occurs when adding $order OR $top. I did find that you need to return IQueryable<T> directly for the aggregate to work without $order or $top. If you return an ActionResult<IQueryable<T>> then the aggregate will not work at all.
We are working on analytic project and we need this bug fixed. Can we help in some way?
I'd like to contribute to this work item because we really need this fixed :-). I looked through the code yesterday, and this seems to be non-trivial. If someone from the Odata team can point me in the right direction I'm happy to help.
We are working on analytic project and we need this bug fixed. Can we help in some way?
I reverted to 8.0.0 using EFCore6 and .NET Core 6 and it works for me.
I'd like to contribute to this work item because we really need this fixed :-). I looked through the code yesterday, and this seems to be non-trivial. If someone from the Odata team can point me in the right direction I'm happy to help.
We are working on analytic project and we need this bug fixed. Can we help in some way?
I reverted to 8.0.0 using EFCore6 and .NET Core 6 and it works for me.
Thank you, but we cannot downgrade because we are using the $search feature.
The issue still has not been resolved as of 3/14. Using AspNetCoreOData v8.0.8/EF Core 6.0.2/.NET Core 6.0.2
Downgrading to 8.0.0 does the trick for now.
Is someone working on this bug? It’s not possible that we cannot use pagination with simple groupby queries. And of course this is a BUG not an enhancement.
It appears the group by issues discussed in #76 are partially fixed using efcore6, .net core 6, and latest odata which is good news. If I do just a simple group by:
/odata/Item?$apply=groupby((ComponentID),aggregate($count%20as%20GroupCount))&$count=true
it works. If I tack an $orderby on the end however, it stops working:
/odata/Item?$apply=groupby((ComponentID),aggregate($count%20as%20GroupCount))&$count=true&$orderby=ComponentID
Yields:
The query specified in the URI is not valid. The given model does not contain the type 'Microsoft.AspNetCore.OData.Query.Wrapper.AggregationWrapper'
I can see on line 54 of QueryBinderContext.cs where it is looking for that AggregationWrapper in my model, and it obviously isn't there. If I comment that out, it gets further, but then pukes with:
Instance property 'ComponentID' is not defined for type 'Microsoft.AspNetCore.OData.Query.Wrapper.AggregationWrapper'
It seems like the basic expression processing is smart enough to deal with AggregationWrapper, but the orderby expression processing code is not. This worked in 3.1 as long as your controller pulled everything into memory first using something like:
if (Request.QueryString.Value != null && Request.QueryString.Value.Contains("$apply")) return db.Item.ToList().AsQueryable(); else return db.Item;That fix no longer works in latest however which has me bummed as we use $apply with $orderby a lot.
Hello I dont know if you have found a fix by yourself, but just in the case I'm going to share my "workaround" for this bug. The problem is that after groupby transformation the original CLR Type is swapped with an AggregationWrapper Type and of course we cannot use anymore the "GetPropertyExpression" function declared in the QueryBinder because we have lost the original CLR Type. The first thing to do is to rewrite that function to work also with AggregationWrapper Type. This workaround works only if you first pull everything into memory as for the 3.1 version.
ODataQueryContext.cs -> line 143 add the following property.
/// <summary>
/// Gets the CLR type of the element before the aggregation wrapper override.
/// </summary>
public Type BeforeAggregationClrType { get; internal set; }
ODataQueryOptions.cs -> line 364 save CLR Type before aggregation.
/// Save source clr type, before aggregation wrapper override.
this.Context.BeforeAggregationClrType = this.Context.ElementClrType;
QueryBinderContext.cs -> line 136 modify as follows.
/// <summary>
/// Gets the Element Clr type.
/// </summary>
public Type ElementClrType { get; internal set; }
/// <summary>
/// Gets the CLR type of the element before the aggregation wrapper override.
/// </summary>
public Type BeforeAggregationClrType { get; }
/// <summary>
/// Gets the CLR type of the element after the aggregation wrapper override.
/// </summary>
public Type AfterAggregationClrType { get; }
QueryBinderContext.cs -> line 48 modify as follows.
public QueryBinderContext(IEdmModel model, ODataQuerySettings querySettings, Type clrType,
Type beforeAggregationClrType = null)
{
Model = model ?? throw Error.ArgumentNull(nameof(model));
QuerySettings = querySettings ?? throw Error.ArgumentNull(nameof(querySettings));
// Try to use before aggregation clr type or fallback to old clr type.
ElementClrType = beforeAggregationClrType ?? clrType ?? throw Error.ArgumentNull(nameof(clrType));
// Store before aggregation clr type.
BeforeAggregationClrType = beforeAggregationClrType ?? clrType;
// Store after aggregation clr type.
AfterAggregationClrType = clrType;
ElementType = Model.GetEdmTypeReference(ElementClrType)?.Definition;
// Check if element type is null and not of AggregationWrapper type.
if (ElementType == null && ElementClrType != typeof(AggregationWrapper))
{
throw new ODataException(Error.Format(SRResources.ClrTypeNotInModel, ElementClrType.FullName));
}
OrderByQueryOption.cs -> line 254 modify binderContext initialization.
// Create new QueryBinderContext instance using the before aggregation clr type.
QueryBinderContext binderContext = new QueryBinderContext(Context.Model, querySettings,
Context.ElementClrType, Context.BeforeAggregationClrType);
BinderExtensions.cs -> line 166 restore after aggregation CLR Type.
// Restore aggregation wrapper.
context.ElementClrType = context.AfterAggregationClrType;
QueryBinder.cs -> line 1182 modify as follows.
if (context.ElementClrType == typeof(AggregationWrapper))
{
return GetFlattenedPropertyExpression(propertyPath, context)
?? ConvertNonStandardPrimitives(GetPropertyExpression(source, propertyName, isAggregated: true), context);
}
return GetFlattenedPropertyExpression(propertyPath, context)
?? ConvertNonStandardPrimitives(GetPropertyExpression(source, propertyName), context);
Finally QueryBinder.cs -> line 1193 modify GetPropertyExpression.
internal static Expression GetPropertyExpression(Expression source, string propertyPath, bool isAggregated = false)
{
string[] propertyNameParts = propertyPath.Split('\\');
Expression propertyValue = source;
foreach (var propertyName in propertyNameParts)
{
// Trying to fix problem with $apply and $orderby. https://github.com/OData/AspNetCoreOData/issues/420
if (isAggregated)
{
propertyValue = Expression.Property(propertyValue, "Values");
var propertyInfo = typeof(Dictionary<string, object>).GetProperty("Item");
var arguments = new List<Expression> { Expression.Constant(propertyName) };
propertyValue = Expression.MakeIndex(propertyValue, propertyInfo, arguments);
}
else propertyValue = Expression.Property(propertyValue, propertyName);
}
return propertyValue;
}
I've not done a lot of tests but if you have time you can help me to check if every scenario works with this. Sorry to all for my bad english.
Seems any operator added to $apply breaks the query.
This is fine ... http://localhost:64771/People?$apply=groupby((FirstName),aggregate($count as Count))
but these error ... http://localhost:64771/People?$apply=groupby((FirstName),aggregate($count as Count))&$orderby=FirstName http://localhost:64771/People?$apply=groupby((FirstName),aggregate($count as Count))&$top=2
Downgrading to 8.0.0 doesn't work for me.
Is my syntax wrong? If not, does anyone have any news on a fix?
http://LOCALHOST:5010/odata/controllername?&keyfld=12&$apply=groupby(( GL_AGENT_INTL, AGENT_NM, ACC_INTL, ACC_NM,AREA_NM,CITY_NM,STATE_NM,ACC_PHONE,ACC_MOBILE ), aggregate(BAL_AMT with sum as BALAMT))&$orderby=BALAMT desc
error "message": "The query specified in the URI is not valid. The given model does not contain the type 'Microsoft.AspNetCore.OData.Query.Wrapper.AggregationWrapper'.",
Please Solve
Thanks @MattiaMagliocchetti! I had some time to try your fixes out last Friday afternoon, and with the caveat that you still have to use db.Item.ToList().AsQueryable(); in your controllers, your fix seems to work well for all my use cases. In fact it works better because $top and $skip work with the new version which allows you do grouping and paging . I can do a pull request if it would be helpful for others to have these fixes until such time as somebody from the odata team can do a proper fix.
Thanks @MattiaMagliocchetti! I had some time to try your fixes out last Friday afternoon, and with the caveat that you still have to use db.Item.ToList().AsQueryable(); in your controllers, your fix seems to work well for all my use cases. In fact it works better because $top and $skip work with the new version which allows you do grouping and paging . I can do a pull request if it would be helpful for others to have these fixes until such time as somebody from the odata team can do a proper fix.
ToListing the whole damn table is not a solution, it will work ONLY if you have a few records.
In practice it works ok for tables with several hundred thousand rows – I just did a random groupby on a table with 345000 rows and it took about 7 seconds. Definitely goes down hill fast though the more rows you have. Hopefully someone will come up with a proper fix that does everything on the DB side efficiently some day soon.
In practice it works ok for tables with several hundred thousand rows – I just did a random groupby on a table with 345000 rows and it took about 7 seconds. Definitely goes down hill fast though the more rows you have. Hopefully someone will come up with a proper fix that does everything on the DB side efficiently some day soon.
Hello, I'm using another approach to the problem and for my necessity is better than the native odata implementation. I've writed a custom odata function for my groupby necessity. I'll show you all my code used in my base odatacontroller hope that this will help someone on my same boat. Sorry if I've pasted all my code, but I don't have enough time at the moment.
You can use it like this (GET ../odata/[controller]/GroupBy(propertyName='Location$Name')) for 1 level depth, or like this for simple properties (GET ../odata/[controller]/GroupBy(propertyName='LocationId')).
You can use all of this odata features: $skip, $top, $filter and $count.
This my odata base controller implementation.
namespace Core.Server.Models
{
public abstract class SerpControllerBase<TEntity> : ODataController where TEntity : SerpEntityBase
{
/// <summary>
/// Indicates if the entity can be archived. Default is true.
/// </summary>
public bool CanBeArchived { get; set; } = true;
/// <summary>
/// Indicates if the entity can be deleted. Default is true.
/// </summary>
public bool CanBeDeleted { get; set; } = true;
/// <summary>
/// Indicates if the default entity can be deleted. Default is false.
/// </summary>
public bool CanDefaultBeDeleted { get; set; }
/// <summary>
/// Used to get entity name property.
/// </summary>
public Expression<Func<TEntity, object?>>? NameProperty { get; set; }
/// <summary>
/// Used to compute the sum on group by query.
/// </summary>
public Expression<Func<TEntity, decimal>>? SumBy { get; set; }
/// <summary>
/// Used to execute user action before post.
/// </summary>
public Func<TEntity, Task>? OnBeforePost { get; set; }
/// <summary>
/// Used to execute user action after post.
/// </summary>
public Func<TEntity, Task>? OnAfterPost { get; set; }
#nullable disable
private readonly MethodInfo _groupByMethod =
GenericMethodOf(_ => Queryable.GroupBy(default, default(Expression<Func<int, int>>)));
private readonly MethodInfo _selectMethod =
GenericMethodOf(_ => Queryable.Select(default(IQueryable<int>), i => i));
private readonly MethodInfo _createMethod = typeof(IQueryProvider).GetTypeInfo()
.GetDeclaredMethods("CreateQuery")
.Where(m => m.IsGenericMethod)
.FirstOrDefault() ??
throw new InvalidOperationException("Failed to get IQueryProvider.CreateQuery() method.");
private readonly MethodInfo _countMethod =
GenericMethodOf(_ => Enumerable.Count(default(IEnumerable<int>)));
private readonly MethodInfo _sumMethod =
GenericMethodOf(_ => Enumerable.Sum(default, default(Func<int, decimal>)));
private readonly MethodInfo _toString = typeof(object).GetTypeInfo().GetDeclaredMethod("ToString") ??
throw new InvalidOperationException("Failed to get object.ToString() method.");
#nullable enable
protected readonly ILogger<SerpControllerBase<TEntity>> _logger;
protected readonly ApplicationDbContext _applicationDbContext;
protected readonly DbSet<TEntity> _entities;
public SerpControllerBase(ILogger<SerpControllerBase<TEntity>> logger,
ApplicationDbContext applicationDbContext)
{
(_logger, _applicationDbContext) = (logger, applicationDbContext);
// Set entities db set.
_entities = _applicationDbContext.Set<TEntity>();
}
[HttpGet]
[EnableQueryAsync]
public virtual IQueryable<TEntity> Get(ODataQueryOptions options)
{
// Check if odata queries contains $apply query.
// In this case we raise an exception because odata groupby dont work.
// We have to use our own groupby implementation.
if (options.Apply?.RawValue?.Contains("groupby") == true && (options.Skip is not null ||
options.Top is not null || options.OrderBy is not null))
{
throw new NotImplementedException("Groupby is not implemented due to odata bug. " +
"You can use GroupBy(name=) function to avoid this problem.");
}
// Return result. We use this for the non $apply scenario.
return _entities;
}
[HttpGet]
[EnableQueryAsync]
public virtual SingleResult<TEntity> Get([FromODataUri] int key)
{
// Return single result. We have to find an async way. // TODO
return SingleResult.Create(_entities.Where(e => e.Id == key));
}
[HttpPost]
public virtual async Task<IActionResult> Post([FromBody] TEntity entity)
{
// Check if model is valid.
if (!ModelState.IsValid) return ValidationProblem(ModelState);
// Execute user action before post.
if (OnBeforePost is not null) await OnBeforePost.Invoke(entity);
// Try to add entity to db context.
var result = await _entities.AddAsync(entity);
try
{
// Save changes to db context.
await _applicationDbContext.SaveChangesAsync();
}
catch (DbUpdateException ex)
{
// Check postgres constraint violation.
return CheckConstraintViolation(ex);
}
// Execute user action after post.
if (OnAfterPost is not null) await OnAfterPost.Invoke(result.Entity);
// Return result.
return CreatedAtAction(nameof(Get), new { key = result.Entity.Id }, result.Entity);
}
[HttpPatch]
public virtual async Task<IActionResult> Patch([FromODataUri] int key, [FromBody] Delta<TEntity> entityDelta)
{
// Check if model is valid.
if (!ModelState.IsValid) return ValidationProblem(ModelState);
// Try to get entity from db context.
if (await _entities.FindAsync(key) is TEntity entity)
{
// Patch entity.
entityDelta.Patch(entity);
try
{
// Save changes to db context.
await _applicationDbContext.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!await _entities.EntityExist(key)) return NotFound();
else throw;
}
catch (DbUpdateException ex)
{
// Check postgres constraint violation.
return CheckConstraintViolation(ex);
}
// Return result.
return Updated(entity);
}
// Entity not found.
return EntityNotFound(key);
}
[HttpPut]
public virtual async Task<IActionResult> Put([FromODataUri] int key, [FromBody] TEntity entity)
{
// Check if model is valid.
if (!ModelState.IsValid) return ValidationProblem(ModelState);
// Check if parameter key is equal to entity key.
if (key != entity.Id) return BadRequest($"{nameof(key)} parameter should be equal to entity id.");
// Set modified state to entity.
_applicationDbContext.Entry(entity).State = EntityState.Modified;
try
{
// Save changes to db context.
await _applicationDbContext.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!await _entities.EntityExist(key)) return NotFound();
else throw;
}
catch (DbUpdateException ex)
{
// Check postgres constraint violation.
return CheckConstraintViolation(ex);
}
// Return result.
return Updated(entity);
}
[HttpDelete]
public virtual async Task<IActionResult> Delete([FromODataUri] int key)
{
// Try to get entity from db context.
if (await _entities.FindAsync(key) is TEntity entity)
{
// Check if entity can be deleted.
if (!CanBeDeleted)
UserException.DeleteError(entityName: GetEntityName(entity));
// Check if entity is default.
if (IsDefault(entity) && !CanDefaultBeDeleted)
{
UserException.DeleteDefaultError(entityName: GetEntityName(entity),
canBeArchived: CanBeArchived);
}
// Remove entity from db context.
_entities.Remove(entity);
try
{
// Save changes to db context.
await _applicationDbContext.SaveChangesAsync();
}
catch (DbUpdateException e)
{
// Check if exception is dependency exception.
if (e.InnerException is PostgresException postgresException)
{
if (postgresException.SqlState.Contains("23503"))
{
UserException.DeleteDependencyError(entityName: GetEntityName(entity),
canBeArchived: CanBeArchived);
}
}
// Rethrow the exception.
throw;
}
// Return result.
return NoContent();
}
// Entity not found.
return EntityNotFound(key);
}
[HttpPost]
public virtual async Task<IActionResult> ArchiveEntity([FromODataUri] int key)
{
// Try to get entity from db context.
if (await _entities.FindAsync(key) is TEntity entity)
{
// Check if entity can be archived.
if (!CanBeArchived)
UserException.ArchiveError(entityName: GetEntityName(entity));
// Check if archived property exist.
if (_applicationDbContext.Entry(entity).Property("IsArchived") is PropertyEntry propertyEntry)
{
// Archive entity.
propertyEntry.CurrentValue = true;
// Save changes to db context.
await _applicationDbContext.SaveChangesAsync();
// Return result.
return NoContent();
}
else UserException.ArchiveError(entityName: GetEntityName(entity));
}
// Entity not found.
return EntityNotFound(key);
}
[HttpGet]
public virtual async Task<SerpEntityAggregationWrapper> GroupBy([FromODataUri] string propertyName,
ODataQueryOptions<TEntity> options)
{
// Check if property name is valid.
if (!string.IsNullOrWhiteSpace(propertyName))
{
// Create new odata settings.
var odataQuerySettings = new ODataQuerySettings();
// Create new serp entity aggregation wrapper.
var serpEntityAggregationWrapper = new SerpEntityAggregationWrapper();
// Get entities as IQueryable.
var query = _entities.AsQueryable();
// Apply filter query.
if (options.Filter is not null)
query = options.Filter.ApplyTo(query, odataQuerySettings).Cast<TEntity>();
// Check if query is valid.
if (query is not null)
{
// Apply group by query.
var groupByQuery = ApplyGroupBy(query, propertyName);
// Check if group by query is valid.
if (groupByQuery is not null)
{
// Apply order by query.
groupByQuery = groupByQuery.OrderBy(e => e.Name);
// Get entities count.
serpEntityAggregationWrapper.Count = await groupByQuery.CountAsync();
// Apply skip query.
if (options.Skip is not null)
groupByQuery = options.Skip.ApplyTo(groupByQuery, odataQuerySettings);
// Apply top query.
if (options.Top is not null)
groupByQuery = options.Top.ApplyTo(groupByQuery, odataQuerySettings);
// Execute query and get all entities.
if (await groupByQuery.ToListAsync() is IEnumerable<SerpEntityAggregation> entities)
{
// Set entities.
serpEntityAggregationWrapper.AddEntityRange(entities);
}
// Return result.
return serpEntityAggregationWrapper;
}
}
}
// Return empty result.
return SerpEntityAggregationWrapper.Empty();
}
public NotFoundObjectResult EntityNotFound(int id)
=> NotFound($"{typeof(TEntity).Name} [{id}] not found.");
public IActionResult ValidationProblem(SerpValidationProblemDetails serpValidationProblemDetails)
{
// Check if serp validation problem details is valid.
if (serpValidationProblemDetails is null)
{
throw new ArgumentNullException(nameof(serpValidationProblemDetails));
}
else if (!serpValidationProblemDetails.HasProblems())
{
throw new InvalidOperationException("No problems founds.");
}
// Return new validation problem.
return ValidationProblem(new ValidationProblemDetails(serpValidationProblemDetails.Build()));
}
private IQueryable<SerpEntityAggregation>? ApplyGroupBy(IQueryable<TEntity> query, string propertyName)
{
// Check if query is valid.
if (query is null)
throw new ArgumentNullException(nameof(query));
// Check if property name is valid.
if (string.IsNullOrWhiteSpace(propertyName))
throw new ArgumentNullException(nameof(propertyName));
// Create $it parameter.
var entityLambdaExpression = Expression.Parameter(typeof(TEntity), "$it");
// Group by property expression.
LambdaExpression groupByProperty;
// Set grouped by property.
var groupedBy = propertyName;
// Check if property name is navigation property. For example Location$Name.
if (propertyName.IndexOf('$') is int index && index > 0 && index < propertyName.Length)
{
// Get parent property name. For example Location.
var parentProperty = propertyName[..index];
// Get child property name. For example Name.
var childProperty = propertyName[(index + 1)..];
// Set grouped by property. We replace $ with / for odata conventions.
groupedBy = propertyName.Replace('$', '/');
// Get group by property expression.
groupByProperty = Expression.Lambda(Expression.Property(Expression
.Property(entityLambdaExpression, parentProperty), childProperty), entityLambdaExpression);
}
else
{
// Get group by property expression.
groupByProperty = Expression.Lambda(Expression
.Property(entityLambdaExpression, propertyName), entityLambdaExpression);
}
// Get group by property type.
var groupByPropertyType = groupByProperty.Body.Type;
// Get IGrouping type.
var iGroupingType = typeof(IGrouping<,>).MakeGenericType(groupByPropertyType, typeof(TEntity));
// Redefine lambda expression.
var groupingLambdaExpression = Expression.Parameter(iGroupingType, "$it");
// Create new member bingins collection.
var memberBindings = new List<MemberBinding>();
// Get entity aggregation grouped by property.
var entityAggregationGroupedByProperty =
GetSerpEntityAggregationProperty(nameof(SerpEntityAggregation.GroupedBy));
// Get entity aggregation name property.
var entityAggregationNameProperty =
GetSerpEntityAggregationProperty(nameof(SerpEntityAggregation.Name));
// Get entity aggregation count property.
var entityAggregationCountProperty =
GetSerpEntityAggregationProperty(nameof(SerpEntityAggregation.Count));
// Get entity aggregation sum property.
var entityAggregationSumProperty =
GetSerpEntityAggregationProperty(nameof(SerpEntityAggregation.Sum));
// Get IGrouping key property.
var keyProperty = Expression.Property(groupingLambdaExpression, "Key");
// Get key property type.
var keyPropertyType = Nullable.GetUnderlyingType(keyProperty.Type) ?? keyProperty.Type;
// Add grouped by property binding.
memberBindings.Add(Expression.Bind(entityAggregationGroupedByProperty,
Expression.Constant(groupedBy, typeof(string))));
// Check if key property type is not string nor enum.
// In this case we use the to object.ToString() method call.
if (keyPropertyType != typeof(string) && !keyPropertyType.IsEnum)
{
// Add name property binding. Here we use the to object.ToString() method call.
memberBindings.Add(Expression.Bind(entityAggregationNameProperty,
Expression.Call(keyProperty, _toString)));
}
else if (keyPropertyType.IsEnum)
{
throw new NotImplementedException("Groupby enum not implemented."); // TODO
//MethodInfo enumToString = keyPropertyType.GetMethod("ToString", Type.EmptyTypes) ??
// throw new InvalidOperationException($"Failed to get {keyPropertyType.Name}.ToString() method.");
//memberBindings.Add(Expression.Bind(entityAggregationNameProperty,
// Expression.Call(keyProperty, enumToString)));
}
else memberBindings.Add(Expression.Bind(entityAggregationNameProperty, keyProperty));
// Add count property binding.
memberBindings.Add(Expression.Bind(entityAggregationCountProperty,
Expression.Call(null, _countMethod.MakeGenericMethod(typeof(TEntity)),
new[] { groupingLambdaExpression })));
// Check if sum by user expression is valid.
// If is valid we replace entity with lambda entity.
if (ReplaceSumParameter(SumBy, entityLambdaExpression) is Expression sumByExpression)
{
// Add sum property binding.
memberBindings.Add(Expression.Bind(entityAggregationSumProperty,
Expression.Call(null, _sumMethod.MakeGenericMethod(typeof(TEntity)),
new Expression[] { groupingLambdaExpression, sumByExpression })));
}
// Create new serp entity aggregation.
var entityAggregation = Expression.Lambda(Expression
.MemberInit(Expression.New(typeof(SerpEntityAggregation)), memberBindings), groupingLambdaExpression);
// Add group by expression.
Expression groupQuery = Expression.Call(null, _groupByMethod
.MakeGenericMethod(typeof(TEntity), groupByPropertyType), new[] { query.Expression, groupByProperty });
// Add select expression.
Expression select = Expression.Call(null, _selectMethod
.MakeGenericMethod(iGroupingType, typeof(SerpEntityAggregation)), new[] { groupQuery, entityAggregation });
// Try to create and return new IQueryable.
return _createMethod?.MakeGenericMethod(typeof(SerpEntityAggregation))?
.Invoke(query.Provider, new[] { select }) as IQueryable<SerpEntityAggregation>;
}
private static Expression? ReplaceSumParameter(
Expression<Func<TEntity, decimal>>? expression, ParameterExpression parameter)
{
// Check if expression is valid.
if (expression is null) return null;
// Check if parameter is valid.
if (parameter is null)
throw new ArgumentNullException(nameof(parameter));
// Create new parameter replacer.
var parameterReplacer = new ExpressionParameterReplacer(expression.Parameters[0], parameter);
// Replace expression body parameter.
if (parameterReplacer.Visit(expression.Body) is Expression body)
{
// Return new lambda expression.
return Expression.Lambda(body, parameter);
}
// Something has failed return null.
return null;
}
private static PropertyInfo GetSerpEntityAggregationProperty(string propertyName)
{
// Check if property name is valid.
if (string.IsNullOrWhiteSpace(propertyName))
throw new ArgumentNullException(nameof(propertyName));
// Try to return property.
return typeof(SerpEntityAggregation).GetProperty(propertyName) ??
throw new InvalidOperationException($"Unable to get {propertyName} property.");
}
private static MethodInfo GenericMethodOf<TReturn>(Expression<Func<object, TReturn>> expression)
{
// Return generic method definition.
return GenericMethodOf(expression as Expression);
}
private static MethodInfo GenericMethodOf(Expression expression)
{
// Check if expression node type is lambda type.
if (expression.NodeType != ExpressionType.Lambda)
throw new ArgumentException($"{nameof(expression)} nodetype is not lambda type.");
// Get lambda expression.
if (expression is LambdaExpression lambdaExpression)
{
// Check if lambda expression node type is call type.
if (lambdaExpression.Body.NodeType != ExpressionType.Call)
throw new InvalidOperationException("Expression node type should be call type.");
// Check if lambda body expression is method call expression.
if (lambdaExpression.Body is MethodCallExpression methodCallExpression)
{
// Return method definition.
return methodCallExpression.Method.GetGenericMethodDefinition();
}
else throw new InvalidOperationException("Expression node type should be call type.");
}
else
{
throw new InvalidCastException($"{nameof(expression)} cannot be converted to lambda expression.");
}
}
private static bool IsDefault(TEntity entity)
{
// Check if entity implements ISerpDefaultEntity.
if (entity is ISerpDefaultEntity defaultEntity)
{
// Return result.
return defaultEntity.IsDefault;
}
// At this point the entity is not default.
return false;
}
private string? GetEntityName(TEntity entity)
{
// Check if entity is valid.
if (entity is null)
throw new ArgumentNullException(nameof(entity));
// Get compiled expression.
if (NameProperty?.Compile() is Func<TEntity, object?> compiledExpression)
{
// Try to get expression value.
if (compiledExpression(entity) is object value)
{
// Convert value to string.
return value.ToString();
}
}
// Unable to get entity name.
return null;
}
public IActionResult CheckConstraintViolation(Exception exception)
{
// Check if exception is valid.
if (exception is null)
throw new ArgumentNullException(nameof(exception));
// Check if inner exception is postgres exception.
if (exception.InnerException is PostgresException postgresException)
{
// Check if sql state contains constraint violation code 23505.
if (postgresException.SqlState?.Contains("23505") == true)
{
// Try to get constraint property name.
if (postgresException.ConstraintName?.LastIndexOf('_') is int index &&
index < postgresException.ConstraintName.Length)
{
var propertyName = postgresException.ConstraintName[(index + 1)..];
// Check if property name is valid.
if (!string.IsNullOrWhiteSpace(propertyName))
{
// Create new validation problems details.
var serpValidationProblemDetails = new SerpValidationProblemDetails();
// Add constraint problem.
serpValidationProblemDetails.AddProblem(propertyName, "Should be unique.");
// Return result.
return ValidationProblem(serpValidationProblemDetails);
}
}
}
}
// At this point something has failed. We rethrow the exception.
throw exception;
}
}
}
This is the aggregation wrapper.
public class SerpEntityAggregationWrapper
{
private List<SerpEntityAggregation> _entities = new();
public int Count { get; set; }
public IEnumerable<SerpEntityAggregation> Entities => _entities ?? Enumerable.Empty<SerpEntityAggregation>();
public static SerpEntityAggregationWrapper Empty()
{
return new SerpEntityAggregationWrapper
{
Count = 0
};
}
public void AddEntityRange(IEnumerable<SerpEntityAggregation> entities)
{
// Check if entities is valid.
if (entities is null)
throw new ArgumentNullException(nameof(entities));
// Add entities to collection.
_entities = new List<SerpEntityAggregation>(entities);
}
public void AddEntity(SerpEntityAggregation entity)
{
// Check if entity is valid.
if (entity is null)
throw new ArgumentNullException(nameof(entity));
// Add entity to collection.
_entities.Add(entity);
}
public bool RemoveEntity(SerpEntityAggregation entity)
{
// Check if entity is valid.
if (entity is null)
throw new ArgumentNullException(nameof(entity));
// Remove entity from collection.
return _entities.Remove(entity);
}
public void ClearEntities()
{
// Clear entities collection.
_entities.Clear();
}
}
This is the entity aggregation.
public class SerpEntityAggregation
{
public string? GroupedBy { get; set; }
public string? Name { get; set; }
public int Count { get; set; }
public decimal Sum { get; set; }
}
This is the response for my edm model.
{
"@odata.context": "https://localhost:7005/odata/$metadata#Core.Shared.Models.Dtos.SerpEntityAggregationWrapper",
"Count": 4,
"Entities": [
{
"GroupedBy": "LocationId",
"Name": "1052",
"Count": 1,
"Sum": 9
},
{
"GroupedBy": "LocationId",
"Name": "20",
"Count": 5,
"Sum": 205
},
{
"GroupedBy": "LocationId",
"Name": "21",
"Count": 6,
"Sum": -425
},
{
"GroupedBy": "LocationId",
"Name": "23",
"Count": 6,
"Sum": 211
}
]
}
@MattiaMagliocchetti I'd strongly recommend trying to minimize your code examples to just the core of the functionality you are discussing. It is really hard to navigate these threads with absolutely huge code chunks like what you posted.
Additionally, you might want to look into how to syntax-highlight code blocks in github, as that can make your code a lot more readable.
@MattiaMagliocchetti I'd strongly recommend trying to minimize your code examples to just the core of the functionality you are discussing. It is really hard to navigate these threads with absolutely huge code chunks like what you posted.
Additionally, you might want to look into how to syntax-highlight code blocks in github, as that can make your code a lot more readable.
Sorry but I’m new on GitHub. Tomorrow I’ll edit my code example with only the core functionality.
The last working version for the following query is 8.0.4. (net & ef 6.0.3)
https://localhost:44488/odata/XXX/?$apply=groupby((name))&$count=true&$top=50
This should really be a P1. It's broken core functionality on a patch version, for almost four months.
@MolallaComm I see your PR looks a lot like this post from @MattiaMagliocchetti . Can you confirm that? And can both of you confirm that this seems to have fixed your issue? I have more to say that I will put in the PR, but I just want to make sure that the PR is the direction we want to go.
Hi Garrett,
Yes, I took the written changes Mattia contributed further up in the thread and made a PR from them. I have tested it and we are using it in production. If you want to merge it, that would be fine with me, if not, I will just leave my fork around on github for others to use until a better solution comes along. I’m not sure there are tests for $apply yet or if they work as intended, but if not, we should probably add some so we can hopefully prevent it from getting broken again the future. Unfortunately, I’m super slammed at the moment and won’t have time to contribute for a few weeks. I think a few of the changes in my PR may have impacted the XML documentation that gets generated, and maybe additional work is needed there, but I didn’t take the time to look into it.
From: Garrett DeBruin @.> Sent: Sunday, April 24, 2022 12:43 PM To: OData/AspNetCoreOData @.> Cc: Jeff Stockett @.>; Mention @.> Subject: Re: [OData/AspNetCoreOData] problem with $apply and $orderby (Issue #420)
@MolallaCommhttps://github.com/MolallaComm I see your PRhttps://github.com/OData/AspNetCoreOData/pull/543 looks a lot like this posthttps://github.com/OData/AspNetCoreOData/issues/420#issuecomment-1069024981 from @MattiaMagliocchettihttps://github.com/MattiaMagliocchetti . Can you confirm that? And can both of you confirm that this seems to have fixed your issue? I have more to say that I will put in the PR, but I just want to make sure that the PR is the direction we want to go.
— Reply to this email directly, view it on GitHubhttps://github.com/OData/AspNetCoreOData/issues/420#issuecomment-1107905697, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AALYQS7SQJCIF3SZLZOG3W3VGWP3BANCNFSM5LNPWUYQ. You are receiving this because you were mentioned.Message ID: @.@.>>
@MolallaComm I see your PR looks a lot like this post from @MattiaMagliocchetti . Can you confirm that? And can both of you confirm that this seems to have fixed your issue? I have more to say that I will put in the PR, but I just want to make sure that the PR is the direction we want to go.
@corranrogue9 Be aware that this doesn't fix the issue, it only works when pulling the IQueryable into memory, which wasn't the previous behavior, and is not feasible for huge datasets. Until 8.0.4 $apply combined with $orderby, $top, $skip or pagination worked fine and with the 8.0.5 release the query option binder refactor broke this functionality.
Also, as referenced above, #400 doesn't fix this issue, even when pulling the IQueryable into memory.
Anyway, thanks @MattiaMagliocchetti and @MolallaComm for the contribution!
@KenitoInc @xuzhg Is $apply and $orderby issue is fixed or not with Microsoft.AspNetcore.Odata 8.0.10?
