Entity framework core - support for projections, joins, nested filters and sorts

[marian-margeta]

I am curious whether the Hot Chocolate has a support for Entity Framework Core. I can't find any information or examples. It seems that the type IQueryable is supported but the result with the use of the EF core is not as great as it could be.

Filtering and sorting over the root entity seems to work fine but projecting doesn't. Always all columns are returned from the database, not only the requested ones. Nested entities (many-to-one relations) are completely ignored.

Let's say I have the two entities:

public class Master 
{
    public int Id { get; set; }
    public string Name { get; set; } = default!;
    public DateTime CreatedAt { get; set; } = DateTime.Now;
    public List<Document> Documents { get; set; } = default!;
}

public class Document
{
    public int Id { get; set; }
    public int MasterId { get; set; }
    public string Name { get; set; } = default!;
    public DateTime CreatedAt { get; set; } = DateTime.Now;
    public Master Master { get; set; } = default!;
}

and the query:

public IQueryable<Master> GetMasters()
{
    return dbContext.Masters.AsNoTracking();
}

I use the following GraphQL query:

{
    masters(order_by: {createdAt:DESC}, where: {name_starts_with: "M"}) {
      name,
      documents(order_by: {name: ASC}, where: {id_gt: 0}) {
        createdAt,
        id
      }
    }
}

the result I get doesn't contain documents as the join is missing in the SQL query:

{
  "data": {
    "masters": [
      {
        "name": "Master 01",
        "documents": null
      },
      {
        "name": "Master 02",
        "documents": null
      }
    ]
  }
}

SELECT "m"."Id", "m"."CreatedAt", "m"."Name"
FROM "Masters" AS "m"
WHERE "m"."Name" LIKE 'M%'
ORDER BY "m"."CreatedAt" DESC

I would expect the query to be parsed into this Linq query/expression:

context.Masters
    .AsNoTracking()
    .Where(x => x.Name.StartsWith("M"))
    .OrderByDescending(x => x.CreatedAt)
    .Select(x => new Master()
    {
        Name = x.Name,
        Documents = x.Documents
            .Where(z => z.Id > 0)
            .OrderBy(z => z.Name)
            .Select(z => new Document()
            {
                Id = z.Id,
                CreatedAt = z.CreatedAt
            })
            .ToList()
    });

which is translated into the correct SQL (SQLite provider 3.1.1):

SELECT "m"."Name", "m"."Id", "t"."Id", "t"."CreatedAt"
FROM "Masters" AS "m"
LEFT JOIN (
  SELECT "d"."Id", "d"."CreatedAt", "d"."Name", "d"."MasterId"
  FROM "Document" AS "d"
  WHERE "d"."Id" > 0
) AS "t" ON "m"."Id" = "t"."MasterId"
WHERE "m"."Name" LIKE 'M%'
ORDER BY "m"."CreatedAt" DESC, "m"."Id", "t"."Name", "t"."Id"

Do you plan to support such functionality in Hot Chocolate? It would be nice to have it out of the box.

[TheJayMann]

Currently it is being implemented in #1446, which is told to be released for 10.4. I also have a mostly finished implementation myself at https://github.com/TheJayMann/HotChocolate if you wanted to use it before 10.4 release. I believe it would not require much effort to port once #1446 is complete.

[marian-margeta]

@TheJayMann Thank you, I didn't know the implementation is in progress. The new feature is just about projections or nested filters/sorts will be implemented as well?

[michaelstaib]

Filter/sorts is already implemented for a while

[michaelstaib]

https://hotchocolate.io/docs/next/filters

[TheJayMann]

But not nested filters and sorts. Those require integration with selection. Implementing filters at the top level that target a nested property essentially filters top level objects which happen to contain child items which match the filter. Implementing filters at the nested level would filter the child items for each top level object present, and requires integration with selection/projection. Same with sorting.

My implementation does have nested filter, though it requires the 11 previews, as it requires a more public access to how the filtering middleware produces IQuerable filter. However, it doesn't have nested sort, as I could not figure out a correct way to implement it.

[marian-margeta]

@michaelstaib I am aware of that but I meant nested filters/sorts in a manner I posted above (order nested documents by name and take only those with id > 0):

{
    masters(order_by: {createdAt:DESC}, where: {name_starts_with: "M"}) {
      name,
      documents(order_by: {name: ASC}, where: {id_gt: 0}) {
        createdAt,
        id
      }
    }
}

If I include Documents directly in the code,

public IQueryable<Master> GetMasters()
{
    return dbContext.Masters.Include(x => x.Documents).AsNoTracking();
}

join is applied in SQL translation but sorts/filters of nested documents is still missing:

SELECT "m"."Id", "m"."CreatedAt", "m"."Name", "d"."Id", "d"."CreatedAt", "d"."MasterId", "d"."Name"
FROM "Masters" AS "m"
LEFT JOIN "Document" AS "d" ON "m"."Id" = "d"."MasterId"
WHERE "m"."Name" LIKE 'M%'
ORDER BY "m"."CreatedAt" DESC, "m"."Id", "d"."Id"

