Filter rows before .ToList() using LINQ and Entity Framework

user_8644294 0
Viewed 181

I have some tables:

NotificationQueue

NotQueueId OrderId CreateDateTime      SentDateTime NotType
1          Order1  2020-02-25 10:37:17 NULL         Failure
2          Order1  2020-02-25 16:37:18 NULL         Escalation
3          Order2  2020-02-24 04:37:16 NULL         Failure
4          Order2  2020-02-24 10:37:17 NULL         Escalation
5          Order3  2020-02-26 10:30:17 NULL         Failure
6          Order4  2020-02-26 10:10:17 NULL         Failure
7          Order4  2020-02-26 16:10:18 NULL         Escalation
8          Order5  2020-02-26 10:10:17 NULL         Failure

FailedTickets

FailedTicketId OrderId StatusId FailedReason     CreateDateTime      UpdateDateTime
1              Order1  2        Somereason1      2020-02-25 10:37:17 NULL
2              Order2  3        Somereason2      2020-02-24 04:37:16 NULL
2              Order3  3        Somereason3      2020-02-26 10:30:17 NULL
2              Order4  5        Cancelled        2020-02-26 10:10:17 2020-02-27 16:10:18
2              Order5  4        Succeeded Later  2020-02-26 10:10:17 2020-02-25 01:37:16

Status

StatusId StatusDESC
1        Initial
2        Validated
3        Saved
4        Complete
5        Cancelled

Essentially what's happening here is:

  1. Every time some ticket fails, it gets pushed to the FailedTickets table and is also pushed to NotificationQueue table and stays there so that some Email sending job will pick it up and send the email and update the SendDateTime column.
  2. After the ticket is not resolved within 6 hours, a new row is pushed to the NotificationQueue table indicating that the ticket needs to be escalated.

The output I need is:

  1. Do not return any tickets that are either cancelled or complete.
  2. If a same OrderId has gone to Escalation, skip the row that was made for Failure altogether and only return the Escalation row.
  3. Return all tickets that failed (if they haven't escalated yet).

Example output from using tables and criteria mentioned above:

NotType    OrderId Status    FailedReason CreateDateTime      NotQueueId SentDateTime
Escalation Order1  Validated Somereason1  2020-02-25 16:37:18 2          NULL
Escalation Order2  Saved     Somereason2  2020-02-24 10:37:17 4          NULL
Failure    Order3  Saved     Somereason3  2020-02-26 10:30:17 5          NULL

BUT currently I'm only able to acheive this:

NotType    OrderId Status    FailedReason CreateDateTime      NotQueueId SentDateTime
Failure    Order1  Validated Somereason1  2020-02-25 10:37:17 1          NULL
Escalation Order1  Validated Somereason1  2020-02-25 16:37:18 2          NULL
Failure    Order2  Saved     Somereason2  2020-02-24 04:37:16 3          NULL
Escalation Order2  Saved     Somereason2  2020-02-24 10:37:17 4          NULL
Failure    Order3  Saved     Somereason3  2020-02-26 10:30:17 5          NULL

with this code:

var skippableStatuses = new List<string> { "Complete", "Cancelled" };
var failedAndEscalatedRows = (from nq in somedbContext.NotificationQueues
               join ft in somedbContext.FailedTickets on nq.OrderID equals ft.OrderID
               join s in somedbContext.Statuses on ft.StatusID equals os.StatusID
               select new
               {
                   CreatedDate = nq.CreateDatetime,
                   NotificationType = nq.NotType,
                   NotQueueId = nq.NotQueueId,
                   OrderId = nq.OrderId,
                   SentTime = nq.SentDateTime,
                   Reason = ft.FailedReason,
                   Status = s.StatusDESC
               }
           ).Where(e => !skippableStatuses.Contains(e.Status)).ToList();

Now I probably can loop through these records and retrieve the ones that have Escalation status. But I don't want to return rows that I don't even need, so before calling .ToList(), I want to create a LINQ query to return only required rows and avoid looping altogether.

I'm sure there has to be a better way to do it, so I've turned here to SO!

entity-framework sql linq .net c#
1 Answers

What you need is, doing some filtering and grouping your first table's data (NotificationQueues) before doing any join, something like this:

 var failedAndEscalatedRows = 

    (from nq in somedbContext.NotificationQueues.GroupBy(c => c.OrderId).Select(c => new
    {
         c.Key,
         CreateDatetime = c.Select(d => d.CreateDateTime).FirstOrDefault(),
         NotQueueId = c.Select(d => d.NotQueueId).FirstOrDefault(),
         NotType = c.Count() > 1 ? c.FirstOrDefault(d => d.NotType == "Escalation").NotType 
             : c.FirstOrDefault(d => d.NotType == "Failure").NotType,
         SentDateTime = c.Select(d => d.SentDateTime).FirstOrDefault()
    })

        join ft in somedbContext.FailedTickets on nq.Key equals ft.OrderId
        join s in somedbContext.Statuses on ft.StatusId equals s.StatusId
        select new
        {
            CreatedDate = nq.CreateDatetime,
            NotificationType = nq.NotType,
            NotQueueId = nq.NotQueueId,
            OrderId = nq.Key,
            SentTime = nq.SentDateTime,
            Reason = ft.FailedReason,
            Status = s.StatusDESC
        }
    ).Where(e => !skippableStatuses.Contains(e.Status)).ToList();
user_29463290
Related