EF Core One-to-Many Linq query

Question

I have a one-to-many relationship between two entities, and need to query a combination of those two entities.

The one is a File model:

 public class FileMetadata
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Category { get; set; } = string.Empty;
        public string Description { get; set; } = string.Empty;
        public string UserName { get; set; }
        public DateTime UploadDate { get; set; }
        public int FileContentId { get; set; }
        public FileContent Content { get; set; }
        public int UserId { get; set; }
        public User User { get; set; }
    }

And the other is a Tag model:

public class FileTag
    {
        [Key]
        public int Id { get; set; }
        public string Text { get; set; }
        public int FileMetadataId { get; set; }
        public FileMetadata Metadata { get; set; }
    }

The nature of the relationship, is that a File can have one-to-many Tags. What I need to do, is implement a search method, that can search the contents (description, name etc.) of a File, but also, search on all the Tags for that File. This is where the issue is, I can't find an efficient way to do that.

This is what I have done so far, it gives me the expected result, but, it is not efficient:

 public async Task<ServiceResponse<FileSearchResult>> SearchFileMetaData(string searchText, int page, float resultsPerPage)
    {
        var pageCount = Math.Ceiling((await FindFilesBySearchText(searchText)).Count / resultsPerPage);

        //Add tags to descriptions
        var extendedFiles = _context.Files.ToList();
        var tags = _context.FileTags.ToList();

        foreach (var file in extendedFiles)
        {
            var thisFilesTags = tags.Where(t => t.FileMetadataId == file.Id).ToList();
            foreach (var tag in thisFilesTags)
            {
                file.Description += tag.Text;
            }
        }

        //Search files with metadata matching the search text.
        var files = extendedFiles
                        .Where(f => f.Name.ToLower().Contains(searchText.ToLower())
                        || f.Description.ToLower().Contains(searchText.ToLower()))
                        .Skip((page - 1) * (int)resultsPerPage)
                        .Take((int)resultsPerPage)
                        .ToList();

        var response = new ServiceResponse<FileSearchResult>
        {
            Data = new FileSearchResult
            {
                Files = files,
                ThisPageNumber = page,
                TotalPages = (int)Math.Floor(pageCount)
            }
        };
        return response;
    }

I am getting all the files and tags from the database, looping to build up the description field with that File entity's tags, then performing the Linq operation to filter on the search text.

How can I still get the expected result, but improve the performance, but not getting unnecessary records?

Also, can this be solved with a better Linq query, that joins those two entities? Or, does the relationships/tables need to be redefined?

Answer 1

Try the following query. It tries to filter out files before materialization. For sure it is not ideal solution, Contains performs full table scan, which may affect performance if table is really big.

public async Task<ServiceResponse<FileSearchResult>> SearchFileMetaData(string searchText, int page, float resultsPerPage)
{
    searchText = searchText.ToLower();

    var filesQuery = _context.Files
        .Where(f => f.Name.ToLower().Contains(searchText)
            || f.Description.ToLower().Contains(searchText)
            || _context.FileTags.Any(t => t.FileMetadataId == f.Id && t.Text.ToLower().Contains(searchText))
        );

    var pageCount = Math.Ceiling((await filesQuery.CountAsync()) / resultsPerPage);

    var files = filesQuery
        .Skip((page - 1) * (int)resultsPerPage)
        .Take((int)resultsPerPage)
        .AsNoTracking()
        .ToList();

    var response = new ServiceResponse<FileSearchResult>
    {
        Data = new FileSearchResult
        {
            Files = files,
            ThisPageNumber = page,
            TotalPages = (int)Math.Floor(pageCount)
        }
    };

    return response;
}