[michaelstaib]

Nested filters are also already implemented on version 11. if you opt into the preview you can use those.

[marian-margeta]

Actually I am using the latest preview version 11.0.0-preview.84. Using the GraphQL query I mentioned above I get JSON that already contains filtered/sorted nested entities. However, apparently it is done in memory as the condition is not included in generated SQL statement. Or some additional settings is required?

[michaelstaib]

So,

in the current 11.0.0 previews we allow to filter on nested objects or arrays.

It works like the following:

{
    masters(where: {name_starts_with: "M" documents: { id_gt: 0 }}) {
      name,
      documents {
        createdAt,
        id
      }
    }
}

[marian-margeta]

It is strange, because I get the following response:

{
  "errors": [
    {
      "message": "The specified input object field `documents` does not exist.",
      "locations": [
        {
          "line": 2,
          "column": 42
        }
      ]
    }
  ]
}

But I noticed that autocomplete in playground offers me the following options:
documents_all
documents_any
documents_none
documents_some

However none of them filter the documents. It is just a filter for masters. That can be really useful when I want to select all masters that have some (none/all) documents with id greater than zero (but all documents are returned):

{
    masters(where: {name_starts_with: "M" documents_some: { id_gt: 0 }}) {
      name,
      documents {
        createdAt,
        id
      }
    }
}

SELECT "m"."Id", "m"."CreatedAt", "m"."Name", "d"."Id", "d"."CreatedAt", "d"."MasterId", "d"."Name"
FROM "Masters" AS "m"
LEFT JOIN "Document" AS "d" ON "m"."Id" = "d"."MasterId"
WHERE ("m"."Name" LIKE 'M%') AND EXISTS (
     SELECT 1
     FROM "Document" AS "d0"
     WHERE ("m"."Id" = "d0"."MasterId") AND ("d0"."Id" > 0))
ORDER BY "m"."Id", "d"."Id"

But what I want is to get all masters along with their documents with id > 0. Let's say I have a few master objects and each one has thousands of documents. I want to select just those with id > 0. I think that this notation seems to be more suitable in this case:

{
    masters {
      name,
      documents(where: {id_gt: 0}) {
        createdAt,
        id
      }
    }
}

And as I said, using this query I get correct results, but the filter condition (document.id > 0) is not included in SQL statement. So I guess that all documents are fetched from the database at first and then they are filtered in memory - which may be annoying if there are thousands of documents in memory.

[TheJayMann]

@marian-margeta beat me to the punch describing the issue. However, I am posting it because it goes into further detail.

After looking more into this, I believe there is a misunderstanding about what was requested, and the solution provided.

From what I understand, The request is to be able to construct a query such that the documents per individual master is filtered without further filtering the masters.

Example Dataset:

masters:
  -
    id: 37
    name: L
    documents:
      - { id : 10 , name : initial document }
      - { id : 51, name : extra document }
  -
    id: 52
    name: M1
    documents:
      - { id : 0 , name : initial document (unsaved) }
      - { id : 0, name : extra document (unsaved) }
  -
    id: 61
    name: M2
    documents:
      - { id : 75 , name : initial document }
      - { id : 0, name : extra document (unsaved) }
  -
    id: 25
    name: M3
    documents:
      - { id : 19 , name : initial document }
      - { id : 55, name : extra document }
  -
    id: 37
    name: N
    documents:
      - { id : 94 , name : initial document }
      - { id : 116, name : extra document }

Expected results

filtered_masters:
  -
    id: 52
    name: M1
    documents: []
  -
    id: 61
    name: M2
    documents:
      - { id : 75 , name : initial document }
  -
    id: 25
    name: M3
    documents:
      - { id : 19 , name : initial document }
      - { id : 55, name : extra document }

As stated above, for a filter of a nested list type, rather than getting a documents field, four fields instead are given of documents_all, documents_any, documents_none, and documents_some. documents_any isn't relevant to the problem at hand, and documents_none isn't desired since we are looking for positive filters and not negative filters. documents_all will completely remove M1 and M2 from the results. documents_some has the problem that it will remove M1 from the list, while also returning the unwanted 'extra document (unsaved)' document on M2.

What appears to be actually requested isn't so much as a nested filter as has been defined for HotChocolate, but, rather, an inner filter; one where the filtering of a nested property does not affect the filter of the top level object. For such an inner filter to work with the currently being built selection middleware, either the filter middleware would have to be able to send it's generated query to the selection middleware so that it would be able to create the proper query, or the selection middleware itself would have to handle filtering as well. In my projection middleware, I do this by checking to see if any where parameter was passed to a nested list property, make use of the QueryableFilterVisitor to convert the filter object to a predicate expression, then use the predicate expression when building the nested projection.

