LINQ's SelectMany Equivalent in OData

Question

I have an entity called AccountAction that has property called TransactionTypes that is an ICollection of TransactionType entities.

I return a list of all the TransactionTypes that are related to all the AccountActions in a method called GetTransactionTypes. I would like to apply query options to the returned list of TransactionTypes. However, so far I have hit a wall because all of the query options are applied to AccountActions.

Is there any way I can apply query options in the URL to the returned lists of TransactionTypes? In other words, is there a way I can do a SelectMany from the URL to get the TransactionTypes related to the AccountActions to move on to apply the query options to the found TransactionTypes?

Below is an extract of the code that I am using.

[Route(FullControllerPath + "/TransactionTypes")]
public IHttpActionResult GetTransactionTypes(ODataQueryOptions<AccountAction> queryOptions, bool addCols, int? skip, int? take)
{
    using (AccountActionManagement _accountActionManage = new AccountActionManagement(this.GenerateInformation()))
    {
        _accountActionManage.SetTraslationList("DATASTRUCT-CONFIG-ACCOUNTACTIONTRANSACTIONTYPE", language);

        // Query composition
        IQueryable<TransactionType> query = queryOptions.ApplyTo(_accountActionManage.GetTypeAsQueryable<AccountAction>())
                                                        .OfType<AccountAction>()
                                                        .SelectMany(aa => aa.TransactionTypes)
                                                        .Include(tt => tt.AccountActionForDefaultTransactionType.DefaultTransactionType);

        var queryData = query.Select(tt => new
                        {
                            Id = tt.Id,
                            Name = tt.Name,
                            Operation = tt.Operation,
                            Type = tt.Type,
                            Default = tt.AccountActionForDefaultTransactionType != null && 
                                      tt.AccountActionForDefaultTransactionType.DefaultTransactionType.Id == tt.Id,
                            Version = tt.AccountActionForDefaultTransactionType.Version
                        });
        // Get count
        int totalRows = queryData.Count();

        // Get biggest version in query
        var maxVersion = queryData.Max(i => i.Version);

        // Get data from database
        var queryResult = queryOptions.OrderBy == null 
                              ? queryData.OrderBy(i => i.Id)
                                         .Skip(skip ?? 0)
                                         .Take(take ?? totalRows)
                                         .ToList() 
                              : queryData.Skip(skip ?? 0)
                                         .Take(take ?? totalRows)
                                         .ToList();
...}}

As seen in the diagram below, AccountAction has a many-to-many relationship to TransactionType. AccountAction has the first role and TransactionType has the second role.

Answer 1

I found a workaround for this issue. I realized that I was not passing the right type to the ApplyTo method. Now, I apply the query options to an IQueryable of TransactionTypes instead of applying the query options to an IQueryable of AccountActions.

Below is the code with the described modification. Also, a diffchecker of the change I made is here.

[Route(FullControllerPath + "/TransactionTypes")]
public IHttpActionResult GetTransactionTypes(ODataQueryOptions<AccountAction> queryOptions, bool addCols, int? skip, int? take)
{
    using (AccountActionManagement _accountActionManage = new AccountActionManagement(this.GenerateInformation()))
    {
        _accountActionManage.SetTraslationList("DATASTRUCT-CONFIG-ACCOUNTACTIONTRANSACTIONTYPE", language);

        // Query composition
        IQueryable<TransactionType> query = queryOptions.ApplyTo(_accountActionManage.GetTypeAsQueryable<AccountAction()
                               .SelectMany(aa => aa.TransactionTypes)
                               .Include(aa =>      aa.AccountActionForDefaultTransactionType.DefaultTransactionType))
                               .OfType<TransactionType>();


        var queryData = query.Select(tt => new
                        {
                            Id = tt.Id,
                            Name = tt.Name,
                            Operation = tt.Operation,
                            Type = tt.Type,
                            Default = tt.AccountActionForDefaultTransactionType != null && 
                                      tt.AccountActionForDefaultTransactionType.DefaultTransactionType.Id == tt.Id,
                            Version = tt.AccountActionForDefaultTransactionType.Version
                        });
        // Get count
        int totalRows = queryData.Count();

        // Get biggest version in query
        var maxVersion = queryData.Max(i => i.Version);

        // Get data from database
        var queryResult = queryOptions.OrderBy == null 
                              ? queryData.OrderBy(i => i.Id)
                                         .Skip(skip ?? 0)
                                         .Take(take ?? totalRows)
                                         .ToList() 
                              : queryData.Skip(skip ?? 0)
                                         .Take(take ?? totalRows)
                                         .ToList();
...}}