c# - One LINQ statement that will return the answer without breaking it out -

i working on issue need counts , group by's. i've tried @ work, not able in 1 query. of course, can split out , answer, curious if can me find answer in 1 statement?

below code , answer need.

using system; using system.collections.generic; using system.linq; using system.web; using system.web.ui; using system.web.ui.webcontrols;  namespace getcountinfo {     public partial class _default : page     {         protected void page_load(object sender, eventargs e)         {             /*              *               *               * current date be: 7/27/2017 4:00 pm              *              reportoutofdatecountdto              answer be:               patrick = count of 1             stacy = count of 2              */         }          /// <summary>         /// data each year nbr         /// </summary>         /// <returns></returns>         public ilist<yearnbrinfodto> getyearinfodata()         {             ilist<yearnbrinfodto> thedata = new list<yearnbrinfodto>();              thedata.add(new yearnbrinfodto { recordid = 1, yearnbr = 0, createdt = new datetime(2014,1,6) }); // out of date             thedata.add(new yearnbrinfodto { recordid = 1, yearnbr = 1, createdt = new datetime(2014, 5, 6) }); // out of date             thedata.add(new yearnbrinfodto { recordid = 1, yearnbr = 2, createdt = new datetime(2015, 1, 22) }); // out of date             thedata.add(new yearnbrinfodto { recordid = 1, yearnbr = 3, createdt = new datetime(2018, 5, 6) }); // not out of date              // above count of 1 because there 3 out of date, have same record id, 1 record id              thedata.add(new yearnbrinfodto { recordid = 2, yearnbr = 0, createdt = new datetime(2019, 5, 6) }); // not out of date             thedata.add(new yearnbrinfodto { recordid = 2, yearnbr = 1, createdt = new datetime(2018, 1, 6) }); // not out of date              // above not calculated because none of record ids out of date              thedata.add(new yearnbrinfodto { recordid = 5, yearnbr = 0, createdt = new datetime(2014, 3, 3) }); // out of date             thedata.add(new yearnbrinfodto { recordid = 5, yearnbr = 1, createdt = new datetime(2015, 3, 6) }); // out of date             thedata.add(new yearnbrinfodto { recordid = 5, yearnbr = 2, createdt = new datetime(2017, 10, 3) }); // not out of date             thedata.add(new yearnbrinfodto { recordid = 5, yearnbr = 3, createdt = new datetime(2018, 1, 6) }); // not out of date              // above count of 1 because there 2 out of date, have same record id, 1 record id              thedata.add(new yearnbrinfodto { recordid = 6, yearnbr = 0, createdt = new datetime(2014, 3, 3) }); // out of date             thedata.add(new yearnbrinfodto { recordid = 6, yearnbr = 1, createdt = new datetime(2018, 3, 6) }); // not out of date             thedata.add(new yearnbrinfodto { recordid = 6, yearnbr = 2, createdt = new datetime(2019, 1, 3) }); // not out of date             thedata.add(new yearnbrinfodto { recordid = 6, yearnbr = 3, createdt = new datetime(2020, 1, 6) }); // not out of date              // above count of 1 because there 1 out of date              return thedata;         }          /// <summary>         /// ties year record info user ids         /// </summary>         /// <returns></returns>         public ilist<mainrecordinfodto> getmainrecordinfodata()         {             ilist<mainrecordinfodto> thedata = new list<mainrecordinfodto>();              thedata.add(new mainrecordinfodto { userid = 1, recordid = 1 });             thedata.add(new mainrecordinfodto { userid = 1, recordid = 2 });             thedata.add(new mainrecordinfodto { userid = 1, recordid = 3 });             thedata.add(new mainrecordinfodto { userid = 1, recordid = 4 });               thedata.add(new mainrecordinfodto { userid = 2, recordid = 5 });             thedata.add(new mainrecordinfodto { userid = 2, recordid = 6 });              return thedata;         }          /// <summary>         /// main user info...         /// </summary>         /// <returns></returns>         public ilist<userinfodto> getuserinfodata()         {             ilist<userinfodto> thedata = new list<userinfodto>();              thedata.add(new userinfodto { name = "patrick", userid = 1 });             thedata.add(new userinfodto { name = "stacy", userid = 2 });              return thedata;         }     }      public class reportoutofdatecountdto     {         public string name { get; set; }          /// <summary>         /// count of out of date per recordid         /// </summary>         public int countofoutofdateyearnbrs { get; set; }      }      public class yearnbrinfodto     {          public int recordid { get; set; }          public int yearnbr { get; set; }          public datetime createdt { get; set; }     }      public class mainrecordinfodto     {         public int userid { get; set; }          public int recordid { get; set; }     }      public class userinfodto     {         public string name { get; set; }         public int userid { get; set; }     } } 

you can join relational data shorter , more efficient query:

var result = getyearinfodata()     .where(d => d.createdt < datetime.now)     .select(x => x.recordid)                                   // recordids 1, 1, 1, 5, 5, 6     .distinct()                                                         // recordids 1, 5, 6     .join(getmainrecordinfodata(), => i, r => r.recordid, (i, r) => r.userid)  // userids 1, 2, 2     .join(getuserinfodata()      , => i, u => u.userid  , (i, u) => u.name  )  // names { patrick, stacy, stacy }     .groupby(n => n)     .select(g => new { name = g.key, count = g.count() });  // names , counts 

---

if data not database, can optimized more creating lookups before query:

var yearlookup = getyearinfodata().tolookup(y => y.recordid); var recordlookup = getmainrecordinfodata().tolookup(r => r.userid);  var result = getuserinfodata().select(u => new { u.name, count = recordlookup[u.userid]     .count(i => yearlookup[i.recordid].any(d => d.createdt < datetime.now)) });