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

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.

c++sharpentity-framework-corewordpresssliderrevolution-slider


Solution 1:[1]

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.

Solution 2:[2]

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.

Solution 3:[3]

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.

Solution 4:[4]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 dglozano
Solution 2
Solution 3 User
Solution 4 Kolazomai

Related Questions