https://github.com/TheJayMann/HotChocolate/blob/master/HotChocolate.Types.Projections/QueryableProjectionFilterVisitor.cs
https://github.com/TheJayMann/HotChocolate/blob/master/HotChocolate.Types.Projections/QueryableProjectionMiddleware.cs#L92-L96
https://github.com/TheJayMann/HotChocolate/blob/master/HotChocolate.Types.Projections/QueryableExtensions.cs#L47

[marian-margeta]

I think the example I gave in the first post should be clear enough:

{
    masters(order_by: {createdAt:DESC}, where: {name_starts_with: "M"}) {
      name,
      documents(order_by: {name: ASC}, where: {id_gt: 0}) {
        createdAt,
        id
      }
    }
}

equivalent LINQ expression:

context.Masters
    .AsNoTracking()
    .Where(x => x.Name.StartsWith("M"))
    .OrderByDescending(x => x.CreatedAt)
    .Select(x => new Master()
    {
        Name = x.Name,
        Documents = x.Documents
            .Where(z => z.Id > 0)
            .OrderBy(z => z.Name)
            .Select(z => new Document()
            {
                Id = z.Id,
                CreatedAt = z.CreatedAt
            })
            .ToList()
    });

This is how I can imagine Entity Framework Core support.

[sgabler-solytic]

@marian-margeta Were you able to get this working in the meantime? I'm trying to find a workaround until version 11 is published, but so far I have not found a solution

[michaelstaib]

@sgabler-solytic have you looked at rc.1

[michaelstaib]

@PascalSenn this should work now, or?

[PascalSenn]

yes this should ne working with 10.4 and UseSelection

[PascalSenn]

https://github.com/ChilliCream/graphql-workshop/blob/master/src/Server/PureCodeFirst%2BEF

        /// <summary>
        /// Gets access to all the people known to this service.
        /// </summary>
        // [Authorize]
        [UsePaging]
        [UseSelection]
        [UseFiltering]
        [UseSorting]
        public IQueryable<Person> GetPeople(
            [Service]ChatDbContext dbContext) =>
            dbContext.People;

[sgabler-solytic]

@michaelstaib thanks, i'll check it out

@PascalSenn thanks for the example

[sgabler-solytic]

@PascalSenn are you sure that it's already in 10.4.0-rc.1? I updated locally, but I neither have the [UseSelection] attribute, nor the .UseSelection() method

Am I doing something wrong maybe?

[michaelstaib]

you need to add the package... HotChocolate.Types.Selections

[sgabler-solytic]

Thanks, it works great! 👍

Will filtering on related properties also be possible in 10.4, or will this come in version 11?

Example:

descriptor
    .Filter(user => user.Contact.FirstName)
    .AllowEquals().And()
    .AllowContains().And()
    .AllowStartsWith().And()
    .AllowEndsWith().And()
    .BindFiltersExplicitly();

The Contact field of a User can be nicely loaded via .UseSelection, but whenever I try to add the above filter, I get HotChocolate.SchemaException: The member expression must specify a property or method that is public and that belongs to the type WebApplication.Entities.BusinessModel.User

[PascalSenn]

@sgabler-solytic this is already in place in v11 :)
You can also filter by arrays and sort by objects in 11

[S0PEX]

Hey I just tried it and its awesome works nice with ef core.
I was wondering is there is or will be a possibility to combine this with batch dataloaders ?
For example:

  userOne: user(userId:1) {
    username,
    email
  },
  userTwo: user(userId: 2) {
    username,
    email
  }

My only Idea to realize this would be to save all selected fields per entity and then run one big query ( in the batch loader ) which just runs

 select (getAllSelectedFieldsPerRequest...) from xyz where ....

this wouldn't be a true UseSelection but still better than selecting every possible field.

[PascalSenn]

Hi @S0PEX
This is rather difficult to realize at the moment. We are working on concepts like these in V11 though.

[XiaoBingCQY]

我很好奇Hot Chocolate是否支持Entity Framework Core。我找不到任何信息或示例。似乎IQueryable支持该类型，但是使用EF内核的结果并不尽如人意。

对_根_实体进行过滤和排序似乎可以正常工作，但投影却不能。总是从数据库返回所有列，而不仅仅是请求的列。嵌套实体（多对一关系）被完全忽略。

假设我有两个实体：

公开 课 硕士 
{
    public  int  Id { get ; 设置 ; }
     公共 字符串 Name { get ; 设置 ; } =  默认！;
    公共 DateTime  CreatedAt { get ; 设置 ; } =  DateTime。现在 ;
    公共 列表 < 文献 > 文档 { 得到 ; 设置 ; } =  默认！;
}

