Short tips to getting Excel working with ASP.Net Core OData service

I've been tinkering with an Asp.Net Core OData service designed to be consumed by the Excel client... There are a lot of details that will screw your brains if you don't get them right, and it doesn't help that ASP.Net is a fidgety beast in that bits move around in each version and there are more examples on the net that don't build than the ones that do. I could do a separate post for each of them since it's very hard to get relevant information, but for the time being I'll just list them here.

For one, older versions of Excel (pre-2016) have some OData support but it doesn't seem to be adequate, it's best to install the Power Query plug-in and use that for OData. In 2016 and later, be careful as there are two OData options - under Get External Data and under New Query... You want the second one.

If you need authentication, don't count on using OAuth (which was what I would have expected - OData, OAuth, right?). Excel supports basic, windows, web api (i.e. some kind of a security key) or organizational account (which I suppose is AD). My users are stored in my application database, so the only way was to use the basic authentication... Which is so deprecated that Asp.Net guys refuse to support it and I had to roll my own. (Not too difficult, there are examples on the net, but additional work nevertheless).

There's a ton of examples on how to implement a trivial Asp.Net OData service that returns data from a single Entity Framework-mapped table. I haven't found (m)any that show how to use more complex SQL queries. Because, if you need this data in Excel, you'd like to have complex query - or not? Apparently nobody thought about that. There's an open source component called DynamicODataToSql that understands the OData data-shaping commands and can convert them to SQL. Well - only if the SQL you start with contains a single table name... Uhm, at least it could be a view in the database, I guess EF can do that as well. But with a couple of modifications, this component can be persuaded to at least treat what you give it as a nested SQL query (turning it effectively into a table) and add its magic on top of that.

Also, Asp.Net doesn't know how to return appropriate errors through OData, it spits out an HTML error page whereas a Web API is supposed to return XML or JSON. So, more manual labour: solutions exist online on how to add a filter that does this, but it's not supported out of the box. Still, even with this, Excel seems to sometimes ignore some errors. Especially in the query editor window, the preview that it shows isn't necessarily the data that it pulled from the server at that precise moment (as in: you see your code throwing an exception on the server side but Excel pays no attention and still shows data). The best way to check if the service works seems to be to load the data into a sheet and refresh it from there.

And the final headbanger - for now, at least - was how to get Excel to do server-side data shaping. Because, for unknown reasons, it sometimes decides to load everything from the database and then filter data by itself... Which is insane. One important bit I found that makes or breaks this is the URL you give Excel to access the data. If you target your OData controller directly (e.g. http://localhost/odata/MyData), everything will seemingly work but the data will be filtered on the client. If the URL points to the base OData directory (e.g. http://localhost/odata), Excel's editor will add a Navigation step to the query to select the controller - and with this the server-side filtering will work. Now, I'm talking only about filtering as I'm not sure if Excel supports other OData stuff like grouping: filtering is fundamental and I'm OK with counting the rest - especially given all the problems listed above - as a bonus.