Excel powerquery error with $expand using sample

[danieltallentire]

When using excel to retrieve an $expand-ed dataset from the sample, an error occurs

Assemblies affected

tested using 7.4-beta source code

Reproduce steps

1. Start the OData sample project AspNetCore3xODataSample.Web
2. Open Excel
3. Go To Data -> Get Data -> From Other Sources -> OData Feed
4. Enter the URL to your application http://{host}/odata/Customers?$select=Name&$expand=Order

Expected result

The OData data should be shown in the preview window, showing the "name" and the full details of the linked order.

Actual result

An error is shown "The odata.context url 'http://{host}/odata/$metadata#Customers(Name,Order())' is invalid"

Additional detail

I'm unsure if the problem is within OData / WebAPI, or within powerquery. I've examined a number of services that do work with powerquery, for instance the devops https://analytics.dev.azure.com odata services, and these return the metadata context url differently.

The context url in the functioning cases would be written as 'http://{host}/odata/$metadata#Customers(Name,Order)'

I have been asked by the Microsoft PowerBI support team to report this here.

[danieltallentire]

This is still an issue with PowerBI 2.90.782.0 and Microsoft.AspNetCore.OData 7.5.6

If anyone else has the same problem, I wrote a horrendous workaround as a middleware:

`public class OdataRewriteExpandContext { private readonly RequestDelegate _next;

    public OdataRewriteExpandContext(RequestDelegate next)
    {
        _next = next;
    }

    public async Task InvokeAsync(HttpContext context)
    {
        if (!context.Request.QueryString.Value.Contains("$expand") || context.Request.Query.ContainsKey("download"))
        {
            await _next(context);
            return;

        }

        var responseBody = context.Response.Body;

        using (var newResponseBody = new MemoryStream())
        {
            context.Response.Body = newResponseBody;
            await _next(context);

            context.Response.Body = new MemoryStream();

            newResponseBody.Seek(0, SeekOrigin.Begin);
            context.Response.Body = responseBody;

            String json = new StreamReader(newResponseBody).ReadToEnd();

            // Replace all spaces between tags skipping PRE tags    
            json = json.Replace("Events()", "Events(*)");
            json = json.Replace("Surveys()", "Surveys(*)");
            json = json.Replace("Lines()", "Lines(*)");

            await context.Response.WriteAsync(json);
        }

    }
}`