公共 课 文件
{
    public  int  Id { get ; 设置 ; }
     public  int  MasterId { get ; 设置 ; }
     公共 字符串 Name { get ; 设置 ; } =  默认！;
    公共 DateTime  CreatedAt { get ; 设置 ; } =  DateTime。现在 ;
    公共 法师 法师 { 获取 ; 设置 ; } = 默认！;
}

和查询：

公共 IQueryable < Master > GetMasters（）
{
    返回 dbContext。大师级。AsNoTracking（）;
}

我使用以下GraphQL查询：

{
     主人（ORDER_BY：{ createdAt：DESC }，其中：{ name_starts_with：“中号” }）{
       名称，
       文件（ORDER_BY：{ 名称：ASC }，其中：{ id_gt：0 }）{
         createdAt，
         ID
      }
    }
}

我得到的结果不包含文档，因为SQL查询中缺少联接：

{
   “ data ”：{
     “ masters ”：[
      {
        “ name ”： “ Master 01 ”，
         “ document ”：空
      }，
      {
        “ name ”： “ Master 02 ”，
         “ document ”：空
      }
    ]
  }
}

选择 “ m ”。“ Id ”，“ m ”。“ CreatedAt ”，“ m ”。“名”
 FROM  “大师”  AS  “米”
 WHERE  “米”。“名称”  LIKE  ' M％'
 ORDER BY  “米”。“ CreatedAt ” 

I would expect the query to be parsed into this Linq query/expression:

context.Masters
    .AsNoTracking()
    .Where(x => x.Name.StartsWith("M"))
    .OrderByDescending(x => x.CreatedAt)
    .Select(x => new Master()
    {
        Name = x.Name,
        Documents = x.Documents
            .Where(z => z.Id > 0)
            .OrderBy(z => z.Name)
            .Select(z => new Document()
            {
                Id = z.Id,
                CreatedAt = z.CreatedAt
            })
            .ToList()
    });

which is translated into the correct SQL (SQLite provider 3.1.1):

SELECT "m"."Name", "m"."Id", "t"."Id", "t"."CreatedAt"
FROM "Masters" AS "m"
LEFT JOIN (
  SELECT "d"."Id", "d"."CreatedAt", "d"."Name", "d"."MasterId"
  FROM "Document" AS "d"
  WHERE "d"."Id" > 0
) AS "t" ON "m"."Id" = "t"."MasterId"
WHERE "m"."Name" LIKE 'M%'
ORDER BY "m"."CreatedAt" DESC, "m"."Id", "t"."Name", "t"."Id"

Do you plan to support such functionality in Hot Chocolate? It would be nice to have it out of the box.

我很好奇Hot Chocolate是否支持Entity Framework Core。我找不到任何信息或示例。似乎IQueryable支持该类型，但是使用EF内核的结果并不尽如人意。

对_根_实体进行过滤和排序似乎可以正常工作，但投影却不能。总是从数据库返回所有列，而不仅仅是请求的列。嵌套实体（多对一关系）被完全忽略。

假设我有两个实体：

公开 课 硕士 
{
    public  int  Id { get ; 设置 ; }
     公共 字符串 Name { get ; 设置 ; } =  默认！;
    公共 DateTime  CreatedAt { get ; 设置 ; } =  DateTime。现在 ;
    公共 列表 < 文献 > 文档 { 得到 ; 设置 ; } =  默认！;
}

公共 课 文件
{
    public  int  Id { get ; 设置 ; }
     public  int  MasterId { get ; 设置 ; }
     公共 字符串 Name { get ; 设置 ; } =  默认！;
    公共 DateTime  CreatedAt { get ; 设置 ; } =  DateTime。现在 ;
    公共 法师 法师 { 获取 ; 设置 ; } = 默认！;
}

和查询：

公共 IQueryable < Master > GetMasters（）
{
    返回 dbContext。大师级。AsNoTracking（）;
}

我使用以下GraphQL查询：

{
     主人（ORDER_BY：{ createdAt：DESC }，其中：{ name_starts_with：“中号” }）{
       名称，
       文件（ORDER_BY：{ 名称：ASC }，其中：{ id_gt：0 }）{
         createdAt，
         ID
      }
    }
}

我得到的结果不包含文档，因为SQL查询中缺少联接：

{
  "data": {
    "masters": [
      {
        "name": "Master 01",
        "documents": null
      },
      {
        "name": "Master 02",
        "documents": null
      }
    ]
  }
}

SELECT "m"."Id", "m"."CreatedAt", "m"."Name"
FROM "Masters" AS "m"
WHERE "m"."Name" LIKE 'M%'
ORDER BY "m"."CreatedAt" DESC

I would expect the query to be parsed into this Linq query/expression:

context.Masters
    .AsNoTracking()
    .Where(x => x.Name.StartsWith("M"))
    .OrderByDescending(x => x.CreatedAt)
    .Select(x => new Master()
    {
        Name = x.Name,
        Documents = x.Documents
            .Where(z => z.Id > 0)
            .OrderBy(z => z.Name)
            .Select(z => new Document()
            {
                Id = z.Id,
                CreatedAt = z.CreatedAt
            })
            .ToList()
    });

