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:
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" />
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.
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();