Filtering/Sorting computed fields in Hot Chocolate

Question

In my application I have the following DTO object which retrieves data, via EF Core, from SQL and computes a certain field:

public class MyDTO
{
  public string MyDTOProperty { get; set ; }
  
  public string MyDTOComputedField(){
     ...
  }

}

My query method looks like:

public class MyQueries
{
    ...

    [UseDbContext(typeof(ApiDbContext))]
    [UseFiltering(typeof(MyFilter))]
    [UseSorting]
    public IQueryable<MyDTO> GetObject([ScopedService] ApiDbContext context){
        var query = context.MyDB;
        return query.Select(fea => new MyDTO(){
            MyDTOProperty = fea.property
        });
    }
}

Filtering and sorting only seems to work on the properties with get and set method. My question is, how can I enable filtering and sorting on my computed fields such that the following GraphQL query would be possible:

{
  Object(where: {MyDTOComputedField: {contains: "someSubString"}}, order: {MyDTOComputedField: ASC}){
    MyDTOProperty 
    MyDTOComputedField
  }
}

I already tried with defining my own filtering/sorting middleware, without any luck so far.

Answer 1

TL;DR; You are using Custom Resolver (HC feature), not Computed Column (T-SQL feature), which could not be translated to SQL by Entity Framework.

---

First thing first, this is not a Hot Chocolate problem, but Entity Framework problem.

[UseFiltering]

Use filtering is not a magic nor golden bullet. It is only middleware, which will generate argument where for your endpoint and then, at runtime, it will take this argument (in your case {MyDTOComputedField: {contains: "someSubString"}}), make Linq Expression from it and return input.Where(Expression).

And thats pretty much it.

(Of course, if you ever wrote string -> linq expression piece of code then you know, its not THAT simple, but good folks from HC did exactly that for us :) )

Something like

System.Linq.Expression<Func<MyDTO, bool>> where = 
  myDto => myDto.MyDTOComputedField.Contains("someSubString");

return input.Where(where);

(remember, every middleware in HC is just a piece of pipe - it have input, some process and output. Thats why order of middlewares matters. Btw, same with "order by", but it will return input.OrderBy(expression))

Now, because input is DbSet<MyDTO>, then nothing is executed "right away" but lazily - real work is done by Entity Framework - it take linq Expresion (.Where().Sort()), translate it to T-SQL and send it as query.

And there is your problem: Your MyDTO.MyDTOComputedField is not translateable to SQL.

Why its not translateable?

Because your MyDTOComputedField is not "computed column" but "custom resolver". It exists only in your app and SQL have no idea what it should contains. Maybe it is something trivial as a + b * 42 (then computed column would be great!) but maybe it is request to another server REST api (why not :) ) - we dont know.

Then why not execute part of query on server and rest locally?

Because this scale reeeeeeeeally badly. You did not show us implementation of MyDTO.MyDTOComputedField, so let assume it do something trivial. Like cast((a + b * 42) as nvarchar(max));. Meaning, it will always be some int but casted as nvarchar. Meaning, if you ask for Contains("someSubString") it will always have 0 results.

Ok, now imagine, your MyDTO table (btw, I expect MyDTO to be EF model even with DataTransferObject in name...) have 10.000.000 rows (in enterprise scale app its bussiness as usual :) ). Because you are sane person (and because it will make this example much better to understand :) ), you add pagination. Lets say 100 items per page.

In this example, you expect EF to do select top 100 * from MyDto where MyDTOComputedField like '%someSubString%'. But thats not gonna happen - sql have no idea what MyDTOComputedField is. So it have two options, both bad: It will execute select top 100, then do filter locally - but there is zero result. So it will take another 100 and another 100 and another and another and (10.000.000/100 = 100.000 select query!) only to found that there is 0 result. Another possible solution is, when EF found that some part of expression have to be executed locally, it will execute locally whole query. So it will select, fetch, materialise 10.000.000 entities in one go and THEN it will filter them just to see there is 0 result. Somehow better, but still bad.

You just DDoS yourself.

Btw, Option 2 was what Entity Framework before core (Classic?) did. And it was source of soooo much bugs, when you accidentally fetched whole table, that good folks from EF team drop support for it and now they throw

"The LINQ expression 'DbSet()\n .Where(f => new MyDTO{ \r\n id = f.i, \r\n }\r\n.MyDTOProperty == __p_3' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See go.microsoft.com/fwlink/?linkid=2101038 for more information."

Ok... But what to do?

I know there will never be many rows.

Maybe MyDTO is just some list which will never explode (lets say, for example, VAT rates - there is pretty much Standard, Zero, Reduced + some states have some more. So we hardly look at table greater then ~5 rows, more when your app is international, but still - a few.)

Then you dont have to be afraid of local execution. Just add ".ToArray()" or ".ToList()" on the end of your DbSet call. As your Exception told you.

But be aware that this can really bite you later, if not done carefully.

Computed Column

If your implementation of MyDTOComputedField is trivial, you can move it to database. Set EF ComputedColumn, do migration, drop your resolver and you are ready to go.

Database View

Another possible option is to make database view. This is more robust solution then Computed Column (at least, you can optimalise your view really well (custom index(es), better joins, no inner query etc...), but it take more work & you have to know what you are doing. AFAIK EF cant generate view for you, you have to write it by hand. Just make empty migration, add your view, EF entity (make sure to use ToView() and not ToTable()), drop your resolver and you are ready to go.

In both cases, your query (dto?) model will be different from mutation (domain?) model, but thats ok - you really do not want to let consumer of your api to even try to mutate your MyDTOComputedField anyway.

Its not possible to translate it to SQL

Maybe your custom resolver do something not really under your control / not doable in sql (= not doable in EF). For example, http call to another server. Then, its up to you to do it right within your business logic. Maybe add custom query argument. Maybe write your own implementation of [UseFiltering] (its not THAT hard - HotChocolate is open source with great licencing, so you can basically go and [ctrl] + [c] [ctrl] + [v] current implementation and add what you need to add.)

I can't advise you, i dont know your bussiness requirement for MyDTOComputedField.