which is translated into the correct SQL (SQLite provider 3.1.1):

SELECT "m"."Name", "m"."Id", "t"."Id", "t"."CreatedAt"
FROM "Masters" AS "m"
LEFT JOIN (
  SELECT "d"."Id", "d"."CreatedAt", "d"."Name", "d"."MasterId"
  FROM "Document" AS "d"
  WHERE "d"."Id" > 0
) AS "t" ON "m"."Id" = "t"."MasterId"
WHERE "m"."Name" LIKE 'M%'
ORDER BY "m"."CreatedAt" DESC, "m"."Id", "t"."Name", "t"."Id"

Do you plan to support such functionality in Hot Chocolate? It would be nice to have it out of the box.

[marian-margeta]

I tried this new selection feature just a few days ago and it works perfectly. Thank you for this! I have played with this a bit and now I am trying to figure out how to use it in my queries. I have some questions in terms of extensibility of the LINQ expression creation.

I have 3 entities - User, Document and many-to-many relation table UserDocRel:

class User
{
    public long Id { get; set; }
    public string Name { get; set; }
    public byte[] Thumbnail { get; set; }

    public List<UserDocRel> UserDocRels { get; set; }
}

class UserDocRel
{
    public long UserId { get; set; }
    public long DocumentId { get; set; }
    public Document Document { get; set; }
    public User User { get; set; }
}

class Document
{
    public long Id { get; set; }
    public string Text { get; set; }
    public List<UserDocRel> UserDocRels { get; set; }
}

Ideally, I would like to end with this graphql user type

type User {
  name: String 
  thumbnail: String # thumbnail encoded in base64
  documents: [Document] # documents of this user
}

I am struggling with thumbnail and documents fields.

Documents

To get all user documents, I need to include the following expression, before it is compiled into SQL

Documents = UserDocRels.Select(x => x.Document).ToList();

Is there any approach to do this? It would be nice to have it outside of my entity object. For example in type configuration, instead of resolver:

public class UserType : ObjectType<User>
{
    protected override void Configure(IObjectTypeDescriptor<User> descriptor)
    {
        descriptor
            .Field("documents")
            .SelectionExpression<User>(c => c.UserDocRels.Select(x => x.Document)); // of course, this does not exist
    }
}

Thumbnails

Maybe more challenging is the thumbnail field. In the entity object It is defined as a byte[] array but I would like to convert it to string (base64). I am new to HC so I can't figure out how to achieve this and if it is possible at all. Maybe in this case it would be possible to change the type in entity to string and use EF Core to do the translation job, but in general, I'd like to change the results and their types also in other cases.

By the way, I noticed that it is not possible to select the tumbnail as an array. And in general, all enumerable properties of non-object types are not selectable. I get this error:

"errors": [
  {
    "message": "UseSelection is in a invalid state. Type Byte is illegal!"
  }
]

The same for array of strings, ints, bools, etc.

[TheJayMann]

Both of your examples would likely be easier if you were to implement DTOs rather than exposing the raw entity types directly to GraphQL. This will allow you insert the byte[] to string conversion using base64 as well as remove the many to many entity class. Entity Framework Core has full support for constructing DTO objects directly within a select clause while also retaining correct mapping if you were to perform other operations after the select. It is what I did in my testing in order to retrieve a first name and last name for a person from the database, but also expose an additional computed property of full name, as well as to hide the extra 5 table joins for getting an address and instead expose it directly as a property as being a simple list of addresses.

What I am seeing for your implementation would be something like the following. Note that I am typing directly in the response without actually testing anything in code, so there may be easy to fix mistakes.

public IQueryable<UserDTO> GetUsers([Service] UserDbContext db) =>
    db.Users.Select(u => new UserDTO() {
        Name = u.Name,
        Thumbnail = Convert.ToBase64(u.Thumbnail),
        Documents = u.UserDocRels.Select(ud => new DocumentDTO(){
            Id = ud.Document.Id,
            Text = ud.Document.Text,
        }),
    })
;

I have done many test with this, and Entity Framework Core is still smart enough that if your query does not include the documents property, then no join will be performed to obtain the list of documents, and if you add filtering within the documents property, a correct query will be constructed within a subquery to create the correct filtered join. as far as the Convert.ToBase64 is concerned, I haven't tested it, but it should work; if Entity Framework Core knows about it, it may translate it to an appropriate SQL conversion function to convert it on SQL side, while if it doesn't know about it, it should use the runtime to convert the binary data retrieved from the database as it is converting the data to the DTO type. Note that your DTO types don't necessarily need to be suffixed with DTO, I just did it here because it is sometimes a common practice to do so, and to contrast with the entity types you described above.

[marian-margeta]

