title: Linq to Sql query self join with grouping c#
body: <p>I have following tables</p>
  <p>Machine table</p>
  <pre><code>public class MachineUpdate : DbContext
  {
      public int MachineUpdateId { get; set;}
      public int MachineId { get; set;}
      public string MachineName { get; set; }
      public Datetime PublishDate { get; set; }
      public string PublishedBy { get; set; }
      public bool IsCorrect { get; set; }
      public decimal Version { get; set; }
  }
  
  public class DetailedMachine : DbContext
  {
      public int DetailedMachineId { get; set;}
      public int MachineUpdateId { get; set;}
      public string ConfigurationInfo { get; set; }
  }
  </code></pre>
  <p>Each machine will have multiple updates that becomes multiple versions. And each machine will have list of detailed information</p>
  <p>Data can look like this</p>
  <pre><code>MachineUpdateId     MachineId     MachineName     PublishDate     PublishedBy     Version   IsCorrect
  1                   1             machine1        12/01/2022      user1        1.00         1
  2                   1             machine1        12/02/2022      user1        1.01         1
  3                   2             machine2        15/01/2022      user2        1.00         1
  </code></pre>
  <p>So in the first table we have 2 machines machine1 and machine2 where machine1 have 2 versions and machine2 having 1</p>
  <p>Then there is detailed table which represents some extra info for every update for a machine</p>
  <pre><code>DetailedMachineId   MachineUpdateId      ConfigurationInfo   
  1                   1                    blah blah got changed
  2                   1                    blah blah got changed
  </code></pre>
  <p>So for MachineUpdateId 1 there are detailed info present. Therefore for each machineupdateid there can be multiple or none detailedmachine info.</p>
  <p>Now I want to show the above structure like below</p>
  <pre><code>public class MachineData
  {
      public int MachineUpdateId { get; set;}
      public string PublishedBy { get; set; }
      public int VersionCount { get; set; }
      public List&lt;Version&gt; Versions { get; set; }
      public List&lt;DetailedMachine&gt; DetailedMachine { get; set; }
  }
  
  public class Version
  {
      public int MachineUpdateId { get; set;}
      public string MachineName { get; set; }
      public Datetime PublishDate { get; set; }
      public string PublishedBy { get; set; }
      public decimal Version { get; set; }
  }
  
  public class DetailedMachine
  {
      public int MachineUpdateId { get; set;}
      public string ConfigurationInfo { get; set; }
  }
  </code></pre>
  <p>Therefore, data get will be List. Each machine data will have multiple versions  which will be in List and each machine data will have detailed info as well.
  Therefore we will need to group by MachineId and put it in List using self join and also detailed information in List.</p>
  <p>I have written below query which is grouping the version data but giving wrong total data. As per the above data the main list should have 2 records and first record should have 2 version and second record should have 1 version along with detailed machine data for both.</p>
  <pre><code>(from machineUpdate in _dbContext.MachineUpdate 
                      where machineUpdate.IsCorrect 
                      select new MachineData
                      {
                          MachineUpdateId = machineUpdate.MachineUpdateId,
                          PublishedBy = machineUpdate.PublishedBy,
                          **VersionCount = ??,**
                          Versions = (from version in _dbContext.MachineData
                                      where machineUpdate.MachineId == version.MachineId &amp;&amp; version.IsCorrect
                                      select new Version
                                      {
                                          MachineUpdateId = version.MachineUpdateId,
                                          MachineName = version.MachineName ,
                                          PublishDate  = version.PublishDate,
                                          Version = version.Version
                                      }).ToList(),
                          DetailedMachine =
                             (from dm in _dbContext.DetailedMachine 
                              where machineUpdate.MachineUpdateId  == dm.MachineUpdateId 
                              select new DetailedMachine
                              {
                                  MachineUpdateId = dm.MachineUpdateId,
                                  ReleasedDataSetId = dm.ReleasedDataSetId
                              }).ToList()
                      }).ToList();
  </code></pre>