Avoiding a cartesian product in Microsoft OData when expanding multiple navigation properties

user_634914 0
Viewed 542

This is not a request for help. It started out as one as I had been completely unable to find anything about this specific problem, but as I was writing this out I eventually figured out a way to do this, and since I already wrote most of it I decided I'd leave it here in the chance it helps anyone else.

The problem

This is a web application in .NET Core 3.1 using Microsoft OData 7.8.1 and Entity Framework Core 5 with SQL Server. Making an OData query that expands two different navigation properties (both of which are many-to-many relationships) results in an Entity Framework-generated SQL statement that creates a dataset that has tens of thousands of rows thanks to a cartesian product.

Attempts to solve it

In my service layer, I was able to use AsSplitQuery() to solve this problem, so I tried to use split queries globally, hoping that OData would be able to take advantage of it, but that was no good (error message: The query has been configured to use 'QuerySplittingBehavior.SplitQuery' and contains a collection in the 'Select' call, which could not be split into separate query). According to issue 21234 the feature won't be available until EF Core 6.

So then I found this blog which shows how to create an ActionFilter to change the OData query options (written for .NET Framework, but I could port it). My plan was to remove one of the expanded properties before the request and then load those entities manually afterwards (taken from here), like this:

public class GroupsclusiveEnableQueryAttribute : EnableQueryAttribute
{
    public override void OnActionExecuting(ActionExecutingContext context)
    {
        bool includeUsers = false;
        bool includeRoles = false;

        ODataQueryOptions options = (ODataQueryOptions)context.ActionArguments[ODataQueryOptionsUtilities.QUERY_OPTIONS_ACTION_ARGS_KEY];
        IEnumerable<string> expand = options.GetExpandProperties();
        if (expand != null && expand.Count() > 0)
        {
            includeUsers = expand.Any(s => s.CaseInsensitiveContains(nameof(Group.UsersInGroup)));
            includeRoles = expand.Any(s => s.CaseInsensitiveContains(nameof(Group.Roles)));

            // when both users and roles are requested, the resulting EntityFramework query can generate a huge result set due to a cartesian product
            // so we are going to intercept it and manually load the roles at the end
            if (includeUsers && includeRoles)
            {
                options = ODataQueryOptionsUtilities.RemoveExpandProperty(options, nameof(Group.Roles));
                context.ActionArguments[ODataQueryOptionsUtilities.QUERY_OPTIONS_ACTION_ARGS_KEY] = options;
            }
        }

        // store the flags in HttpContext.Items for later retrieval (can't use private variables because this class is a singleton)
        context.HttpContext.Items.Add(nameof(includeUsers), includeUsers);
        context.HttpContext.Items.Add(nameof(includeRoles), includeRoles);
        base.OnActionExecuting(context);
    }