@TheJayMann Thank you for your quick response. I am familiar with DTO pattern, I use it frequently with automapper in all of my REST APIs but for some reason I didn't try it here :). I Tried it out and it works like a charm! Thanks again.

Anyway, selection of properties whose type is an array of the primitive types doesn't seems to work. I get error bellow. I don't know if it is a bug or known limitation.

"errors": [
  {
    "message": "UseSelection is in a invalid state. Type Byte is illegal!"
  }
]

[TheJayMann]

So I'm not exactly sure why Hotchocolate is trying to do anything with a Byte at this point. If you've properly typed UserDTO.Thumbnail as a string, then it shouldn't be trying to do anything with Byte. Unless , for some reason, Entity Framework Core itself is having an issue somewhere and UseSelection is bubbling up the error. But even that seems a bit strange.

[twilly86]

This is working awesome for me in version 11. I've been seeing some recommendations that version 11 is not ready for production but we need this for our project to go live. Would there be an easy way to back port this to version 10, or is it safe to be running version 11 in production?

[marian-margeta]

@TheJayMann It works fine If I convert thumbnail to base64 while projecting to DTO. The problem occurs if I want to get this thumbnail as a byte array for some reason (if I turn off byte64 conversion and change the type from string to byte[]). I don't want to project any arrays at this time but I think it could be supported anyway.

@twilly86 I am not sure what is not implemented in version 10 but I am trying filtration and selection features over IQueryable on version 10.4.0 and it works great.

[twilly86]

I am trying to filter/sort on a nested item which is not in v10, but is in v11. Seeing if v11 is stable to run in production against EF core, or what it would take to get nested sorting/filtering available in v10.

[S0PEX]

@TheJayMann
I also have currently some issues with exposing the whole object. But I am not sure how I would use this DTO pattern ( you have posted above ) correctly in my case.

Currently, I have a User Entity:

• UserId
• Name
• Some other fields.....
• List OwnedProducts

then I have my Products Entity:

• ProductId
• Title
• Some other fields....
• List OwnedByUsers

My current issue is that when I am exposing the user through this dto pattern like:

