Can value of columns be lazy loaded in EntityFramework?

Question

There is a table in the database that has columns Id, Name, Group, and Text. and sizes of values in the Text column are big (about 1MB to 3 MB).

If I use EntityFramework to update one row

public async Task UpdateName(int id, string name, CancellationToken cancellationToken)
{
    var record = await dbContext.TheTables.FirstOrDefaultAsync(t => t.Id == id, cancellationToken) 
        ?? throw new ObjectNotFoundException();
    
    record.Name = newName;
    dbContext.SaveChangesAsync(cancellationToken);
}

This can be expensive to run since the size of the Text column is big.

The query like dbContext.TheTables.Where(t => t.Group == "TheGroupName") can be even more expensive!

My question is "Is there a cheaper way to query data and/or update the data??? Can Text be lazy-loaded and how?"

Answer 1

AFAIK EF doesn't support lazy loading value properties, where as I recall NHibernate does have this capability.

The typical recommended relational approach when you have expensive, but seldom needed fields is to split those fields off to use a one-to-one related table. I.e. TheTable.TheTableDetails which is where your expensive fields would live.

This way you can work efficiently with the base table entity and only hit the expensive stuff when needed. (Typically one record at a time)

Note that in your example you are missing an await on your SaveChangesAsync() call as well.

When reading information, leverage projection to exclude expensive fields/relationships rather that returning entities and either eager loading or risking a lazy load call fetching expensive values.

There is also a more "hacky" option that I wouldn't recommend except as possibly a last resort:

public async Task UpdateName(int id, string name, CancellationToken cancellationToken)
{
    var recordExists = dbContext.TheTables.Any(x => x.Id == id);
    if (!recordExists) throw new ObjectNotFoundException();
    
    var record = dbContext.TheTables.Local.SingleOrDefault(x => x.Id == id);
    if (record != null)
        record.Name = newName;
    else
    {
        record = new Record { Id = id, Name = newName };
        dbContext.Attach(record);
        dbContext.Entry(record).Property(x => x.Name).IsModified = true;
    }
    await dbContext.SaveChangesAsync(cancellationToken);
}

What this essentially does is check that the record exists without loading it. We then check the local cache in case the DbContext is already tracking it, if so we set the Name. If it isn't tracked we create a new minimal instance with the ID and the new name, marking the name as modified.

Such an approach should be used as a last resort and carefully as the "record" instance could be either a complete tracked entity instance, or a tracked incomplete instance. Later modifications that pass that entity onwards or return the entity would lead to potential runtime bugs. Realistically if that stub of an entity is attached and updated, it should be detached and disposed of after the SaveChanges() call.