CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6c#-5.0
Lars Holdgaard
Lars Holdgaard

Reputation: 9966

Group by some columns depending on values in Entity Framework

I have the following simple statement in my Entity Framework code:

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

It simply finds the latest Notification based on a group by with conversationId and select latest. Easy.

However, this is ONLY what I want if c.NotificationType == NotificationType.AppMessage. If the column is different than AppMessage (c.NotificationType <> NotificationType.AppMessage), I just want the column. What I truly Want to write is a magical statement such as:

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

But this doesn't make sense because the GroupBy/Select is based on the first where statement.

How do I solve this?

Upvotes: 2

Views: 2140

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205539

The simplest way is to compose UNION ALL query using Concat at the end of your original query:

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));

Upvotes: 3

David Hruška
David Hruška

Reputation: 168

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<EntityClass>(); // 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. you filter your list with "GroupBy" based on ConversationId. Next, create a dictionary from the result and make only one list (SelectMany). Then, you already have one list where should be only records with ConversationId you want.

Last part is for filtering this list - you wanted to last notification with certain NotificationType. Should be working :)

Upvotes: 0

Related Questions