Adding computed fields (Entity Framework)

[aradalvand]

Hi there, thank you for this great framework!
I'm intending to use Hot Chocolate with Entity Framework, and I faced a scenario which I would say is pretty common, but I realized it's apparently not possible to implement with Hot Chocolate.

It's very straight-forward. Let's say you have a Book domain class, and a Rating domain class, and there is a one-to-many relationship between these two. I think it's pretty self-explanatory so far.

Now, I want to add a field on my Book type called rating, which is obviously the average of all the ratings given to a particular book.
I thought this would work (this is in the Configure method of the book schema type, as you know)

descriptor.Field(book => book.Ratings.Average(r => r.Rate)).Name("rating");

But it doesn't, and when I query the rating field, I get an Unexpected Execution Error, and it says the Ratings property is null (and I'm aware that this is because it hasn't been fetched from the database).

Any guidance on how I should implement scenarios like this would be highly appreciated.
Also, I should mention that since I don't want to face the n+1 problem, I don't want Hot Chocolate to execute book.Ratings.Average(r => r.Rate) separately after it retrieved the book from the database, but preferably it should do this in the single query it creates as well.

[michaelstaib]

Are you using UseSelections?

[TheJayMann]

There are three ways (that I know) to get this to work. First, in your initial query, you need to Include the ratings property so that the query properly does the necessary join operation. The second is for your initial query to invoke the Select method which accesses the Rate property on the Ratings property. Or, perhaps, use a DTO type which has a rating property which assigned in the Select method as defined above, which would also eliminate the need for defining the field.

Finally, there is the UseSelections method, which dynamically constructs a Select method query based on what fields are actually requested. Given that this is not a direct field access, I don't believe it will work by itself. If you are using Entity Framework Core, using UseSelections in conjunction with a DTO having a property assigned to the calculated query does just work, only including the necessary joins if the field was requested, at least in cases that I have tried. I don't know if it works well with Entity Framework as I have not tested this. This method requires the IQueryable provider to have a fairly robust interpretation of the Select method call to cross the DTO boundary, which EFCore appears to do quite well.

[aradalvand]

Are you using UseSelections?

@michaelstaib Yes. I have a Query class that has an IQueryable<Book> GetBooks() method, on which I'm using the UseSelections middleware. Selection on the related Author of a book, for example, works properly.

I think it's reasonable to say that what I've already done should basically work. Given the fact that Hot Chocolate could simply add the expression that I passed as an argument to the Field() method book.Ratings.Average(r => r.Rate) to the Select method query that it generates whenever that rating field is requested. And then EF will take care of generating the right SQL for that expression, of course.
Hot Chocolate could do something like this:

... .Select(book => new {
    rating = [The expression that I passed to the Field() method] // If rating is requested, obviously.
}).ToList();

I, of course, don't know the underlying implementation details but I think you get the idea. Please refer to my later comments as well

[aradalvand]

There are three ways (that I know) to get this to work. First, in your initial query, you need to Include the ratings property so that the query properly does the necessary join operation. The second is for your initial query to invoke the Select method which accesses the Rate property on the Ratings property. Or, perhaps, use a DTO type which has a rating property which assigned in the Select method as defined above, which would also eliminate the need for defining the field.

Finally, there is the UseSelections method, which dynamically constructs a Select method query based on what fields are actually requested. Given that this is not a direct field access, I don't believe it will work by itself. If you are using Entity Framework Core, using UseSelections in conjunction with a DTO having a property assigned to the calculated query does just work, only including the necessary joins if the field was requested, at least in cases that I have tried. I don't know if it works well with Entity Framework as I have not tested this. This method requires the IQueryable provider to have a fairly robust interpretation of the Select method call to cross the DTO boundary, which EFCore appears to do quite well.

Hi! The first way you explained won't work for me since I guess it basically first loads all the related ratings of a book from the database and then calculates the average in the application. Instead, I want the right SQL to be generated to get the average of the Rate property on all the related Ratings of a book. Let me know if I misunderstood you.

The second way also won't work for me because I want Hot Chocolate to do the selection dynamically!

But your third way appears to be an interesting solution, but it requires creating extra classes and adding further complexity, also I don't seem to quite understand what you exactly meant when you said "having a property assigned to the calculated query does just work" on the DTO? Do you mean something like this?
public double Rating => Ratings.Average(r => r.Rate)
Plus, should the DTO inherit from the domain class or what?
Thank you for taking the time to write this answer, by the way.

