EF Core 2.0/2.1 - How to efficiently handle large, infrequently accessed columns?

Question

EF Core 2.0/2.1 - How to efficiently handle large, infrequently accessed columns?

ef-core-2.0 entity-framework entity-framework-core

Question

I have a table as follows:

CREATE TABLE MyTable
(
  ID INT NOT NULL PRIMARY KEY,
  NAME VARCHAR(50) NOT NULL, 
  LARGEBLOB VARBINARY(MAX) NULL
)

With an Entity defined as:

public class Entity
{
  public int Id {get;set;}
  public string Name {get;set;}
  public virtual byte[] LargeBlob {get;set;}
}

99% of my use cases involve displaying ID and NAME only.

1% of the time I need LARGEBLOB.

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?

I tried splitting into 2 tables with a 1->[0|1] relationship as follows:

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
)

with entities

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; }
}

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:

Blob is loaded only when the LargeBlob property is actually derefenced.
If entity.LargeBlob property gets set to a new LargeBlob, the (now "orphaned" ) old LargeBlob gets deleted from the database.
If the entity gets deleted, the related large blob gets deleted.

Quick Update re: versions &c

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:

<PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.6" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Proxies" Version="2.1.0-preview1-final" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.1.0-preview1-final" PrivateAssets="All" />

ubienewbie

Popular Answer

You should only select the columns you need to save bandwidth:

var entity = await dbContext.Entities
  .Where(...)
  .Select(e => new
  {
    Id = e.Id,
    Name = e.Name,
    LargeBlob = null,
  })
  .FirstOrDefaultAsync();

and whenever you really need the LargeBlob column, load it manually

entity.LargeBlob = await dbContext.Entities
  .Where(e => e.Id == entity.Id)
  .Select(e => e.LargeBlob)
  .SingleOrDefaultAsync();

You can delete an entity without loading the whole entity, just the Id (and the concurrency token, if present on the entity) suffices

var entity = new Entity { Id = removeEntityId };
dbContext.Entities.Remove(entity);
await dbContext.SaveChangesAsync();

Lars

View more on Stack Overflow

Ivan Stoev · Accepted Answer · 3/25/2018 10:54:19 AM

I tried splitting into 2 tables with a 1->[0|1] relationship as follows

But by putting the FK in the Entity you actually did the opposite - [0|1]->1 relationship.

To get the desired relationship, the FK must be at LargeBlog. It could be a separate property (column), but the most appropriate is to use the Id property as both PK and FK (the so called shared PK association). You can do it with the following fluent configuration:

modelBuilder.Entity<Entity>()
    .HasOne(e => e.LargeBlob)
    .WithOne()
    .HasForeignKey<LargeBlob>(e => e.Id);

Once you do that, since the whole purpose of doing it was to get separate controllable (eager, explicit or lazy when available) load behavior, it can be seen that the separate table is not really needed - the "entity" containing the blob data can be embedded inside the same table using the table splitting which is achieved by simply adding the following to the above configuration:

modelBuilder.Entity<Entity>().ToTable("MyTable");
modelBuilder.Entity<LargeBlob>().ToTable("MyTable");

Note that while the most logical choice seems to be owned type, unfortunately currently owned types are always loaded (similar to EF6 complex types), so they cannot be used to achieve controllable load behavior.

EF Core 2.0/2.1 - How to efficiently handle large, infrequently accessed columns?

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related