EF Core. How to load only necessary properties from deep nested entity

Question

I develop a simple application, something like a chat where every message may contain text and files. Entities are related like this:

Message group -> every message group has a collection of messages -> every message has a property 'FileCollection' -> 'File collection' has 4 collections: Images, Video, Audio, Files. All of them has the same relations in database. To show this logic here is my query to get all message groups with their entities:

var messageGroups = await _db.MessageGroups
    .Where(mg => mg.UserId == id)
    .Include(m => m.Messages).ThenInclude(mes => mes.FileCollection.Images)
    .Include(m => m.Messages).ThenInclude(mes => mes.FileCollection.Video)
    .Include(m => m.Messages).ThenInclude(mes => mes.FileCollection.Audio)
    .Include(m => m.Messages).ThenInclude(mes => mes.FileCollection.Files)
    .ToListAsync();

The problem is that every type of file (Image, Audio etc.) has a 'Data' column in Db (property in EF Core) which contains their blob data. I want to exclude all blob from query, because query becomes extremely heavy loading all user files from Db. Something like this (but exclude method does not exist):

.Include(m => m.Messages).ThenInclude(mes => mes.FileCollection.Video).exclude(video => video.Data);

Is there any way to use explicit loading at the end of the query? Or maybe there are attributes like [JsonIgnore] which excludes class property from Json serializing? Or any other method?

If it helps: ImageFile, AudioFile and others inherit from File super class:

public class File
{
    [Column("id")]
    public int Id { get; set; }

    [Column("content_type")]
    public string ContentType { get; set; }

    [Column("file_name")]
    public string FileName { get; set; }

    [Column("length")]
    public long Length { get; set; }

    [Column("related_file_collection_id")]
    public int FileCollectionId { get; set; }

    public FileCollection FileCollection { get; set; }
}


public class ImageFile : File
{
    [Column("data")]
    public byte[] Data { get; set; }

}

I need all properties from 'File' class without 'Data' property from it's child classes.

I believe this is a duplicate question. This post provides two methods (split table and projection) for handling this situation. https://stackoverflow.com/questions/3274241/entity-framework-4-selective-lazy-loading-properties — Dan Barrett, Mar 02 '21 at 16:47
It's a good idea to send 'Data' column to it's own table. It can be the answer, thank you. But I believe it's not the most elegant way. Or EF Core is not so powerful to solve the problem. — User, Mar 02 '21 at 16:53
Can you please include the class definition for FileCollection as well as any Fluent API model configurations for FileCollection. — Dan Barrett, Mar 02 '21 at 18:33

score 1 · Answer 1 · answered Mar 02 '21 at 22:54

I believe the best way would be to configure your DbContext for those entities containing Blob columns using Table Splitting.

Don't let the name confuse you. This technique is not to move the Blob to a different table. Instead, it will allow you to fit two "entities" on the same row. In your case, you could split your File from your FileData, meaning that you will have a different entity for each of them, but both will be stored on the same row on the same table.

By using table splitting, you can .Include your File and it won't include the FileData unless you explicitly tell EF Core to do it.

If you don't wanna go down that road, I believe that you would either need to write some custom Selects or custom SQL.

kkica · Answer 2 · 2021-03-02T21:30:14.350

0

You can use the [NotMapped] attribute but then you will not be able to retrive that column from the db from other queries.

You can also create a DTO and select only the required properties, but that would not be elegant considering all your includes.

edited Mar 02 '21 at 21:30

answered Mar 02 '21 at 16:43

kkica

4,034
1
20
40

I know, but I need that column in Db. It are user files, they send them in messages and they need it. With [NotMaped] there won't be such column in Db and files will be not saved. – User Mar 02 '21 at 16:45
1

The solution is not to insist on EF too much. Use queries ! – Ergis Mar 02 '21 at 16:56

score 0 · Answer 3 · answered Mar 04 '21 at 13:19

As it was advised here, Table splitting can be the answer, but it is a little complicated. I've just modified my query using .Select(). Not very elegant, also I have a cycle inside cycle, but it works:

List<MessageGroup> messageGroups = await _db.MessageGroups.Where(mg => mg.UserId == id).AsNoTracking().AsSplitQuery().Include(m => m.Messages).ThenInclude(mes => mes.FileCollection.Images)
                                                                                                                             .Include(m => m.Messages).ThenInclude(mes => mes.UrlPreviews).ToListAsync();
foreach (var mg in messageGroups)
{
    foreach (var m in mg.Messages)
    {
         m.FileCollection.Video = await _db.Video.Where(video => video.FileCollectionId == m.FileCollection.Id).Select(v => new VideoFile(v.ContentType, v.FileName, v.Length, v.FileCollectionId, null)).ToListAsync();
         m.FileCollection.Audio = await _db.Audio.Where(audio => audio.FileCollectionId == m.FileCollection.Id).Select(a => new AudioFile(a.ContentType, a.FileName, a.Length, a.FileCollectionId, null)).ToListAsync();
         m.FileCollection.Files = await _db.Files.Where(file => file.FileCollectionId == m.FileCollection.Id).Select(f => new OtherFile(f.ContentType, f.FileName, f.Length, f.FileCollectionId, null)).ToListAsync();
    }
}

null in file constructors is where byte[] blob data should be.

I used this approach and the key is the AsSplitQuery(). This will make EF Core use a separate query for the child objects. You can then update the query for the child objects to include only the wanted properties before it is executed. I didn't get the performance improvement without AsSplitQuery — Brett Manners, Aug 10 '22 at 02:50

score 0 · Answer 4 · answered May 19 '22 at 19:25

One other solution is to (re-)construct your entity using Select.

_dbContext.MyEntity.Select(
    myEntity => new MyEntity()
    {
        Property1 = myEntity.Property1,
        Property2 = myEntity.Property2
        // do not select / load Property3
    });

EF Core will translate this query in such a way that in the SELECT SQL-statement it only accesses the fields used in your query. This way, the other large columns are not loaded from the database.

However, this solution only works if you have a suitable class constructor or you can set properties to empty or dummy values, and is therefore not generally applicable.

EF Core. How to load only necessary properties from deep nested entity

4 Answers4