CODEWITHSUNDEEP
c#graphqlhotchocolate
Willy K.
Willy K.

Reputation: 432

Unable to use filtering on custom fields in Hot Chocolate

I am still (or again) on my student project and one part of the project is the ability to send and view (of course) messages between users, and we use HotChocalte (.Net 5.0) for queries (read) and mutations (send). Data are usually obtained from a MySQL database. We also applied [UseFiltering] and [UseSorting] to queries and that works find with the members of the DB model.

Along others, we added a custom field containing the number of messages within a "thread" (we called that a conversation), and it tells the correct number of queries, but it seems to be impossible to filter or sort by this special fiels - the message "The specified input object field '...' does not exist.".

I have no more ideas what can I do. Do you have any suggestions? Hope following code is good enough to understand what I'm doing here:

C# implementation (backend):

public class ConversationType : ObjectType<Model.Conversation>
    protected override void Configure(IObjectTypeDescriptor<Model.Conversation> descriptor)
    {
        // [UseFiltering]   // This doesn't help
        // [UseSorting]     // This doesn't help
        descriptor.Field("messageCount")
                  .UseDbContext<ReviverDbContext>()
                  .Type<IntType>()
                  .ResolveWith<ConversationResolvers>(
                        product => product.GetMessageCount(default, default)
                   )
                  // .UseFiltering();    // This doesn't help

        // ...

        private class ConversationResolvers
        {
            /**
             * @brief   Get the number of message within a conversation
             */
            public async Task<int>
                         GetMessageCount([Parent] Model.Conversation conversation,
                                         [ScopedService] OurDbContext dbContext)
            {
                return await Task.FromResult(dbContext.Messages
                    .Where(message => message.Conversation == conversation)
                    .Count()
                );
            }  
  
            // ...
        }
    }
}

HotChocolate QueryType in C# (backend too):

[ExtendObjectType(OperationTypeNames.Query)]
public class ConversationQueries
{
    [UseOurDbContext]
    [UsePaging(IncludeTotalCount = true)]
    [UseFiltering]
    [UseSorting]
    public async Task<IEnumerable<Model.Conversation>>
                 GetConversationsAsync([ScopedService] OurDbContext dbContext)
    {
        return dbContext.Conversations.ToListAsync();
    }
}

Example of query that does not work:

query {
  conversations
 (
    where: { 
      messageCount: {neq : 0}   <- "The specified input object field does not exist"
    }
  )
  nodes {
    messageCount
    ...
  }
}

Thanks for any advise.

Upvotes: 0

Views: 3182

Answers (1)

Jan &#39;splite&#39; K.
Jan &#39;splite&#39; K.

Reputation: 2114

TLDR: No, its not possible. And for good reasons.

Please note, that when you are configure() in Hot Chocolate, you are really setting a pipeline. Every piece (middleware) have input, process and is returning output. Thats why order of middleware really matter. This is important for further understanding.

[UseFiltering]

There is no magic in [UseFiltering] - it is just middleware which will generate gql parameter object translateable to Linq Expression and at execution time, it will take this parameter, make Linq Expression from it and call return pipeInput.Where(linq_expression_i_just_generated_from_useFiltering_parameter_object)

So, when your input is EF DbSet<Model.Conversation>, then it simply call conversationDbSet.Where(expression). Entity framework then take this expression and when someone read results, it translate it to SQL select and fetch results from server.

But your expression is not translatable to sql - it contains field unknown to sql server, hence the error.

This is the reason why it work, when you call .ToList() -- it fetched whole table from sql server and do everything locally, which is not good (maybe its doable right now, but this will bite you later)

Why this is actually good behaviour:

Ok, imagine yourself few years in future where your Conversation table have 100.000.000 rows. Your users ara really chatty or your app is smashing success, congratulations :)

Imagine you have (privacy nightmare, but this is only example) api endpoint returning all conversations. And because there is so many conversation, you did what any sane guy/girl do: you added filtering and paging. Lets say 100 items per page.

Now, you have this messageCount field, which is executed locally.

