asp.net-web-api2 - 使用带有 groupby 的过滤器时出现 OData v4 错误

Question

将 $filter 与 $apply=groupby 一起使用时出现错误。它似乎仅当过滤字段不在 groupby 表达式中时才会发生。这是错误消息:未为类型“DynamicTypeWrapper”定义实例属性“DRG_Definition”

这工作正常: http://localhost:9810/odata/PAYMENTS ?$apply=groupby((Provider_Id,DRG_Definition),aggregate(Total_Payments，总和为 Total_Payments))&$filter=(DRG_Definition eq '069 - 短暂性缺血')

这会引发错误(唯一的区别是 groupby 中没有 DRG_Definition 字段): http://localhost:9810/odata/PAYMENTS ?$apply=groupby((Provider_Id),aggregate(Total_Payments，总和为 Total_Payments))&$filter=(DRG_Definition eq '069 - 短暂性缺血')

更新了我的包和代码示例如下:

<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>

这是 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;
        }
    }
}

最后，PAYMENTS.cs 模型:

//------------------------------------------------------------------------------
// <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; }
    }
}

Answer 1

这是一个关于过滤器和groupby的问题，过滤器无法应用于groupby或聚合属性并在WebAPI/OData 5.9.1中解决。

https://www.nuget.org/packages/Microsoft.AspNet.OData/5.9.1

在您的场景中，apply 将始终先执行，然后执行过滤器，因此当 $apply=groupby((Provider_Id),aggregate(Total_Payments with sum as Total_Payments)) 时，结果获胜不包含DRG_Definition，所以过滤失败，如果要先过滤，应该在apply中使用filter，如$apply=filter(Name eq 'Lowest')/groupby((名称))

仅供引用，规范 http://docs.oasis-open.org/odata/odata-data-aggregation-ext/v4.0/odata-data-aggregation-ext-v4.0.html