Entity Framework - improve query efficiency that retrieve lots of data

Question

I have a database with lots of data - Excel file management.

The application manages objects when each object contains an Excel file (number of sheets, list of rows for each sheet).

The application contains a Data Grid and a list of sheets. The user will select revision number, and sheet name, the lines of the same sheet are displayed.

The objects are built like this:

Version object contains list of Pages, each page contains list of PageLine.

What is the best way to retrieve data ?

For example, my PopulateGrid method :

public void PopulateGrid() 
{
    CurrentPageLineGridObjects.Clear();
    PreviousPageLineGridObjects.Clear();
    SetCurrentConnectorPageList();

    // get current revision
    CurrentPageLineGridObjects = CurrentCombinedPageList.Where(page => page.Name == 
 PageNameSelected).FirstOrDefault().PageLines.ToList().ToObservablePageLineGridObjectCollection();
    //get prev revision
    RevisionCOMBINED prevRevCombined = pgroupDataService.GetRevisionCombinedForPGroup(((PGroup)PGroupSelected.Object).Id).Result;
    // get pages and pagelines for revision eeprom and override.
    List<Page> eepromPages =  
 revisionEEPROMDataService.GetEEPROMPages(prevRevCombined.RevisionEEPROM.Id).Result;                    
}

public async Task<List<Page>> GetEEPROMPages(int eepromRevId)
{
    string[] includes = { "Pages", "Pages.PageLines" };
    IEnumerable<RevisionEEPROM> list = (IEnumerable<RevisionEEPROM>)await dataService.GetAll(includes);
    return list.Where(r => r.Id == eepromRevId).SelectMany(p => p.Pages).ToList();
}

public async Task<IEnumerable<T>> GetAll()
{
    using (DeployToolDBContex contex = _contexFactory.CreateDbContext())
    {
        IEnumerable<T> entities = await contex.Set<T>().ToListAsync();
        return entities;
    }
}

As you can see I pull out all the version data along with all the Sheets and all the PageLines and only then filter by the given version key.

It takes me quite a while to load.

I would appreciate any advice.

I tried to use IQueryable:

public async Task<List<T>> GetQueryable(string[] includes = null)
{
    using (DeployToolDBContex context = _contextFactory.CreateDbContext())
    {
        if (includes != null)
        {
            var query = context.Set<T>().AsQueryable();

            foreach (var include in includes)
                query = query.Include(include);

            return query.ToList();
        }
        else
        {
            List<T> entities = await context.Set<T>().AsQueryable().ToListAsync();
            return entities;
        }
    }
}

Answer 1

This is terrible use of EF. For a start, code like this:

IEnumerable<RevisionEEPROM> list = (IEnumerable<RevisionEEPROM>)await dataService.GetAll(includes);
return list.Where(r => r.Id == eepromRevId).SelectMany(p => p.Pages).ToList();

You are fetching the entire table and associated includes (based on that includes array passed) into memory before filtering.

Given you are scoping the DbContext within that data service method with a using block, the best option would be to introduce a GetPagesForEepromRevision() method to fetch the pages for a given ID in your data service. Your Generic implementation for this Data Service should be a base class for these data services so that they can provide common functionality, but can be extended to support specific cases to optimize queries for each area. For instance if you have:

public class DataService<T>
{
    public async Task<IEnumerable<T>> GetAll() {...}

    // ...
}

extend it using:

public class EepromDataService : DataService<EEPROM>
{
    public async Task<IEnumerable<Page>> GetPagesForEepromRevision(int eepromRevId)
    {
        using (DeployToolDBContext context = _contexFactory.CreateDbContext())
        {
            var pages = await context.Set<EEPROM>()
                .Where(x => x.Id == eepromRevId)
                .SelectMany(x => x.Pages)
                .ToListAsync();
            return pages;
        }
    }
}

So if your calling code was creating something like a var dataService = new DataService<EEPROM>(); this would change to var dataService = new EepromDataService();

The IQueryable option mentioned before:

public IQueryable<T> GetQueryable()
{
    var query = _context.Set<T>().AsQueryable();
    return query;
}

Then when you go to fetch your data:

 var results = await dataService.GetQueryable()
     .Where(r => r.Id == eepromRevId)
     .SelectMany(r => r.Pages)
     .ToListAsync();
return results;

This requires either a Unit of Work pattern which would scope the DbContext at the consumer level (eg: GetEEPROMPages method) or a shared dependency injected DbContext that spans both the caller where ToListAsync would be called as well as the data service. Since your example is scoping the DbContext inside the dataService with a using block that's probably a bigger change.

Overall you need to review your use of asynchronous vs. synchronous calls because other methods that do things like:

RevisionCOMBINED prevRevCombined = pgroupDataService.GetRevisionCombinedForPGroup(((PGroup)PGroupSelected.Object).Id).Result;

is very bad practice to just call .Result. If you need to call async calls from within a synchronous method then there are proper ways to do it and ensure things like exception bubbling can occur. For examples, see (How to call asynchronous method from synchronous method in C#?) If the code doesn't need to be asynchronous then leave it synchronous. async is not a silver "go faster" bullet, it is used to make supporting code more responsive so long as that code is actually written to leverage async the entire way. (I.e. HTTP Web requests in ASP.Net)