title: Return array from Linq SQL query on 80 million row table using EFCore
body: <p>I'm querying a SQL database using Linq and Entity Framework Core in a Razorpages project to generate a residency plot, <a href="https://i.stack.imgur.com/USPzP.jpg" rel="nofollow noreferrer">here's one that I made earlier.</a></p>
  
  <p>I'm struggling to optimise this query despite many attempts and iterations, it is slow and it often times out. I need the final array of the Count() values that make up each square of the residency plot and am not interested in the raw data.</p>
  
  <p>The data are from a table with ~80 million rows and I've found solutions from SO which might work with fewer entries but that aren't suitable in this use case (generally searching Linq, group, join). I think the problem is the combination of filters, groups and joins followed by a count occurring server-side without first downloading the raw data.</p>
  
  <p>Reviewing the SQL command in SSMS (pulled from LINQPad) it is very poorly optimised - I can post this if it would be useful but it's 236 lines long made up of repeated sections.</p>
  
  <p>The Linq I've hobbled together performs the required operation in the 4 steps outlined here.</p>
  
  <p><strong>Step 1</strong> (rows between a certain time, with a certain LocationTypeId, and a channelId = engSpeed):</p>
  
  <pre><code>var speedRows = context.TestData
  .Where(a =&gt; a.Time &gt;= start
  &amp;&amp; a.Time &lt; end
  &amp;&amp; a.LocationTypeId == 3
  &amp;&amp; a.channelId == 7)
  .Select(s =&gt; new
  {
      s.Time,
      s.ChannelValue
  })
  .Distinct();
  </code></pre>
  
  <p><strong>Step 2</strong> (rows with a channelId = torque):</p>
  
  <pre><code>var torqueRows = context.TestData
  .Where(a =&gt; a.LocationTypeId == 3
  &amp;&amp; a.channelId == 8)
  .Select(s =&gt; new
  {
      s.Time,
      s.ChannelValue
  })
  .Distinct();
  </code></pre>
  
  <p><strong>Step 3</strong> (join the speed and torque rows from <strong>Step 1</strong> and <strong>Step 2</strong> on Time):</p>
  
  <pre><code>var joinedRows = speedRows.Join(torqueRows, arg =&gt; arg.Time, arg =&gt; arg.Time,
      (speed, torque) =&gt; new
      {
          Id = speed.Time,
          Speed = Convert.ToDouble(speed.ChannelValue),
          Torque = Convert.ToInt16(torque.ChannelValue)
      });
  </code></pre>
  
  <p><strong>Step 4</strong> (create the dynamic groupings using the joined table from <strong>Step 3</strong>):</p>
  
  <pre><code>var response = (from a in joinedRows
              group a by (a.Torque / 100) into torqueGroup
              orderby torqueGroup.Key
              select new
              {
                  TorqueBracket = $"{100 * torqueGroup.Key} &lt;&gt; {100 + (100 * torqueGroup.Key)}",
                  TorqueMin = 100 * torqueGroup.Key,
                  TorqueMax = 100 + (100 * torqueGroup.Key),
                  Speeds = (from d in torqueGroup
                              group d by (Math.Floor((d.Speed) / 500)) into speedGroup
                              orderby speedGroup.Key
                              select new
                              {
                                  SpeedBracket = $"{500 * speedGroup.Key} &lt;&gt; {500 + (500 * speedGroup.Key)}",
                                  SpeedMin = 500 * (int)speedGroup.Key,
                                  SpeedMax = 500 + (500 * (int)speedGroup.Key),
                                  Minutes = speedGroup.Count()
                              })
              }).ToList();
  </code></pre>
  
  <p>I could be missing something obvious but I've tried many attempts and this is the best I've got.</p>
  
  <p>The <code>TestData</code> class:</p>
  
  <pre><code>public partial class TestData {
      public int LiveDataId { get; set; }
      public DateTime? Time { get; set; }
      public int? LocationTypeId { get; set; }
      public int? TestNo { get; set; }
      public int? LogNo { get; set; }
      public int? LiveDataChannelId { get; set; }
      public decimal? ChannelValue { get; set; }
      public virtual LiveDataChannelNames LiveDataChannel { get; set; }
      public virtual LocationType LocationType { get; set; }
  }
  </code></pre>
  
  <p>Any help or pointers would be appreciated.</p>
  
  <p>Thank you.</p>