Sort List on a property in a related List

Question

Say we have two List<T>. The first is a list of sales totals:

class SalesTotals
{
    public Guid EmpID { get; set; }
    public string EmpName { get; set; }
    public decimal? TotalSales { get; set; }
}

Then we have another list of sales by year:

class YearlySales
{
    public Guid EmpID { get; set; }
    public short SalesYear { get; set; }
    public decimal? YearlyTotals { get; set; }
}

These are used together to create a "cross tab" report which lists the total sales by each employee, followed by a column for each year with the related yearly sales. It would look something like this:

| Name | Total | 2018 | 2017 | 2016 |
+------+-------+------+------+------+
|  Joe |    70 |   20 |      |   50 |
|  Sam |    60 |   30 |   20 |   10 |
| Fred |    50 |   30 |      |   20 |
|  Bob |    40 |   10 |   15 |   15 |

By default, the report is sorted by TotalSales (no problem). But if we want to sort by an individual year, things get trickier. Sorted by 2017 (then by total):

| Name | Total | 2018 | 2017 | 2016 |
+------+-------+------+------+------+
|  Sam |    60 |   30 |   20 |   10 |
|  Bob |    40 |   10 |   15 |   15 |
|  Joe |    70 |   20 |      |   50 |
| Fred |    50 |   30 |      |   20 |

I assume we want to (Left) Join these two List<T>s on EmpID, where SalesYear == <year to sort by> then OrderBy YearlyTotals, TotalSales (since YearlyTotals might not exist for a given year, and we still want some type of order in that case). So we also have to consider that there might not be a record for that year to join with (so it needs to be a left join).

If I were writing SQL it would look something like this:

SELECT ST.EmpID, ST.EmpName, ST.TotalSales
FROM SalesTotals AS ST
LEFT JOIN YearlySales AS YS ON ST.EmpID=YS.EmpID
WHERE YS.SalesYear=@SortBySalesYear OR YS.SalesYear IS NULL
ORDER BY YS.YearlySales DESC, ST.TotalSales DESC

I'm not good enough with Linq (yet) to be able to figure this out. In fact, I was able to get virtually no where (maybe trying to do too much at once, perhaps I need to break it down in to individual steps, and not search for the one liner).

So, is there a way to do this with Linq? Or should I be attempting some other type of approach?

Note: All I need is an "in place" sort here. I don't need/want a different type of List<T> returned here, just a sorted List<SalesTotals>.

Edit: I prefer the Linq "Query Syntax" as it is more intuitive to me (strong SQL background). So I prefer an answer using Query Syntax as opposed to Method Syntax.

Edit: Here is a test case setup:

class SalesTotals
{
    public int EmpID { get; set; }
    public string EmpName { get; set; }
    public decimal? TotalSales { get; set; }
}
class YearlySales
{
    public int EmpID { get; set; }
    public short SalesYear { get; set; }
    public decimal? YearlyTotals { get; set; }
}
class TestSort
{
    public TestSort()
    {
        var st = new List<SalesTotals>
        {
            new SalesTotals() { EmpID = 1, EmpName = "Joe", TotalSales = 70 },
            new SalesTotals() { EmpID = 2, EmpName = "Sam", TotalSales = 60 },
            new SalesTotals() { EmpID = 3, EmpName = "Fred", TotalSales = 50 },
            new SalesTotals() { EmpID = 4, EmpName = "Bob", TotalSales = 40 }
        };

        var ys = new List<YearlySales>
        {
            new YearlySales() { EmpID = 1, SalesYear = 2018, YearlyTotals = 20 },
            new YearlySales() { EmpID = 2, SalesYear = 2018, YearlyTotals = 30 },
            new YearlySales() { EmpID = 3, SalesYear = 2018, YearlyTotals = 30 },
            new YearlySales() { EmpID = 4, SalesYear = 2018, YearlyTotals = 10 },
            new YearlySales() { EmpID = 2, SalesYear = 2017, YearlyTotals = 20 },
            new YearlySales() { EmpID = 4, SalesYear = 2017, YearlyTotals = 15 },
            new YearlySales() { EmpID = 1, SalesYear = 2016, YearlyTotals = 10 },
            new YearlySales() { EmpID = 2, SalesYear = 2016, YearlyTotals = 15 },
            new YearlySales() { EmpID = 3, SalesYear = 2016, YearlyTotals = 50 },
            new YearlySales() { EmpID = 4, SalesYear = 2016, YearlyTotals = 20 }
        };

        st = SortByYear(st, ys, 2017);
    }
    private List<SalesTotals> SortByYear(List<SalesTotals> salesTotals, List<YearlySales> yearlySales, short sortYear)
    {
        // return sorted salesTotals by sortYear using both salesTotals and yearlySales joined on EmpID
    }
}         