[aradalvand]

Are you using UseSelections?

For example, EntityGraphQL has this exact feature that I'm talking about.
You could do something like this:

schema.Type<Book>().AddField("rating", b => b.Ratings.Average(r => r.Rate));

And it would work as expected, without any extra work. It will add that expression to the Select method query it generates, and then all of this will be only one roundtrip to the database (i.e. One SQL query).

I request the rating field and receive it:

And here is SQL that gets generated:

Hot Chocolate too should definitely have this feature.

[aradalvand]

Are you using UseSelections?

Currently, it seems like Hot Chocolate is executing book.Ratings.Average(r => r.Rate) after the rest of the data is fetched from the database, and that's why it's throwing a null reference exception. I don't think that makes sense, book.Ratings.Average(r => r.Rate) should be part of the query that gets generated.

I found this out by selecting the rate fields on the ratings field in the GraphQL query and only then querying the rating field, and as you can see it works:

But if I don't select the rate field on the ratings field, it's the same story: (because book.Ratings.Average(r => r.Rate) is not part of the generated query)

[TheJayMann]

I'm assuming your model is something like this.

public class Book {
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Rating> Ratings { get; set; }
}
public class Rating {
    public int Id { get; set; }
    public decimal Rate { get; set; }
}

Assuming this, the DTO would be defined as in the following example.

public class BookDTO {
    // Maybe you do or don't want to copy the Id property over.
    public int Id { get; set; }
    public string Name { get; set; }
    // Maybe you do or don't want to expose the collection in the DTO, and maybe you do or don't want to use a RatingDTO type
    public IEnumerable<Rating> Ratings { get; set; }
    public decimal Rating { get; set; }
}

Then, in your query definition, instead of returning IQueryable<Book>, you would return IQueryable<BookDTO>. Also, you would add a Select before returning the query. It would look something like the following.

public IQueryable<BookDTO> GetBooks(BooksDbContext context) =>
    context.Books
    .Select(b => new BookDTO(){
        Id = b.Id,
        Name = b.Name,
        Ratings = b.Ratings,
        // If you are using a rating DTO type, it would be the following instead
        Ratings = b.Ratings.Select(r => new RatingDTO() { Rate = r.Rate },
        Rating = b.Ratings.Average(r => r.Rate)
    })
;

In my testing, I have found EF Core to be smart enough that, if the query asks for the rating field, then it knows it needs to create SQL to compute the average, and, if the rating field is not requested, then it does not create SQL to compute the average. Also, there are packages out there which exist to make this stuff easier to do, such as AutoMapper, though I have not actually used them for some time, so I cannot verify their compatibility.

[aradalvand]

I'm assuming your model is something like this....

I think your solution is elegant, but for something that should be fairly trivial, I think it's a little bit too complicated. I would rather not create extra DTO classes.
I honestly think it's fair to say that what I have already done should be enough to get this to work. And it is in fact enough in some other libraries and frameworks on GrpahQL that I've come across. [See this]
I think Hot Chocolate should implement this. And if they don't, I would have to implement your solution.
Thank you, again.

[TheJayMann]

Given that my needs require me to implement a DTO anyway (mainly to abstract away many of the complicated database structures underneath, especially many <-> many relationship tables), it tends not to be too much effort for me to implement things this way (i.e. most of the complicated stuff in implementing this will have already been implemented anyway). It is, however, a significant amount of work for a simple case, which is always the case when introducing DTO types, for example, when using Web API. Things like AutoMapper help by doing most, if not all, of this work for you.

I have tested the use of DTOs for situations like this, but only for EF Core. I believe I have tried with EntityFramework without success, and I believe others have tried other IQueryable providers without success. It depends on how robust the IQueryable provider is when it comes to analyzing the query.

Looking briefly at the code, I think it might be possible to enable simple scenarios using the method you described earlier with appropriate changes to the selection middlware. I notice that the Field method does take an expression tree. It depends on how difficult it would be for the selection middleware to see these custom defined fields, and how difficult it would be to attach it to the query generated by the selection middleware.

[michaelstaib]

@AradAral we will look into this ... will take a couple of days. However, any fix will be integrated into version 11. We plan to release it at the end of October. At the moment, Version 11 has no selections support, we started a rewrite of this. @PascalSenn and I will test this against the rewrite.