Simplifying EDM with OData

Avatar

Hassan

Summary

In a previous article, I talked about how you can leverage the power of OData with your existing ASP.NET Core API to bring in more features to your API consumers.

But there are different ways you could enable OData on your existing API that are just as simple but offers more powerful features than overriding your existing routes and enabling dependency injection.

For instance, if you’ve tried to perform a count operation using our previous method you will notice it doesn’t really return or perform anything, the same thing goes with many other features that we will talk about extensively in future articles.

In this article, however, I’m going to show you how you can enable OData on your existing ASP.NET Core API using EDM.

 

What is EDM?

EDM is short for Entity Data Model, it plays the role of a mapper between whatever data source and format you have and the OData engine.

In other words, whether your source of data is SQL, Cosmos DB or just plain text files, and whether your format is XML, Json or raw text or any other type out there, What the entity data model does is to turn that raw data into entities that allow functionality like count, select, filter and expand to be performed seamlessly through your API.

 

Setting Things up

Let’s set our existing API up with OData using EDM.

First and foremost, add in Microsoft.AspNetCore.OData nuget package to your ASP.NET Core project.

Once the nuget package is installed, let’s setup the configuration to utilize that package.

In your Startup.cs file, in your ConfigureServices function, add in the following line:

 

Important Note: This will work with ASP.NET Core 2.1, if you are trying to set this up with ASP.NET Core 2.2, then you must add another line of code as follows:

OData doesn’t yet support .NET Core 3.0 – at the time of this article, .NET Core 3.0 is still in preview, OData support will extend to 3.0 once it’s production ready.

Once that part is done, let’s build a private method to do a handshake between your existing data models (Students in this case) and EDM, as follows:

The student model we are using here is the same model we used in our previous article, as a reminder here’s how the model looks like:

Now that we have created our EDM method, now let’s do one last configuration change in the Configure method in our Startup.cs file as follows:

 

Just like our last article, we enabled the functionality we needed such as select, filter and order by then we used the MapODataServiceRoute method to utilize our EDM method.

We used “api” instead of “odata” as our first and second parameters as a route name and a route prefix to continue to support our existing APIs endpoints, but there’s a catch to that.

Your contract in this case will change, if your API returns a list of students like this:

 

With the EDM method, your contract will change a bit, your response will have some helpful metadata that we are going to talk about, and it will look like this:

That extra metadata is going to help us perform more operations than the old method.

In that case if you have existing consumers for your API, I recommend introducing a new endpoint, version or informing them to change their contracts, otherwise this will be a breaking change.

The other option is to change your route name and route prefix parameters to say “odata” instead, which is the standard way to implement OData.

The last thing we need to do to make this work for us is removing the notations on top of your existing API controller class, in our case we will remove these two lines:

And don’t forget to add the enabling querying annotation on top of your API method:

 

Putting OData into Action

Once that’s done, now you can try to perform higher operations using OData like Count for instance, you can call your endpoint with /api/students?$count=true and you should get:

 

As you can see here, you have a new property @odata.count that shows you the count of the items in your list.

 

Final Notes

Now that you’ve learned about the simplest way (8 lines of code) to create a handshake between ASP.NET Core, OData and EDM here’s few notes:

  1. EDM doesn’t have any dependency on the Entity Framework, in fact the whole purpose of creating an EDM is to link whatever data you have in any format it may be to the OData engine and serialize the results through an API endpoint.
  2. EDM can only be useful if you need some specific OData features such as count and nextlink and so many other features that we will explore in future articles.
  3. There’s more to learn about EDM, I encourage you to check all about EDM in this extensive, comprehensive documentation.
  4. OData is an open-source project, I encourage you as you benefit from it’s amazing features to contribute to the project, suggest new features and participate with documentation and your experiences to keep the community active and useful for everyone.
  5. You can clone the project I built and try things out from this github repo.

 

 

Avatar
Hassan Habib

Sr. Software Engineer, Microsoft

Follow Hassan   

21 comments

