I'm getting an error when using $filter with $apply=groupby. It appears to happen only when the filter field is not in the groupby expression. Here is the error message: Instance property 'DRG_Definition' is not defined for type 'DynamicTypeWrapper'
This works fine: http://localhost:9810/odata/PAYMENTS?$apply=groupby((Provider_Id,DRG_Definition),aggregate(Total_Payments with sum as Total_Payments))&$filter=(DRG_Definition eq '069 - TRANSIENT ISCHEMIA')
This throws the error (only difference is no DRG_Definition field in the groupby): http://localhost:9810/odata/PAYMENTS?$apply=groupby((Provider_Id),aggregate(Total_Payments with sum as Total_Payments))&$filter=(DRG_Definition eq '069 - TRANSIENT ISCHEMIA')
Updated with my packages and code samples below:
<packages>
<package id="EntityFramework" version="6.1.3" targetFramework="net452" />
<package id="Microsoft.ApplicationInsights" version="2.1.0" targetFramework="net452" />
<package id="Microsoft.ApplicationInsights.Agent.Intercept" version="1.2.1" targetFramework="net452" />
<package id="Microsoft.ApplicationInsights.DependencyCollector" version="2.1.0" targetFramework="net452" />
<package id="Microsoft.ApplicationInsights.JavaScript" version="0.15.0-build58334" targetFramework="net452" />
<package id="Microsoft.ApplicationInsights.PerfCounterCollector" version="2.1.0" targetFramework="net452" />
<package id="Microsoft.ApplicationInsights.Web" version="2.1.0" targetFramework="net452" />
<package id="Microsoft.ApplicationInsights.WindowsServer" version="2.1.0" targetFramework="net452" />
<package id="Microsoft.ApplicationInsights.WindowsServer.TelemetryChannel" version="2.1.0" targetFramework="net452" />
<package id="Microsoft.AspNet.OData" version="5.9.1" targetFramework="net452" />
<package id="Microsoft.AspNet.WebApi" version="5.2.3" targetFramework="net452" />
<package id="Microsoft.AspNet.WebApi.Client" version="5.2.3" targetFramework="net452" />
<package id="Microsoft.AspNet.WebApi.Core" version="5.2.3" targetFramework="net452" />
<package id="Microsoft.AspNet.WebApi.WebHost" version="5.2.3" targetFramework="net452" />
<package id="Microsoft.CodeDom.Providers.DotNetCompilerPlatform" version="1.0.1" targetFramework="net452" />
<package id="Microsoft.Net.Compilers" version="1.3.2" targetFramework="net452" developmentDependency="true" />
<package id="Microsoft.OData.Core" version="6.15.0" targetFramework="net452" />
<package id="Microsoft.OData.Edm" version="6.15.0" targetFramework="net452" />
<package id="Microsoft.Spatial" version="6.15.0" targetFramework="net452" />
<package id="Newtonsoft.Json" version="9.0.1" targetFramework="net452" />
<package id="System.Spatial" version="5.7.0" targetFramework="net452" />
</packages>
Here's the WebApiConfig.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Http;
using System.Web.OData.Builder;
using System.Web.OData.Extensions;
using HealthcareWebApp;
namespace HealthcareWebApp
{
public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
// Web API configuration and services
// Web API routes
config.MapHttpAttributeRoutes();
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{id}",
defaults: new { id = RouteParameter.Optional }
);
//Custom code
ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet<PAYMENTS>("PAYMENTS"); //.EntityType.HasKey(p => p.PAYMENT_KEY);
builder.EntitySet<DATE_DIM>("DATE_DIM"); //.EntityType.HasKey(p => p.Year);
builder.EntitySet<PROVIDERS>("PROVIDERS"); //.EntityType.HasKey(p => p.Provider_Id);
config.MapODataServiceRoute("odata", "odata", builder.GetEdmModel());
}
}
}
PAYMENTSController.cs:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Web.ModelBinding;
using System.Web.OData;
using System.Web.OData.Query;
using System.Web.OData.Routing;
using HealthcareWebApp;
namespace HealthcareWebApp.Controllers
{
/*
The WebApiConfig class may require additional changes to add a route for this controller. Merge these statements into the Register method of the WebApiConfig class as applicable. Note that OData URLs are case sensitive.
using System.Web.OData.Builder;
using System.Web.OData.Extensions;
using HealthcareWebApp;
ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet<PAYMENTS>("PAYMENTS");
builder.EntitySet<DATE_DIM>("DATE_DIM");
builder.EntitySet<PROVIDERS>("PROVIDERS");
config.MapODataServiceRoute("odata", "odata", builder.GetEdmModel());
*/
public class PAYMENTSController : ODataController
{
private FlexIT_HealthcareEntities db = new FlexIT_HealthcareEntities();
// GET: odata/PAYMENTS
[EnableQuery]
public IQueryable<PAYMENTS> GetPAYMENTS()
{
return db.PAYMENTS;
}
// GET: odata/PAYMENTS(5)
[EnableQuery]
public SingleResult<PAYMENTS> GetPAYMENTS([FromODataUri] int key)
{
return SingleResult.Create(db.PAYMENTS.Where(pAYMENTS => pAYMENTS.PAYMENT_KEY == key));
}
// PUT: odata/PAYMENTS(5)
public IHttpActionResult Put([FromODataUri] int key, Delta<PAYMENTS> patch)
{
Validate(patch.GetEntity());
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
PAYMENTS pAYMENTS = db.PAYMENTS.Find(key);
if (pAYMENTS == null)
{
return NotFound();
}
patch.Put(pAYMENTS);
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
if (!PAYMENTSExists(key))
{
return NotFound();
}
else
{
throw;
}
}
return Updated(pAYMENTS);
}
// POST: odata/PAYMENTS
public IHttpActionResult Post(PAYMENTS pAYMENTS)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
db.PAYMENTS.Add(pAYMENTS);
db.SaveChanges();
return Created(pAYMENTS);
}
// PATCH: odata/PAYMENTS(5)
[AcceptVerbs("PATCH", "MERGE")]
public IHttpActionResult Patch([FromODataUri] int key, Delta<PAYMENTS> patch)
{
Validate(patch.GetEntity());
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
PAYMENTS pAYMENTS = db.PAYMENTS.Find(key);
if (pAYMENTS == null)
{
return NotFound();
}
patch.Patch(pAYMENTS);
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
if (!PAYMENTSExists(key))
{
return NotFound();
}
else
{
throw;
}
}
return Updated(pAYMENTS);
}
// DELETE: odata/PAYMENTS(5)
public IHttpActionResult Delete([FromODataUri] int key)
{
PAYMENTS pAYMENTS = db.PAYMENTS.Find(key);
if (pAYMENTS == null)
{
return NotFound();
}
db.PAYMENTS.Remove(pAYMENTS);
db.SaveChanges();
return StatusCode(HttpStatusCode.NoContent);
}
// GET: odata/PAYMENTS(5)/DATE_DIM
[EnableQuery]
public SingleResult<DATE_DIM> GetDATE_DIM([FromODataUri] int key)
{
return SingleResult.Create(db.PAYMENTS.Where(m => m.PAYMENT_KEY == key).Select(m => m.DATE_DIM));
}
// GET: odata/PAYMENTS(5)/PROVIDERS
[EnableQuery]
public SingleResult<PROVIDERS> GetPROVIDERS([FromODataUri] int key)
{
return SingleResult.Create(db.PAYMENTS.Where(m => m.PAYMENT_KEY == key).Select(m => m.PROVIDERS));
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
private bool PAYMENTSExists(int key)
{
return db.PAYMENTS.Count(e => e.PAYMENT_KEY == key) > 0;
}
}
}
Lastly, the PAYMENTS.cs model:
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace HealthcareWebApp
{
using System;
using System.Collections.Generic;
public partial class PAYMENTS
{
[System.ComponentModel.DataAnnotations.Key] //manually added by ataft
public int PAYMENT_KEY { get; set; }
public string DRG_Definition { get; set; }
public string Provider_Id { get; set; }
public string Hospital_Referral_Region_Description { get; set; }
public Nullable<decimal> Total_Discharges_ { get; set; }
public Nullable<decimal> Covered_Charges { get; set; }
public Nullable<decimal> Total_Payments { get; set; }
public Nullable<decimal> Medicare_Payments { get; set; }
public int Year { get; set; }
public virtual DATE_DIM DATE_DIM { get; set; }
public virtual PROVIDERS PROVIDERS { get; set; }
}
}
It's an issue about filter and groupby is filter can't apply to groupby or aggregated property and resolve in WebAPI/OData 5.9.1.
https://www.nuget.org/packages/Microsoft.AspNet.OData/5.9.1
And in your scenario, apply will always executed first and then filter get executed, so when $apply=groupby((Provider_Id),aggregate(Total_Payments with sum as Total_Payments)), the result won't contain DRG_Definition, so the filter failed, if you want to filter first, you should use filter in apply, like $apply=filter(Name eq 'Lowest')/groupby((Name))
FYI the spec http://docs.oasis-open.org/odata/odata-data-aggregation-ext/v4.0/odata-data-aggregation-ext-v4.0.html