title: Advanced LINQ Grouping and Projection Query for Hierarchical Data (EF 4.0 + LINQ + ASP.NET MVC + HighCharts)
body: <h1>Problem overview:</h1>
  
  <p>I have database tables that describe data gathering from multiple devices using web services into centralized database. </p>
  
  <p>The table that stores results is deliberately de-normalized for performance: faster querying and grouping by multiple indexes.
  <strong>I am using Entity Framework and Linq for data access.</strong></p>
  
  <blockquote>
    <p>I need to properly <strong>design Linq queries</strong> with <strong>hierarchical grouping and
    projection</strong>.</p>
  </blockquote>
  
  <hr>
  
  <h1>Devices database modeling overview:</h1>
  
  <p>Currently I have 2 types of devices</p>
  
  <h2>1. Rfid devices</h2>
  
  <p>First table is RfidTag that describes tag which gathers data, <strong>1 single RfidTag = 1 single sensor</strong>. So for example 1 single tag can get data about temperature.</p>
  
  <p>Second table is RfidReader that describes reading module which gather and send all data from attached tags to it. There is no restriction about quantity of attached RfidTags to single RfidReader. <strong>However one single RfidTag can be attached to one single RfidReader during reading period.</strong></p>
  
  <pre><code>CREATE TABLE [dbo].[RfidTag]
  (
      Id INT IDENTITY(1,1),
      CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(),
      ModifiedDateTime DATETIME,
      --
      ReaderId INT NULL,                  -- Reference to reader
      SensorTypeId INT NOT NULL,          -- Reference to sensor type
      SensorParameters NVARCHAR(50) NULL, -- Sensor parameters
      Hex  NVARCHAR(50) NOT NULL,         -- Hex tag identifier stored as string
      Name NVARCHAR(50) NOT NULL,         -- Tag name
      [Description] NVARCHAR(200) NULL,   -- Tag description
      --
  )
  
  CREATE TABLE [dbo].[RfidReader]
  (
      Id INT IDENTITY(1,1),
      CreatedDateTime  DATETIME NOT NULL DEFAULT GETDATE(),
      ModifiedDateTime DATETIME,
      --
      Name        NVARCHAR(20),        -- Tag name
      [Description]   NVARCHAR(200),   -- Tag description                                             
      SerialNumber    NVARCHAR(12),    -- Unique device serial name               
      --
  )
  </code></pre>
  
  <p>Each RfidReader <strong>can be attached to specific measurement zone</strong> that is <strong>described for specific construction</strong>.</p>
  
  <pre><code>CREATE TABLE [dbo].[RfidReaderPlacement]
  (
      Id INT IDENTITY(1,1),
      CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(),
      --
      ReaderId INT NOT NULL,              -- Reference to Reader.
      MeasurementZoneId INT NOT NULL,     -- Reference to Measurement Zone.
      StartDate DATETIME NOT NULL,        -- Start date of reading.
      StopDate  DATETIME,                 -- End date of reading.
      --
  )
  </code></pre>
  
  <p><strong>Single data gathered by RfidTag</strong> is saved in de-normalized table. This table stores millions of records and is really heavy loaded. <strong>From this table we will be gathering data using LINQ queries.</strong></p>
  
  <pre><code>CREATE TABLE [dbo].[RfidReading]
  (
      Id INT IDENTITY(1,1),
      CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(),
      ModifiedDateTime DATETIME,
      --
      ReaderPlacementId INT NOT NULL, -- Reference to Rfid Reader Placement
      ConstructionId  INT NOT NULL,   -- Reference to Construction
      MeasurementZoneId INT NOT NULL, -- Reference to Measurement Zone
      ReaderId INT NOT NULL,          -- Reference to Rfid Reader
      TagId    INT NOT NULL,          -- Reference to Rfid Tag
      SensorTypeId INT NOT NULL,      -- Reference to Sensor Type
      ReadingDate DATETIME NOT NULL,  -- Reading date
      Value FLOAT NOT NULL            -- Measured value
      --
  )
  </code></pre>
  
  <h2>2. ZigBee devices</h2>
  
  <p>First table is ZigBeeNodeProbe that describes single probe that gathers data, <strong>1 single ZigBeeNodeProbe = 1 single sensor</strong>. So for example 1 single probe can get data about temperature.</p>
  
  <p>Second table is ZigBeeNode that describes single device that contains attached probes, <strong>1 single ZigBeeNode = 3 ZigBeeNodeProbes</strong>.</p>
  
  <p>Third table is ZigBeeReader that describes reading module that gather and send all data from attached nodes to it. There is no restriction about quantity of attached ZigBeeNodes (that has attached ZigBeeNodeProbes) to single ZigBeeReader. <strong>However one single ZigBeeNode can be attached to one single ZigBeeReader during reading period.</strong></p>
  
  <pre><code>CREATE TABLE [dbo].[ZigBeeNodeProbe]
  (
      Id INT IDENTITY(1,1),
      CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(),
      --
      NodeId INT NULL,                    -- Reference to node
      SensorTypeId INT NOT NULL,          -- Reference to sensor type
      SensorParameters NVARCHAR(50) NULL, -- Sensor parameters
      SocketNumber  INT NOT NULL,         -- Socket number used in parent ZigBeeNode
      Name NVARCHAR(50) NOT NULL,         -- Node name
      [Description] NVARCHAR(200) NULL,   -- Node description
      --
  )
  
  CREATE TABLE [dbo].[ZigBeeNode]
  (
      Id INT IDENTITY(1,1),
      CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(),
      ModifiedDateTime DATETIME,
      --
      ReaderId INT NULL,                    -- Reference to reader
      NetworkAddress  NVARCHAR(50) NOT NULL,-- Node address in ZigBee network
      Name NVARCHAR(50) NOT NULL,           -- Given name
      [Description] NVARCHAR(200) NULL,     -- Tag description
      SocketCount INT NOT NULL DEFAULT 0,   -- Count of available sockets to plug in probe
      NodeFrequency INT NULL,               -- Node frequency
  )
  
  CREATE TABLE [dbo].[ZigBeeReader]
  (
      Id INT IDENTITY(1,1),
      CreatedDateTime  DATETIME NOT NULL DEFAULT GETDATE(),
      ModifiedDateTime DATETIME,
      --
      Name        NVARCHAR(20),        -- Tag name
      [Description]   NVARCHAR(200),   -- Tag description                                             
      SerialNumber    NVARCHAR(12),    -- Unique device serial name               
      --
  )
  </code></pre>
  
  <p>Each ZigBeeReader <strong>can be attached to specific measurement zone</strong> that is <strong>described for specific construction</strong>.</p>
  
  <pre><code>CREATE TABLE [dbo].[ZigBeeReaderPlacement]
  (
      Id INT IDENTITY(1,1),
      CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(),
      --
      ReaderId INT NOT NULL,              -- Reference to Reader.
      MeasurementZoneId INT NOT NULL,     -- Reference to Measurement Zone.
      StartDate DATETIME NOT NULL,        -- Start date of reading.
      StopDate  DATETIME,                 -- End date of reading.
      --
  )
  </code></pre>
  
  <p><strong>Single data gathered by ZigBeeNodeProbe</strong> is saved in de-normalized table. This table stores millions of records and is really heavy loaded. <strong>From this table we will be gathering data using LINQ queries.</strong></p>
  
  <pre><code>CREATE TABLE [dbo].[ZigBeeReading]
  (
      Id INT IDENTITY(1,1),
      CreatedDateTime DATETIME NOT NULL DEFAULT GETDATE(),
      ModifiedDateTime DATETIME,
      --
      ReaderPlacementId INT NOT NULL, -- Reference to ZigBee Reader Placement
      ConstructionId    INT NOT NULL, -- Reference to Construction
      MeasurementZoneId INT NOT NULL, -- Reference to Measurement Zone
      ReaderId      INT NOT NULL,     -- Reference to ZigBee Reader
      NodeId        INT NOT NULL,     -- Reference to ZigBee Node
      ProbeId       INT NOT NULL,     -- Reference to ZigBee Node Probe
      SensorTypeId      INT NOT NULL, -- Reference to Sensor Type
      ReadingDate   DATETIME NOT NULL,-- Reading date
      Value         FLOAT NOT NULL    -- Measured value
      --
  )
  </code></pre>
  
  <hr>
  
  <h1>Quering, grouping and projection issues:</h1>
  
  <p>As you may see above, we have 2 de-centralized tables that contains data gathered by 2 mentioned types of devices. <strong>And yes we may assume that RfidTag is in business modeling almost the same as ZigBeeNodeProbe.</strong></p>
  
  <pre><code>RfidReader
  -- RFidTag
  -- RFidTag
  ...
  
  ZigBeeReader
  -- ZigBeeNode
  ---- ZigBeeNodeProbe
  ---- ZigBeeNodeProbe
  ---- ZigBeeNodeProbe
  -- ZigBeeNode
  ---- ZigBeeNodeProbe
  ---- ZigBeeNodeProbe
  ---- ZigBeeNodeProbe
  ...
  </code></pre>
  
  <p>And now we have to <strong>query both of these tables</strong>, project values from different tables into the same view models, and add specific grouping for filtering data.</p>
  
  <h2>Common scenario:</h2>
  
  <p>We want to create chart that will present average temperature <strong>in specific measurement zone</strong>, remember that specific measurement zone may contain multiple attached readers (both Rfid and ZigBee) sow we have to provide series of data.</p>
  
  <p>I am using HightStock <a href="http://www.highcharts.com/products/highstock" rel="nofollow">http://www.highcharts.com/products/highstock</a> chart that must have enabled:</p>
  
  <ul>
  <li>Zoom for 1 month, 3 months, 6 months, etc.</li>
  <li>From, To date period</li>
  <li>Export features</li>
  <li>Legend with enabling and disabling series</li>
  </ul>
  
  <p>Example of chart:</p>
  
  <p><a href="http://jsfiddle.net/hNHUY/1/" rel="nofollow">http://jsfiddle.net/hNHUY/1/</a></p>
  
  <h2>Issue:</h2>
  
  <p><strong>How to create LINQ Grouping and Projection Query for Hierarchical Data?</strong> 
  <strong>We need to provide common view model for both RfidReading and ZigBeeReading tables.</strong></p>
  
  <p>My first attempt was with something like:</p>
  
  <pre><code>public class ReadingReaderDataModel
  {
      public string SeriesName { get; set; }
      public ReadingPeriod ReadingPeriod { get; set; }
  
      public IEnumerable&lt;ReadingNodeDataModel&gt; ReadingNodeDataModels { get; set; }
      public ReadingReaderDataModel()
      {
          ReadingNodeDataModels = new List&lt;ReadingNodeDataModel&gt;();
      }
  }
  
  public class ReadingNodeDataModel
  {
      public string NodeName { get; set; }
      public IEnumerable&lt;double&gt; DataValues { get; set; }
      public ReadingNodeDataModel()
      {
          DataValues = new LinkedList&lt;double&gt;();
      }
  }
  
  public enum ReadingPeriod
  {
      OneMonth, ThreeMonths, SixMonths, YearToDay, OneYear, All
  }
  
  public enum ReaderType
  {
      Rfid, ZigBee
  }
  </code></pre>
  
  <p>Later on I have to design LINQ projection for ASP.NET MVC controller, and here I have no idea how to create proper query that must contain average value from time period grouped by OneMonth, ThreeMonths, SixMonths, YearToDay, OneYear, All.</p>
  
  <p>Can anyone help me design this advanced LINQ query ?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Please do not point me any "recommendations" for large datasets... </p>
  
  <p>This is not question about performance. </p>
  
  <p>This question is about complex LINQ query. I am seeking proper LINQ code and NOT answers like "that car is faster and you should try to use that car"... Code answers please... If you do not even try to understand scenario, and provide any code, do not participate...</p>
  
  <p>I specifically added a bounty to find a solution for LINQ, since it is advanced LINQ question as in the subject of this question. </p>