Comments are closed.

  • Guillermo Acosta
    Guillermo Acosta

    Hi, Hassan! I follow step for step this tutorial and too your the “Supercharging your Web APIs with OData and ASP.NET Core” video. In both cases, the API requests respond correctly, and with OData only correctly responds Select and OrderBy, but not Expand. Can you give me an idea, according to your experience, what may be failing or missing? I use Visual Studio 2019 .Net Core 2.1 (also probe with .Net 2.1) and SQL Server 2017. Thanks in advance.

  • Avatar
    Peter Hamilton

    Hi Hassan,
    I have been trying to get this to work correctly within an API project I am working on.  The api is currently working but I don’t have the Edm stuff wired in (hence I am reading this article). 
    I have followed the steps for Startup.cs:
    services.AddOData();services.AddMvcCore(action => action.EnableEndpointRouting = false);
    app.UseMvc(routeBuilder =>{routeBuilder.Select().Filter().OrderBy().Expand().Count().MaxTop(10);routeBuilder.MapODataServiceRoute(“api”, “api”, GetEdmModel());});
    I have removed the routing attributes from my controller and ensured the [EnableQuery()] attribute is added:
    //[Route(“api/[controller]”)]//[ApiController][EnableQuery()]//…further code omitted
    I have the GetEdmModel() class configured.
    But, when I try to run, there are no compile errors, but endpoints are giving me a nastygram:
    This localhost page can’t be foundNo webpage was found for the web address: https://localhost:5001/api/valuesHTTP ERROR 404
    This all works fine without the changes made for this article, and I can’t find a sample like you had for the first article with the items mentioned here.
    I am a bit stumped.
    Thank you for the great articles!

  • Chris Darrigo
    Chris Darrigo

    Sorry for the poor formatting. I’ve try to reformat this messatge multiple times, but the blog software keeps removing all the formatting.  . ________. .
    You can find a better formatted version here -> https://pastebin.com/dHrTLi35
    _______
    Thanks for the article! . I seem to be having mixed results trying to get my code working. . I’m running .net core 2.1 and Microsoft.AspNetCore.OData Version=7.1.0.. My startup extensions:. // invoked from startup, ConfigureServices()public static IServiceCollection AddODataSupport(this IServiceCollection services){services.AddOData();//Workaround: https://github.com/OData/WebApi/issues/1177services.AddMvcCore(options =>{    foreach (var outputFormatter in options.OutputFormatters.OfType<ODataOutputFormatter>()        .Where(_ => _.SupportedMediaTypes.Count == 0))    {        outputFormatter.SupportedMediaTypes.Add(new MediaTypeHeaderValue(“application/prs.odatatestxx-odata”));    }     foreach (var inputFormatter in options.InputFormatters.OfType<ODataInputFormatter>()        .Where(_ => _.SupportedMediaTypes.Count == 0))    {        inputFormatter.SupportedMediaTypes.Add(new MediaTypeHeaderValue(“application/prs.odatatestxx-odata”));    }   });   return services;} // invoked from startup, app.UseMVC() public static void ConfigureODataRouting(this IRouteBuilder routeBuilder){    routeBuilder.EnableDependencyInjection();   routeBuilder.Select().Filter().OrderBy().Expand().Count().MaxTop(100);routeBuilder.MapODataServiceRoute(“odata”, “odata”, GetEdmModel());} private static IEdmModel GetEdmModel(){    var obuilder = new ODataConventionModelBuilder();   obuilder.EntitySet<TransactionEntity>(“Transactions”);   return obuilder.GetEdmModel();} Here’s my controller: public class ODataController : ControllerBase{    private readonly ITmDataContext dataContext;     public ODataController(ITmDataContext dataContext)    {        this.dataContext = dataContext;    }     [Route(“odata/transactions”)]    [HttpGet]    [EnableQuery]    public ActionResult<IEnumerable<object>> GetTransactions()    {        return dataContext.Transactions;    }} . I can hit the $metadata endpoint and get my edm xml.  <?xml version=”1.0″ encoding=”utf-8″?><edmx:Edmx Version=”4.0″ xmlns:edmx=”http://docs.oasis-open.org/odata/ns/edmx”><edmx:DataServices><Schema Namespace=”TMS.DataManagement.Service.Data.Entities” xmlns=”http://docs.oasis-open.org/odata/ns/edm”><EntityType Name=”TransactionEntity”><Key><PropertyRef Name=”Id” /></Key><Property Name=”Id” Type=”Edm.Int32″ Nullable=”false” /> … . and I can hit the odata/Transactions endpoint. . HTTP GET https://localhost:5001/odata/Transactions returns data like [{“id”: 2860,”school_id”: 3,”type_code”: 1,”post_status_code”: 0,”post_date”: “2019-07-01T00:00:00″,”deleted”: true,”description”: “fee name”,”comments”: “”,”insert_date”: “2019-07-01T11:35:48.2343931″,”modified_date”: “2019-07-02T17:24:35.5232986″,”last_modify_user_id”: 100169},… etc]. BUT: .   1) my results never have the edm metadata (payload is missing the “@odata.context” node) .   2) some operations like $expand and $filter work, but others like $count=true don’t include the count node.  .   3) If I try to consume my odata feed in a client application (like excel), I get an error               ‘StartArray’ node was found when reading from the JSON reader. A ‘StartObject’ node was expected.  . If I remove the [Route] attribute on my endpoint as you suggested, I can’t hit the odata/transactions endpoint. I get a 404.   . All of the above makes me think I haven’t implemented the OData routing correctly, but I’m not sure where I’ve messed up.  . Any thoughts?

  • Avatar
    David Smith

    Hi, Hassan. Thanks for the great mini-series on OData and ASP.NET Core. Does OData with EF Core support $apply=aggregate()?
    I ask because this tutorial works fine when I test with https://localhost:5001/api/students?$count=true&$apply=aggregate(Score with sum as Total). When I hook up the same code to the database from Supercharging your ASP.NET Core API with OData, I get the following error:

    System.ArgumentException: Value does not fall within the expected range.at System.SharedTypeExtensions.GetSequenceType(Type type)at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitMainFromClause(MainFromClause fromClause, QueryModel queryModel)at Remotion.Linq.Clauses.MainFromClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel)at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitSubQueryModel(QueryModel queryModel)at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.LiftSubQuery(IQuerySource querySource, SubQueryExpression subQueryExpression)at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.CompileMainFromClauseExpression(MainFromClause mainFromClause, QueryModel queryModel)at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitMainFromClause(MainFromClause fromClause, QueryModel queryModel)

    Is there a work-around?

  • Avatar
    Luan Le Thanh

    Thanks for greate article but how can we solve OData N + 1 query problems with EntifyFrameWorkCore ?

      • Avatar
        Luan Le Thanh

        Hi Hassan, thanks for reply. I using EntityFrameWorkCore with Odata.
        This is my code:
        [DefaultProperties(@"APK,DivisionID,ShopID")][EnableQuery]public IEnumerable<POST0016Entity> Get(){   return _context.Orders.Include(e => e.Items).AsNoTracking();}

        and query http: http://localhost:60365/odata/Orders?$count=true&$expand=Items&$select=divisionID,APK.
        The problem is if i let OData query then the EntityFrameWorkCore will generate 22 query statement like this:
        exec sp_executesql N'SELECT [p].[APK], [p].[DivisionID], [p].[APKDInherited], [p].[APKMInherited], [p].[APKMaster], [p].[APPDetailSuggestID], [p].[APPOrderID], [p].[ActualQuantity], [p].[Amount], [p].[Ana01ID], [p].[Ana02ID], [p].[Ana03ID], [p].[Ana04ID], [p].[Ana05ID], [p].[Ana06ID], [p].[Ana07ID], [p].[Ana08ID], [p].[Ana09ID], [p].[Ana10ID], [p].[BeforeVATDiscountAmount], [p].[BeforeVATUnitPrice], [p].[CA], [p].[CAAmount], [p].[DeleteFlg], [p].[DiscountAllocation], [p].[DiscountAmount], [p].[DiscountOneUnitOfProduct], [p].[DiscountRate], [p].[EVoucherNo], [p].[ID1], [p].[ID2], [p].[Imei01], [p].[Imei02], [p].[InventoryAmount], [p].[InventoryID], [p].[InventoryName], [p].[InvoicePromotionID], [p].[IsBooking], [p].[IsDisplay], [p].[IsExportNow], [p].[IsFreeGift], [p].[IsInstallmentPrice], [p].[IsInvoicePromotionID], [p].[IsKindVoucherID], [p].[IsPackage], [p].[IsPromotePriceTable], [p].[IsPromotion], [p].[IsTable], [p].[IsTaxIncluded], [p].[IsWarehouseGeneral], [p].[IsWholesale], [p].[MarkQuantity], [p].[Notes], [p].[OrderNo], [p].[OrderPackage], [p].[PackageID], [p].[PackagePriceID], [p].[PriceTable], [p].[ProcessingNumberPrint], [p].[ProcessingPrintDate], [p].[PromoteChangeUnitPrice], [p].[PromoteID], [p].[PromotePriceTableID], [p].[RedureAllocation], [p].[RedureOneUnitOfProduct], [p].[SerialNo], [p].[ShopID], [p].[TaxAmount], [p].[UnitID], [p].[UnitName], [p].[UnitPrice], [p].[VATGroupID], [p].[VATPercent], [p].[WareHouseID], [p].[WareHouseName], [p].[WarrantyCard]FROM [POST00161] AS [p]WHERE @_outer_APK = [p].[APKMaster]',N'@_outer_APK uniqueidentifier',@_outer_APK='9F29DCAE-02A7-4A33-8A56-94F410C01DC4'

        Do you have any way to solve it?

  • Avatar
    codetrooper

    I don’t know if anyone else is experiencing this but I added the code to my project and now my swagger no longer works. It seems like swagger doesn’t work well with Odata and regular Api controllers?

  • Avatar
    David Smith

    Sorry for the double-post. I will simplify: Does OData with EF Core support $apply=aggregate()?