Linq Query: Group By

Question

I have 2 tables with the following fields:

Person

ID, LastName, FirstName

Assignment

PersonID, AssignmentDate, OtherField

Let's say the table data looks like this:

Person

1, Smith, John

2, Allen, James

3, Stone, Emma

Assignment

1, 12/1/2016, Blue

1, 1/1/2017, Green

1, 1/1/2018, Red

2, 5/5/2015, Yellow

I'm building a report, and the logic states that...

if the AssignmentDate's YEAR ends in an EVEN number, report the even number (if year is 2014, report 2014).
If the AssignmentDate's year ends in an ODD number, report the year minus 1 (i.e., year is 2015, report 2014).

In addition, group by PersonID followed by that "even" AssignedYear. Include all persons and all assignments. (I wasn't sure if I should be using Group By or Distinct. Confused on this part.)

I need it to output these results based off the example table:

1, 2016

1, 2018

2, 2014

3, null (since personID = 3 exists, but there are no assignments, we still want to incorporate this person)

How would you do that? I've gotten this far, but got stuck. :(

var t = from p in tblPersons
        join a in tblAssignments
             on p.ID equals a.PersonID into fml
        from x in fml
        select new AssignmentFilters
              {
                  PersonID = x.PersonID,
                  Person= x.tblPersons.LastName + ", " + x.tblPersons.FirstName +
                       (string.IsNullOrEmpty(x.tblPersons.PreferredName) ? "" : " (" + x.tblPersons.PreferredName + ")") +
                       (string.IsNullOrEmpty(x.tblPersons.MiddleName) ? "" : " " + x.tblPersons.MiddleName) +
                       (string.IsNullOrEmpty(x.tblPersons.Suffix) ? "" : " " + x.tblPersons.Suffix),
                  AuditStartYear = x.DateAssigned == null ? 0 : (x.DateAssigned.Year % 2 == 0 ? x.DateAssigned.Year : x.DateAssigned.Year - 1)
               };

Any help would be greatly appreciated! Been working on this for the past few hours and am just frustrated/stumped, lol. Thank you!

What does your current code do now (i.e. which bits are working, which aren't)? — mjwills, Dec 07 '17 at 20:54
https://stackoverflow.com/questions/3404975/left-outer-join-in-linq shows you how to do a left outer join (to get null). — mjwills, Dec 07 '17 at 20:55
@mjwills No errors with the above. The query brings back everything though. It's like an outer join. It yields at least 4 records (2 of 1, 2016; 1 of 1, 2018; 1 of 2, 2014). It might yield a 5th (for 3, null), but I haven't tested that hehe. It's when I attempt to start grouping things up that everything goes bonkers. ETA: If I throw in a group by, I can do it if it's an anonymous type object. But I need it to be a specific class that I've created, and so then it fails there. Or if I attempt to add in the statement expression into the select clause to... idk, it's just a mess when I try lol. — ktstar, Dec 07 '17 at 20:58
@mjwills I'm not having issues with the join. I'm having issues attempting to group by when there's a field that is a statement expression and when I need to specify the class type. Not sure if I can cast this list to a specified list type. — ktstar, Dec 07 '17 at 21:01
See msdn left outer join : https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — jdweng, Dec 07 '17 at 22:57

score 0 · Answer 1 · answered Dec 08 '17 at 09:15

isolate the audits per person based on the specific rules you mention

var audits = from assignment in tblAssignments
             select new
             {
                 PersonId = assignment.PersonID,
                 Year = assignment.DateAssigned == null
                     ? 0
                     : assignment.DateAssigned.Year % 2 == 0
                         ? assignment.DateAssigned.Year
                         : assignment.DateAssigned.Year - 1
             };

iterate over the persons and left outer join on the distinct of audits

var results = from person in tblPersons
            from audit in
            (
                from a in audits.Distinct()
                where a.PersonId == person.ID
                select a
            ).DefaultIfEmpty()
            select new
            {
              Person = person,
              Audit = audit,
            };

this should give you something like this and then you can project the results even further in whatever shape you need

Linq Query: Group By

1 Answers1