One of your user have no idea what he is doing and filter "where messageCount = -1" (or 123 or anything which have 0 result).

Now what... Your pipeline do select top 100 (paging) but when it try to evaluate filter locally, it found out that there is not a single result. And this is where problems start: what should it do? Fetch another 100 rows? And another, and another and (100.000.000 / 100 = 1.000.000x select query) another, until it iterate whole table only to find there is no result? Or, second options, when it found out that part of your Where must be evaluated locally, do paging locally too and fetch 100.000.000 items in one go?

Both cases are really bad for performance and RAM. You just DDoS yourself.

By the way, option B is what EF (before-core, no idea how it is called... EF Classic? :D ) did. And it was a source of hard to find bugs, when in one moment query take few ms but then it take minutes. Good folks from EF team dropped this in first version of EFCore.

What to do instead...

One, AFAIK future proof (but it take some work), solution for this is using DB Views. Actually, in this case it is pretty easy. Just create view like this:

create view ConversationDto
as 
select Conversation.*,
       isnull(c.[count], 0) as MessageCount
  from Conversation
  left join ( 
    select ConversationId,
           count(Id)
      from Messages
      group by ConversationId 
  ) as c on c.ConversationId = Conversation.Id

(This is mssql query, no idea which db you are using. As a small bonus, you can optimalise hell out of your view (add custom index(ex), better joins, etc))

Then create new EF Entity:

public class ConversationDto
 : Conversation 
{
  public int MessageCount { get; set; }
}

public class ConversationDtoMap 
 : IEntityTypeConfiguration<ConversationDto>
{
  public void Configure(EntityTypeBuilder<SubjectOverviewDto> builder)
  {
    builder.ToView("ConversationDto"); // ToView! not ToTable!
  }
}

And now you are pretty much ready to go.

Just throw out your custom resolver (you moved your work to db, which is (well... should be. MySql, I am looking at you :-| ) great in this kind of work - working with data sets.) and change (query) type to ConversationDtoType.

As side effect, your ConversationDto (gql query model) is no longer same as your input model (gql mutation model - but your (domain?) model Conversation still is), but thats ok - you dont want to let users set messageCount anyway.

What to do instead #2

Your second option is to use ComputedColumns. I am not sure if its doable in your case, not a big fan of ComputedColumns, but when your custom resolver do something stupid like var p = ctx.Parent<Foo>(); return p.x + p.y * 42; then this should be fine.

Again, you moved work to database and there is no need for custom resolver and you can throw it out.

What to do instead #3

Also, calling .ToList()/.ToArray() before returning your DbSet<> at the beginning of your pipe and simply emberacing that "this will fetch, materialize and filter whole table" is another possible solution.

But be aware, that this can come back later and bite you really hard, especially if your Conversation is not some sort of list and there is chance that this table will grow big. (I have a feeling that Conversation is exactly that).

Also, because your implementation is another query to database, be aware that you just created 1 + n problem.

Do it at your own risk.

Impossible cases:

Of course, there can be resolver which do things that are impossible to do in sql (for example, sending http request to REST api on another server). Then you are out of luck for reason I showed you in example "Why this is actually good behaviour".

AFAIK you have two possible solutions: You can reimplement UseFiltering (I did that, its not THAT hard, but my case was relatively simple and HC is open source, so you have great starting point...) or you can just add custom argument + custom middleware when configuring your endpoint and implement (pre) filtering yourself.

Foot note:

please, dont do this:

public async Task<int> GetMessageCount([Parent] Model.Conversation conversation,
                                       [ScopedService] OurDbContext dbContext)
{
  return await Task.FromResult(dbContext.Messages
    .Where(message => message.Conversation == conversation)
    .Count()
  );
}

at least, name it GetMessageCountAsync() but even better, its not async, so no need to wrap it in Task<> and await Task.FromResult() at all, just:

public int GetMessageCount([Parent] Model.Conversation conversation,
                           [ScopedService] OurDbContext dbContext)
{
  return dbContext.Messages
    .Where(message => message.Conversation == conversation)
    .Count();
}

Upvotes: 2

Related Questions