Multiple column grouping in Entity Framework

Feedback

Question:

In my Entity Framework code, I have a straightforward statement as follows:

        query = query
                .Where(c => c.NotificationType == NotificationType.AppMessage)
                .GroupBy(c => c.ConversationId)
                .Select(d => d.OrderByDescending(p => p.DateCreated).FirstOrDefault());

It effortlessly identifies the most recent

Notification

by grouping with
conversationid
and selecting the latest option. It’s straightforward.

If the column is not AppMessage (

c.NotificationType <> NotificationType.AppMessage

), I only want the column. This is what I desire to write: a magical statement that satisfies my needs.

         query = query
                .Where(c => (c.NotificationType <> NotificationType.AppMessage) 
                || ((c.NotificationType == NotificationType.AppMessage)
                .GroupBy(c => c.ConversationId)
                .Select(d => d.OrderByDescending(p => p.DateCreated).FirstOrDefault()));

However, the GroupBy/Select is determined by the initial where statement, rendering it illogical.

How do I solve this?


Solution 1:

One method is to create a query by appending

Concat

to the end of your original query,

UNION ALL

.

query = query
    .Where(c => c.NotificationType == NotificationType.AppMessage)
    .GroupBy(c => c.ConversationId)
    .Select(d => d.OrderByDescending(p => p.DateCreated).FirstOrDefault())
    .Concat(query.Where(c => c.NotificationType != NotificationType.AppMessage));


Solution 2:

public class EntityClass
{
    public int NotificationType { get; set; }
    public int ConversationId { get; set; }
    public DateTime Created { get; set; }
    public static EntityClass GetLastNotification(int convId)
    {
        var list = new List(); // Fill the values
        list = list
            .GroupBy(i => i.ConversationId) // Group by ConversationId.
            .ToDictionary(i => i.Key, n => n.ToList()) // Create dictionary.
            .Where(i => i.Key == convId) // Filter by ConversationId.
            .SelectMany(i => i.Value) // Project multiple lists to ONLY one list.
            .ToList(); // Create list.
        // Now, you can filter it:
        // 0 - NotificationType.AppMessage
        // I didn't get what exactly you want to filter there, but this should give you an idea.
        var lastNotification = list.OrderByDescending(i => i.Created).FirstOrDefault(i => i.NotificationType == 0);
        return lastNotification;
    }
}
  1. Firstly, apply the “GroupBy” filter to your list using the ConversationId parameter. Afterwards, convert the resulting data into a dictionary and consolidate it into a single list using the SelectMany function. At this point, you will have a single list containing only the records with the desired ConversationId.


The final step involves filtering the list to select the last notification with a specific NotificationType. It should be functional 🙂

Frequently Asked Questions

Posted in Uncategorized