    public override void OnActionExecuted(ActionExecutedContext context)
    {
        base.OnActionExecuted(context);

        // get the flags
        bool includeUsers = false;
        bool includeRoles = false;
        object includeUsersBox, includeRolesBox;
        if (context.HttpContext.Items.TryGetValue(nameof(includeUsers), out includeUsersBox))
            includeUsers = (bool)includeUsersBox;
        if (context.HttpContext.Items.TryGetValue(nameof(includeRoles), out includeRolesBox))
            includeRoles = (bool)includeRolesBox;

        if (includeUsers && includeRoles)
        {
            object resultValue = ((ObjectResult)context.Result).Value;
            IQueryable<GroupViewModel> groups = resultValue as IQueryable<GroupViewModel>;
            if (groups == null && resultValue is IQueryable)
            {
                // THIS LINE IS THE PROBLEM -- this is the wrong type!
                groups = ((IQueryable)resultValue).Cast<GroupViewModel>();
            }
            if (groups != null)
            {
                IEmployeeDTOContext db = context.HttpContext.RequestServices.GetService<IEmployeeDTOContext>();
                List<GroupViewModel> ret = groups.ToList();
                var roles = groups.Include(x => x.Roles.Where(rm => rm.Role.Active)).ThenInclude(rm => rm.Role);
                roles.Load();
                ((ObjectResult)context.Result).Value = ret;
            }
        }

    }

The problem is that the result, while it is IQueryable, it's not an IQueryable<GroupViewModel>. The real type is SelectExpandBinder.SelectAllAndExpand<GroupViewModel>, a Microsoft internal class, so when I call .ToList(), I get: No coercion operator is defined between types 'Microsoft.AspNet.OData.Query.Expressions.SelectExpandBinder+SelectAllAndExpand`1[MyNamespace.GroupViewModel]' and 'MyNamespace.GroupViewModel'.

Next I thought I would try and the LINQ expression out of the IQueryable and execute it manually, but that results in the same error.

Then I tried eager-loading the navigation properties on their own when the flag is set, hoping that EF would recognize that they already existed in the object graph and therefore not need to load them. That didn't work because OData is using projection to get those objects. This is what the expression tree generated by OData looks like:

[Microsoft.EntityFrameworkCore.Query.QueryRootExpression].Where($it => $it.GroupId == TypedProperty).Select(
    $it => new SelectExpandBinder.SelectAllAndExpand<GroupViewModel>
    {
        ModelID = TypedProperty,
        Instance = $it,
        UseInstanceForProperties = true,
        Container = new PropertyContainer.NamedPropertyWithNext0<IEnumerable<SelectExpandBinder.SelectAll<GroupMembershipViewModel>>>
        {
            Name = "UsersInGroup",
            Value = $it.UsersInGroup.Select(
                $it => new SelectExpandBinder.SelectAll<GroupMembershipViewModel>
                {
                    ModelID = TypedProperty,
                    Instance = $it,
                    UseInstanceForProperties = true
                }),
            Next0 = new PropertyContainer.NamedProperty<IEnumerable<SelectExpandBinder.SelectAll<GroupRoleMembershipViewModel>>>
            {
                Name = "Roles",
                Value = $it.Roles.Select(
                    $it => new SelectExpandBinder.SelectAll<GroupRoleMembershipViewModel>
                    {
                        ModelID = TypedProperty,
                        Instance = $it,
                        UseInstanceForProperties = true
                    })
            }
        }
    })

The big problem is that because the IQueryable will resolve to Microsoft internal classes, there was no way for me to manipulate the objects even if I loaded the roles myself ... or is there?

entity-framework-core asp.net-core odata c#
1 Answers

So the key question became, how can I get the objects into a form where I can manually add the additional expanded entities? I could convert the MSFT class into a IDictionary<string, object> but I still can't cast object to SelectExpandBinder.SelectAll<>.

Newtonsoft to the rescue!

So what I did was use Newtonsoft.Json to convert the MSFT class into a JArray. That allows me to manipulate the individual elements in the collection and then convert them back into the GroupViewModel objects. The important part is that at the end we have to convert the collection back into IQueryable<GroupViewModel> otherwise the OData model formatter is going to complain, which is why I didn't convert them to ExpandoObject. It's true we could just replace the result with an OkObjectResult but then you won't get the OData metadata like item count (when $count=true is specified).

    public override void OnActionExecuted(ActionExecutedContext context)
    {
        base.OnActionExecuted(context);

        // get the flags
        bool includeUsers = false;
        bool includeRoles = false;
        object includeUsersBox, includeRolesBox;
        if (context.HttpContext.Items.TryGetValue(nameof(includeUsers), out includeUsersBox))
            includeUsers = (bool)includeUsersBox;
        if (context.HttpContext.Items.TryGetValue(nameof(includeRoles), out includeRolesBox))
            includeRoles = (bool)includeRolesBox;

        if (includeUsers && includeRoles)
        {
            ObjectResult result = (ObjectResult)context.Result;
            // the type of the value is an internal Microsoft class so there's nothing we can do with it, but we can create a dynamic object using Newtonsoft.Json
            object resultValue = result.Value;
            JArray jResult = JArray.FromObject(resultValue);

            // get the query options so we can get the filter (if any)
            ODataQueryOptions options = (ODataQueryOptions)context.HttpContext.Items[ODataQueryOptionsUtilities.QUERY_OPTIONS_ACTION_ARGS_KEY];
            Expression<Func<GroupViewModel, bool>> filter = options.Filter?.ToExpression<GroupViewModel>();

            // put the original options back in so that the OData media formatter will process the $expand as requested by the caller
            context.HttpContext.Request.ODataFeature().SelectExpandClause = options.SelectExpand.SelectExpandClause;

            // apply the filter so that we don't need to load the roles for every single group
            IEmployeeDTOContext db = context.HttpContext.RequestServices.GetService<IEmployeeDTOContext>();
            IQueryable<GroupViewModel> groups = db.Groups.AsQueryable();
            if (filter != null)
                groups = groups.Where(filter);
            List<GroupViewModel> filteredGroups = groups.ToList();

            // explicitly load the role memberships now
            var roles = groups.Include(x => x.Roles.Where(rm => rm.Role.Active)).ThenInclude(rm => rm.Role).ThenInclude(r => r.Application);
            roles.Load();

            JsonSerializer serializer = new JsonSerializer();
            serializer.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;

            // loop thru the JSON elements and manually add the roles
            foreach (JObject jResultObj in jResult)
            {
                // get the group ID and find its roles
                int groupId = jResultObj.Value<int>(nameof(GroupViewModel.GroupId));
                GroupViewModel filteredGroup = filteredGroups.SingleOrDefault(x => x.GroupId == groupId);
                List<GroupRoleMembershipViewModel> retGroupRoles = filteredGroup.Roles;
                if (retGroupRoles == null)
                    continue;

                // EF will automatically link up entities when they are loaded in the object graph, whether we want them or not
                // so we want to drop the Group from GroupRoleMembership -- better to do it from the JArray because if you mess with
                // the entities you can have unforseen effects
                JArray jRetMemberships = JArray.FromObject(retGroupRoles, serializer);
                foreach (JObject jRetMembership in jRetMemberships)
                {
                    jRetMembership.Remove(nameof(GroupRoleMembershipViewModel.Group));
                }
                jResultObj.Add(nameof(GroupViewModel.Roles), jRetMemberships);
            }

            // reassign this back to the result so tha the OData media formatter can add the metadata
            groups = jResult.ToObject<List<GroupViewModel>>().AsQueryable(); // it's expecting an IQueryable
            result.Value = groups;
        }
    }
user_6349140
Related