Answer 1

Rather than convert the SQL directly, I think it is a little clearer to break the query into two parts.

First, find the YearlySales for the year to sort by:

var sortYearSales = from ys in yearlySales
                    where ys.SalesYear == SortBySalesYear
                    select ys;

Then you can left join on that and sort (since ys might by null, I used the null conditional member acecss operator):

var orderedSalesTotals = (from st in salesTotals
                          join ys in sortYearSales on st.EmpID equals ys.EmpID into ysj
                          from ys in ysj.DefaultIfEmpty()
                          orderby ys?.YearSales descending, st.TotalSales descending
                          select st).ToList();

Note: I changed the name of the YearlySales member to YearSales since the C# compiler complained about the member and class having the same name.

You can do it in a single query, but you must either nest the first query into the second, or use lambda syntax in the query:

var orderedSalesTotals = (from st in salesTotals
                          join ys in yearlySales on st.EmpID equals ys.EmpID into ysj
                          from ys in ysj.Where(y => y.SalesYear == SortBySalesYear).DefaultIfEmpty()
                          orderby ys?.YearSales descending, st.TotalSales descending
                          select st).ToList();

Answer 2

You can write it pretty much the same way you would in SQL!

        var results = from t in totals
            join y in years on t.EmpID equals y.EmpID into groupedTable
                      from p in groupedTable.DefaultIfEmpty()
                      where y == null || y.SalesYear == year
                      orderby y.SalesYear, t.TotalSales descending
                      select t;

Quick note: Joins in LINQ are by default inner joins. If you want an outer join, you have to use a DefaultIfEmpty() call.

Answer 3

Kind of works. Need to put in a null for sales

List<YearlySale> YearlySales = new List<YearlySale>() { new YearlySale() { EmpID = 1, Sales = 700, Year = 2018 },
                                                        new YearlySale() { EmpID = 1, Sales = 600, Year = 2017 },
                                                        new YearlySale() { EmpID = 1, Sales = 500, Year = 2016 },
                                                        new YearlySale() { EmpID = 2, Sales = 400, Year = 2018 },
                                                        new YearlySale() { EmpID = 2, Sales = null, Year = 2017 },
                                                        new YearlySale() { EmpID = 2, Sales = 300, Year = 2016 }
                                                        };
List<SalesTotal> SalesTotals = new List<SalesTotal>() { new SalesTotal() { EmpID = 1, EmpName = "stan", TotalSales  = 1800 },
                                                        new SalesTotal() { EmpID = 2, EmpName = "sally", TotalSales = 700 }

                                                        };
var q = from s in SalesTotals
        join y18 in YearlySales 
            on s.EmpID equals y18.EmpID
        join y17 in YearlySales
            on s.EmpID equals y17.EmpID
        join y16 in YearlySales
            on s.EmpID equals y16.EmpID
        where y18.Year == 2018
        where y17.Year == 2017
        where y16.Year == 2016
        select new { SalesTotal = s, Year18 = y18 == null ? 0 : y18.Year, YearS18 = y18 == null ? 0 : y18.Sales
                                   , Year17 = y17 == null ? 0 : y17.Year, YearS17 = y17 == null ? 0 : y17.Sales
                                   , Year16 = y16 == null ? 0 : y16.Year, YearS16 = y16 == null ? 0 : y16.Sales
                    };
foreach (var v in q.OrderBy(x => x.SalesTotal.EmpID))
{
    Debug.WriteLine($"{v.SalesTotal.EmpID} {v.SalesTotal.EmpName} {v.SalesTotal.TotalSales} {v.YearS18} as y18 {v.YearS17} as y17  {v.YearS16} as y16" );
}