public IQueryable<UserDTO> GetUsers([Service] UserDbContext db) =>
    db.Users.Select(u => new UserDTO() {
       Id = u.Id
       Name = u.Name,
       OwnedProducts = u.OwnedByUsers.Select(ud => new ProductDTO(){
            Id = ud.Product.Id,
            Title = ud.Product.Title,
             OwnedBy = ud.Product.OwnedByUsers.Select(ou => new UsersDto...
        });

So basically I end up in infinite recursion because the User as multiple products and the products again have this owners and so on. Do you have a tip when dealing with such a situation?
My second option would be to just create an ObjectType and then change it through the descriptor. But maybe you have a better solution for this.

Regards Artur

[TheJayMann]

With this design, I believe a business decision should be made; how much should recursion be allowed? There must be a point at which attempting further recursion doesn't make sense, or possibly affects performance to attempt to get all the data at one time.
It appears that this service would make available all users (I'm assuming with either filters or paging preferred), for which each will provide a list of products owned by that user, then for each will provide a list of all users which own that product. At this point in this single query, do you still need to also provide a list of all products owned by each user which owns the product? And, do you then need to get a full list of users which owns those products which are owned by the users which own the product owned by the user? Or could it possibly make more sense to just get the list of users, the products they own, and then just enough information about the users who own the product that you could then pull details about said user at a later time, such as when a user clicks a link or button in the application.

If you truly do need to make potentially infinite recursion available, or at least allow a large level of recursion, you will likely either need to set up the select query in a recursive manner, or possibly write your own middleware which can take the input query and translate it into the proper dynamic linq query.

If this is something that many people want, then perhaps a feature request (or even a pull request) could be made for HotChocolate.

[baleeds]

Regarding DTOs and projection, is there a recommended way to project my IQueryable to a DTO using AutoMapper?

[UseSelection]
public IQueryable<TitleDto> GetTitles([Service] DbContext dbContext, [Service] IMapper mapper)
{
    var titles =  dbContext.Titles.ProjectTo<TitleDto>(mapper.ConfigurationProvider);
    return titles;
}

In my current example the projection to AutoMapper appears to "overwrite" the [UseSelection] middleware, and will request all fields required for filling the DTO.

[kotpal]

Support for DTOs (projections) is required - because we don't want to expose the mapping tables that are required for 1-N, N-N relationships (with additional data in the mapping table). The consumers of the API (GraphQL or REST) doesn't need to know these internals.

[Abdullah-onGit]

[UseSelection] Not Working

HotChocolate.AspNetCore 11.3.2
HotChocolate.Types.Selections 10.5.5

Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware: Error: An unhandled exception has occurred while executing the request. HotChocolate.SchemaException: For more details look at the Errors property.

1. Could not load type 'HotChocolate.Utilities.TypeInfo' from assembly 'HotChocolate.Types, Version=11.3.2.0, Culture=neutral, PublicKeyToken=null'. (HotChocolate.Types.ObjectType<FoodShopGQLApi.GraphQL.Query>)

   at HotChocolate.Configuration.TypeInitializer.DiscoverTypes()
   at HotChocolate.Configuration.TypeInitializer.Initialize(Func1 schemaResolver, IReadOnlySchemaOptions options) at HotChocolate.SchemaBuilder.Setup.InitializeTypes(SchemaBuilder builder, DescriptorContext context, IBindingLookup bindingLookup, IReadOnlyList1 types, LazySchema lazySchema)
   at HotChocolate.SchemaBuilder.Setup.Create(SchemaBuilder builder)
   at HotChocolate.SchemaBuilder.Create()
   at HotChocolate.SchemaBuilder.HotChocolate.ISchemaBuilder.Create()
   at HotChocolate.Execution.RequestExecutorResolver.CreateSchemaAsync(NameString schemaName, RequestExecutorSetup options, IServiceProvider serviceProvider, CancellationToken cancellationToken)
   at HotChocolate.Execution.RequestExecutorResolver.CreateSchemaServicesAsync(NameString schemaName, RequestExecutorSetup options, CancellationToken cancellationToken)
   at HotChocolate.Execution.RequestExecutorResolver.GetRequestExecutorNoLockAsync(NameString schemaName, CancellationToken cancellationToken)
   at HotChocolate.Execution.RequestExecutorResolver.GetRequestExecutorAsync(NameString schemaName, CancellationToken cancellationToken)
   at HotChocolate.Execution.RequestExecutorProxy.GetRequestExecutorAsync(CancellationToken cancellationToken)
   at HotChocolate.Execution.RequestExecutorProxy.GetSchemaAsync(CancellationToken cancellationToken)
   at HotChocolate.AspNetCore.ToolOptionsFileMiddleware.Invoke(HttpContext context)
   at HotChocolate.AspNetCore.HttpGetSchemaMiddleware.InvokeAsync(HttpContext context)
   at HotChocolate.AspNetCore.HttpMultipartMiddleware.InvokeAsync(HttpContext context)
   at HotChocolate.AspNetCore.HttpPostMiddleware.InvokeAsync(HttpContext context)
   at HotChocolate.AspNetCore.WebSocketSubscriptionMiddleware.InvokeAsync(HttpContext context)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

[tobias-tengler]

@Abdullah-onGit you can't mix versions of Hot Chocolate packages. If you want to use Projections with v11, you'll have to use the HotChocolate.Data package with the same version as your HotChocolate.AspNetCore package (11.3.2)
Learn more about Projections in v11

[tobias-tengler]

Closing this issue, since all of the things the original issue talks about are supported in v11 to my knowledge.

If there are other requests in this thread that are yet to be implemented/fixed, please create a separate issue for those and make sure they aren't yet addressed in the latest preview. Also please keep the issue focused on one specific thing and not multiple things, as these issues are harder to track.

[Abdullah-onGit]

@Abdullah-onGit you can't mix versions of Hot Chocolate packages. If you want to use Projections with v11, you'll have to use the HotChocolate.Data package with the same version as your HotChocolate.AspNetCore package (11.3.2)
Learn more about Projections in v11

Thank you so much for your replay ❤

How about in DataLoaders?

lets take example from 3-understanding-dataLoader.md

protected override async Task<IReadOnlyDictionary<int, Speaker>> LoadBatchAsync(
            IReadOnlyList<int> keys, 
            CancellationToken cancellationToken)
        {
            await using ApplicationDbContext dbContext = 
                _dbContextFactory.CreateDbContext();

            return await dbContext.Speakers
                .Where(s => keys.Contains(s.Id))
                .ToDictionaryAsync(t => t.Id, cancellationToken);
        }
    }

How to use projection here?

[tobias-tengler]

@Abdullah-onGit I don't think you can use projections in DataLoaders. A DataLoader's job is to fetch an entity based on its Id. So for the same Id you should always get the same entity. The DataLoader therefore can't take an individual subselection of fields into account when fetching the entity, and thus you can not use Projections here. Projections only work if you are directly translating your GraphQL query into a database query. If you have custom resolvers/Data Loaders inbetween, the query can only be projected until it hits one of these unsupported fields.

@PascalSenn please correct me, if what I'm saying is not accurate.

[chandsalam1108]

Can someone provide github sample for "nested filters and sorts" with HotChocolate.Data.EntityFramework v11.3.0? Im

Implementation in Query.cs file.

[UseDbContext(typeof(HealthCenterContext))]
        [UseProjection]
        [UseFiltering]
        [UseSorting]
        public IQueryable<Clinic> GetClinics([ScopedService] HealthCenterContext context)
        {
            return context.Clinics;
        }

        [UseDbContext(typeof(HealthCenterContext))]
        [UseProjection]
        [UseFiltering]
        [UseSorting]
        public IQueryable<HealthCenter> GetHealthCenters([ScopedService] HealthCenterContext context)
        {
            return context.HealthCenters;
        }


Query Redquest:

{
  clinics(where : {clinicCode : {eq : "11"}}){
    clinicCode
    clinicNameEn
    clinicHealthCenterMappings{
      hCntNavigation(where : {hCntCode : {eq : "KBN"}}){
        hCntCode
        hCntNameEn
      }
    }
  }
}

Response:
{
  "errors": [
    {
      "message": "Unexpected Execution Error",
      "locations": [
        {
          "line": 2,
          "column": 3
        }
      ],
      "path": [
        "clinics"
      ],
      "extensions": {
        "message": "No generic method 'Where' on type 'System.Linq.Enumerable' is compatible with the supplied type arguments and arguments. No type arguments should be provided if the method is non-generic. ",
        "stackTrace": "   at System.Linq.Expressions.Expression.FindMethod(Type type, String methodName, Type[] typeArgs, Expression[] args, BindingFlags flags)\r\n   at System.Linq.Expressions.Expression.Call(Type type, String methodName, Type[] typeArguments, Expression[] arguments)\r\n   at HotChocolate.Data.Projections.Handlers.QueryableFilterInterceptor.BeforeProjection(QueryableProjectionContext context, ISelection selection)\r\n   at HotChocolate.Data.Projections.Expressions.Handlers.ProjectionFieldWrapper`1.TryHandleEnter(T context, ISelection selection, ISelectionVisitorAction& action)\r\n   at HotChocolate.Data.Projections.ProjectionVisitor`1.Enter(ISelection selection, TContext context)\r\n   at HotChocolate.Data.Projections.SelectionVisitor`1.Visit(ISelection selection, TContext context)\r\n   at HotChocolate.Data.Projections.ProjectionVisitor`1.Visit(ISelection selection, TContext context)\r\n   at HotChocolate.Data.Projections.SelectionVisitor`1.VisitChildren(IOutputField field, TContext context)\r\n   at HotChocolate.Data.Projections.SelectionVisitor`1.Visit(IOutputField field, TContext context)\r\n   at HotChocolate.Data.Projections.ProjectionVisitor`1.Visit(IOutputField field, TContext context)\r\n   at HotChocolate.Data.Projections.SelectionVisitor`1.VisitChildren(ISelection selection, TContext context)\r\n   at HotChocolate.Data.Projections.SelectionVisitor`1.Visit(ISelection selection, TContext context)\r\n   at HotChocolate.Data.Projections.ProjectionVisitor`1.Visit(ISelection selection, TContext context)\r\n   at HotChocolate.Data.Projections.SelectionVisitor`1.VisitChildren(IOutputField field, TContext context)\r\n   at HotChocolate.Data.Projections.SelectionVisitor`1.Visit(IOutputField field, TContext context)\r\n   at HotChocolate.Data.Projections.ProjectionVisitor`1.Visit(IOutputField field, TContext context)\r\n   at HotChocolate.Data.Projections.ProjectionVisitor`1.Visit(TContext context)\r\n   at HotChocolate.Data.Projections.Expressions.QueryableProjectionProvider.<CreateExecutor>g__ExecuteAsync|2_1[TEntityType](FieldDelegate next, IMiddlewareContext context)\r\n   at HotChocolate.Data.ToListMiddleware`1.InvokeAsync(IMiddlewareContext context)\r\n   at HotChocolate.Types.EntityFrameworkObjectFieldDescriptorExtensions.<>c__DisplayClass2_1`1.<<UseDbContext>b__4>d.MoveNext()\r\n--- End of stack trace from previous location ---\r\n   at HotChocolate.Types.EntityFrameworkObjectFieldDescriptorExtensions.<>c__DisplayClass2_1`1.<<UseDbContext>b__4>d.MoveNext()\r\n--- End of stack trace from previous location ---\r\n   at HotChocolate.Execution.Processing.ResolverTask.ExecuteResolverPipelineAsync(CancellationToken cancellationToken)\r\n   at HotChocolate.Execution.Processing.ResolverTask.TryExecuteAsync(CancellationToken cancellationToken)"
      }
    }
  ],
  "data": {
    "clinics": null
  }
}

Even tried this approach but still not working.

Query Request:

{
  clinics(where : 
  {clinicCode : {eq : "11"} 
  clinicHealthCenterMappings : {some : {
    hCntNavigation : {cOVID_HC : {eq: 1}}
  }
  }
  }){
    clinicCode
    clinicNameEn
    clinicHealthCenterMappings{
      hCntNavigation{
        hCntCode
        hCntNameEn
        cOVID_HC
      }
    }
  }
}

![image](https://user-images.githubusercontent.com/48621464/127852994-f246d106-385f-4e82-abc3-d719b114c274.png)