LINQ/Lambda expression: Join lists and find the average counts of data with the given formula

user_13448659 0
Viewed 223

I have a list of 500+ EventData records. Model of EventData is as given below

public class EventData
{
    public int preEventId { get; set; }
    public int empNum { get; set; }
    public int EventId { get; set; }
    public DateTime CreateDate { get; set; }
    public string UserId { get; set; }
}

Here the EventId can be 1,2,3,4 and 5 (Enum). and from this list, I want to filter the records for all weeks of the month and then calculate the avg. NB: weekList is the list of Mondays in the current month.

(count of records having EventId = 1) / (count of records with EventId = 2 or 5 and empNum's with EventId = 1 (CreateDate 5 days old or newer))

What I've done is:-

int calledCount = lstEventData?.Where(e => e.EventId == 1 &&
                     e.CreateDate >= weekList.ToList()[week] &&
                     e.CreateDate <= weekList.ToList()[week].AddDays(4)).Count() ?? 0;
                     

int totalCount = (lstEventData?
                    .Where(e => (e.EventCd == 2 || e.EventCd == 5) &&
                                 e.CreateDate >= weekList.ToList()[week] &&
                                 e.CreateDate <= weekList.ToList()[week].AddDays(4))
                    .GroupBy(e => e.empNum)
                    .Select(x => x.First())
                    .Count()) ?? 0;
                    
 int avgCalls = Convert.ToDecimal(calledCount) / Convert.ToDecimal(totalCount);

Sample SQL structure of the same:

SELECT * FROM CalledList c  INNER JOIN
        Events e 
        ON (c.empNum = e.empNum AND c.EventId IN(2,5) 
        AND c.CreateDate > e.CreateDate - 5
        AND c.EventId = 1

How can I achieve my actual goal?

lambda asp.net linq .net c#
1 Answers

Based on what I can infer from your code and explanation, I think this is what you're after:

// first, to reduce parsing your weekList EVERY time, get a reference to your start/end dates
  var wkStart = weekList.ToList()[week];

  // This may not quite work since if your weekstart is Monday midnight, 
  // adding 4 days is Friday midnight, so 12:01 AM Friday won't fall into your range.
  // I'd recommend doing AddDays(5), and using less than (<), not less than or equal (<=)
  var wkEnd = wkStart.AddDays(5); 
  
  // using consts to avoid "magic numbers"
  const int CALLED_LIST = 1;
  const int OTHER_EVENTS = 2;

  // Then get all of the events in that period that match the EventIds you want, 
  // just so your followup queries are against a smaller set
  var events = 
    lstEventData?
    .Where(e => (
    (e.EventId == 1 && e.CreateDate >= wkStart && e.CreateDate <= wkEnd) // event ID 1 within date range
                    || e.EventId == 2 || e.EventId == 5 // or event ID 2 or 5, ingoring date range for the moment
                    ))
    // Create a lookup to split by event ID to have two separate lists to join below    
    .ToLookup(e => e.EventId == 1 ? CALLED_LIST : OTHER_EVENTS);    
    
  var calledList = events[CALLED_LIST].ToList();
  var otherEvents = events[OTHER_EVENTS].ToList();
  
  if ((calledList?.Count ?? 0) == 0)
  {
    // you need to handle empty or null to
    // avoid a DivdeByZero error below.
    return;
  }
  // by handling the null/empty above, you no longer need to handle it everywhere below;
  var joinedResults = 
    calledList.Join(otherEvents, 
      c => c.empNum,
      e => e.empNum,
      (c,e) => new {Parent = c, Child = e})
      .Where(joined => joined.Parent.CreateDate > joined.Child.CreateDate.AddDays(-5));     
  
  // From here, you'll need to figure out what average you're trying to get. 
  // I made a note in the comments that I'm unclear what average you're trying to calculate.
}
user_12575570
Related