How to sort something in LINQ based on many dates?

Question

Hello this is a LINQ Query but it doesn't sort properly because four different dates are involved.

var EventReportRemarks = (from i in _context.pm_main_repz
                                   .Include(a => a.PM_Evt_Cat)
                                   .Include(b => b.department)
                                   .Include(c => c.employees)
                                   .Include(d => d.provncs)
                          where i.department.DepartmentName == "Finance"
                          orderby i.English_seen_by_executive_on descending
                          orderby i.Brief_seen_by_executive_on descending
                          orderby i.French_seen_by_executive_on descending
                          orderby i.Russian_seen_by_executive_on descending
                          select i).ToList();

All i want is that it should somehow combine the four dates and sort them in group not one by one.

For Example, at the moment it sorts all English Reports based on the date that executive has seen it, then Brief Report and So on.

But i want that it should check which one is seen first and so on. For example if the first report which is seen is French, then Brief, then English then Russian, so it should sort it accordingly.

Is it Possible??

score 0 · Answer 1 · answered Feb 17 '19 at 06:18

You need to have them all in one column. The approach I would do, assuming that the value of the respective cells is null, when you don't want them to show up in the order by:

var EventReportRemarks = (from i in _context.pm_main_repz
                                   .Include(a => a.PM_Evt_Cat)
                                   .Include(b => b.department)
                                   .Include(c => c.employees)
                                   .Include(d => d.provncs)
                          where i.department.DepartmentName == "Finance"
                          select new
                          {
                              Date = 
                              (
                                  i.English_seen_by_executive_on != null ? i.English_seen_by_executive_on :
                                  i.Brief_seen_by_executive_on != null ? i.Brief_seen_by_executive_on :
                                  i.French_seen_by_executive_on != null ? i.French_seen_by_executive_on :
                                  i.Russian_seen_by_executive_on
                              )
                          }).ToList().OrderBy(a => a.Date);

In the select clause you could add more columns if you whish.

Reference taken from here.

score 0 · Answer 2 · answered Feb 17 '19 at 07:44

Why not just use .Min() or .Max() on the dates and then .OrderBy() or .OrderByDescending() based on that?

Logic is creating a new Enumerable (here, an array) with the 4 dates for the current line, and calculate the Max/Min of the 4 dates: this results in getting the latest/earliest of the 4. Then order the records based on this value.

var EventReportRemarks = (from i in _context.pm_main_repz
                                   .Include(a => a.PM_Evt_Cat)
                                   .Include(b => b.department)
                                   .Include(c => c.employees)
                                   .Include(d => d.provncs)
                          where i.department.DepartmentName == "Finance"
                          select i)
.OrderBy(i => new[]{ 
    i.English_seen_by_executive_on,
    i.Brief_seen_by_executive_on, 
    i.French_seen_by_executive_on, 
    i.Russian_seen_by_executive_on 
    }.Max())
.ToList();

score 0 · Answer 3 · answered Feb 18 '19 at 08:01

Your problem is not a problem if you use method syntax for your LINQ query instead of query syntax.

var EventReportRemarks = _context.pm_main_repz
    .Where(rep => rep.Department.DepartmentName == "Finance")
    .OrderByDescending(rep => rep.English_seen_by_executive_on)
    .ThenByDescending(rep => rep.Brief_seen_by_executive_on)
    .ThenByDescending(rep => rep.French_seen_by_executive_on descending)
    .ThenByDescending(rep => resp.Russian_seen_by_executive_on descending)
    .Select(rep => ...);

Optimization

One of the slower parts of a database query is the transport of selected data from the DBMS to your local process. Hence it is wise to limit the transported data to values you actually plan to use.

You transport way more data than you need to.

For example. Every pm_main_repz (my, you do love to use easy identifiers for your items, don't you?), every pm_main_repz has zero or more Employees. Every Employees belongs to exactly one pm_main_repz using a foreign key like pm_main_repzId.

If you use include to transport pm_main_repz 4 with his 1000 Employees every Employee will have a pm_main_repzId with value 4. You'll transport this value 1001 times, while 1 time would have been enough

Always use Select to select data from the database and Select only the properties you actually plan to use. Only use Include if you plan to update the fetched objects

Consider using a proper Select where you only select the items that you actually plan to use:

    .Select(rep => new
    {
        // only Select the rep properties you actually plan to use:
        Id = rep.Id,
        Name = rep.Name,
        ...

        Employees = rep.Employees.Select(employee => new
        {
            // again: select only the properties you plan to use
            Id = employee.Id,
            Name = employee.Name,

            // not needed: foreign key to pm_main_repz
            // pm_main_repzId = rep.pm_main_repzId,
        })
        .ToList(),

        Department = new 
        {
            Id = rep.Department,
            ...
        }
        // etc for pm_evt_cat and provencs
    });

How to sort something in LINQ based on many dates?

3 Answers3

Optimization