title: EF Core 2.0/2.1 - How to efficiently handle large, infrequently accessed columns?
body: <p>I have a table as follows:</p>
  
  <pre><code>CREATE TABLE MyTable
  (
    ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL, 
    LARGEBLOB VARBINARY(MAX) NULL
  )
  </code></pre>
  
  <p>With an Entity defined as:</p>
  
  <pre><code>public class Entity
  {
    public int Id {get;set;}
    public string Name {get;set;}
    public virtual byte[] LargeBlob {get;set;}
  }
  </code></pre>
  
  <p>99% of my use cases involve displaying ID and NAME only.</p>
  
  <p>1% of the time I need LARGEBLOB.</p>
  
  <blockquote>
    <p><strong>Is there any way I can mark LargeBlob as Lazily Loaded so to avoid
    huge wasted data transfers? Alternatively, are there other ways of
    achieving the same outcome?</strong></p>
  </blockquote>
  
  <p>I tried splitting into 2 tables with a 1->[0|1] relationship as follows:</p>
  
  <pre><code>CREATE TABLE MyTable
  (
    ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL, 
    LARGEBLOBID INT NULL
  )
  
  CREATE TABLE MySubTable
  (
    ID INT PRIMARY KEY,
    LARGEBLOB VARBINARY(MAX) NOT NULL
  )
  </code></pre>
  
  <p>with entities</p>
  
  <pre><code>public class Entity
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public virtual LargeBlob LargeBlob { get; set; }
  }
  
  public class LargeBlob
  {
      public int Id { get; set; }
      public virtual byte[] Blob { get; set; }
  }
  </code></pre>
  
  <p>That did work in so far as lazy loading was concerned, but I tried all manner of inverse relationship / foreign key tags, HasOne, OwnsOne, OnDelete(Cascade) in all kinds of combinations, but I couldn't achieve what I wanted to achieve.  Just to recap, that would be:</p>
  
  <ol>
  <li>Blob is loaded only when the LargeBlob property is actually derefenced.</li>
  <li>If entity.LargeBlob property gets set to a new LargeBlob, the (now "orphaned" ) old LargeBlob gets deleted from the database.</li>
  <li>If the entity gets deleted, the related large blob gets deleted.</li>
  </ol>
  
  <p><strong>Quick Update re: versions &amp;c</strong></p>
  
  <p>Note: I'm using VS 2017 15.6.2, .net core 2.0, with EF core 2.1 (to get at least the possibility of some lazy loading).  Nuget packages: </p>
  
  <pre><code>&lt;PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.6" /&gt;
  &lt;PackageReference Include="Microsoft.EntityFrameworkCore.Proxies" Version="2.1.0-preview1-final" /&gt;
  &lt;PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.1.0-preview1-final" PrivateAssets="All" /&gt;
  </code></pre>