'How to use GroupBy with Join in Linq's method syntax in EF?

I have the following query:

            var chat_AppUsers = _context.Chat_AppUsers;
            var chats = _context.Chats;

            var chat_lists = chat_AppUsers.Join(chats,
                ca => ca.ChatId,
                c => c.ChatId,
                (ca, c) => new
                {
                    ChatId = ca.ChatId,
                    ChatType = c.ChatType,
                    chat_AppUsers = c.chat_AppUsers
                }
            ).ToList();

            var grouped_list = chat_lists
            .GroupBy(c => c.ChatId);

Basically, I want a list of all chats with their users by ChatId, like:

  "result": [
    {
      "chatId": "alice_bob_id_1",
      "chat_AppUsers": [
        {
          "chat_AppUserId": "Chat_AppUserId_1",
          "chatId": "alice_bob_id_1",
          "chat": null,
          "appUserId": "bob_id",
          "appUser": null
        },
        {
          "chat_AppUserId": "Chat_AppUserId_2",
          "chatId": "alice_bob_id_1",
          "chat": null,
          "appUserId": "alice_id",
          "appUser": null
        }
      ]
    },
  ]

But GroupBy doesn't group by the unique ChatId of alice_bob_id_1, and since I have two users in Chat_AppUsers, I get the duplicate result below instead:

  "result": [
    {
      "chatId": "alice_bob_id_1",
      "chat_AppUsers": [
        {
          "chat_AppUserId": "Chat_AppUserId_1",
          "chatId": "alice_bob_id_1",
          "chat": null,
          "appUserId": "bob_id",
          "appUser": null
        },
        {
          "chat_AppUserId": "Chat_AppUserId_2",
          "chatId": "alice_bob_id_1",
          "chat": null,
          "appUserId": "alice_id",
          "appUser": null
        }
      ]
    },
    {
      "chatId": "alice_bob_id_1",
      "chat_AppUsers": [
        {
          "chat_AppUserId": "Chat_AppUserId_1",
          "chatId": "alice_bob_id_1",
          "chat": null,
          "appUserId": "bob_id",
          "appUser": null
        },
        {
          "chat_AppUserId": "Chat_AppUserId_2",
          "chatId": "alice_bob_id_1",
          "chat": null,
          "appUserId": "alice_id",
          "appUser": null
        }
      ]
    },
  ]

I am a beginner in Linq, and wasn't able to find examples of using Join with GroupBy using method syntax anywhere.

Thanks and much appreciated,

c++sharpentity-frameworklinq


Solution 1:[1]

the method syntax of linq is very hard to understand I prefer to use query syntax

        var query = from chatAppUser in _context.Chat_AppUser
                    join chat in _context.Chat
                    on chatAppUser.ChatId equals chat.Id
                    select new
                    {
                        ChatId = chat.Id,
                        Chat_AppUser = chat.Chat_AppUsers
                    };

but there is also an easier way to join two tables

            var query = _context.Chat.Include(i => i.Chat_AppUser).ToList();

and for your question ..

the GroupBy Element in Linq is a key value IEnumerable of IGrouping

The GroupBy operator returns a group of elements from the given collection based on some key value. Each group is represented by IGrouping<TKey, TElement> object. from tutorialsTeacher

I hope this link can help you

Solution 2:[2]

I think you could achieve what you want by grouping the chat_AppUsers by ChatId first, and then join the grouped results with chats:

var grouped_list = chatAppUsers
    .GroupBy(appUser => appUser.ChatId)
    .Join(chats,
        appUserGroup => appUserGroup.Key,
        chat => chat.ChatId,
        (appUserGroup, chat) => new {
            ChatId = chat.ChatId,
            ChatType = chat.ChatType,
            Chat_AppUsers = appUserGroup
         })
    .ToList();

Taking your example, a simplified version of chat_AppUsers may look like the following:

chat_AppUsers:
{
    ChatId: "alice_bob_id_1",
    AppUserId: "bob_id"
},
{
    ChatId: "alice_bob_id_1",
    AppUserId: "alice_id"
}

The .GroupBy() operation

chatAppUsers.GroupBy(appUser => appUser.ChatId)

will group your app users by ChatId. The result from the .GroupBy() operation can (after enumeration, e.g. after calling .ToList()) be visualized as

[0]
    Key: "alice_bob_id_1"
    [0]
        ChatId: "alice_bob_id_1"
        AppUserId: "bob_id"
    [1]
        ChatId: "alice_bob_id_1"
        AppUserId: "alice_id"

When we then join this grouping withchats, based on the grouping's Key, the app users are already properly grouped into their respecitve chats.

Example fiddle here.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Javid_Leo
Solution 2 Astrid E.

Related Questions