I need to fetch a lot of records from a SQL Server database with EF6. The problem that its takes a lot of time. The main problem is entity called Series which contains Measurements. There is like 250K of them and each has 2 nested entities called FrontDropPhoto and SideDropPhoto.
[Table("Series")]
public class DbSeries
{
[Key] public Guid SeriesId { get; set; }
public List<DbMeasurement> MeasurementsSeries { get; set; }
}
[Table("Measurements")]
public class DbMeasurement
{
[Key] public Guid MeasurementId { get; set; }
public Guid CurrentSeriesId { get; set; }
public DbSeries CurrentSeries { get; set; }
public Guid? SideDropPhotoId { get; set; }
[ForeignKey("SideDropPhotoId")]
public virtual DbDropPhoto SideDropPhoto { get; set; }
public Guid? FrontDropPhotoId { get; set; }
[ForeignKey("FrontDropPhotoId")]
public virtual DbDropPhoto FrontDropPhoto { get; set; }
}
[Table("DropPhotos")]
public class DbDropPhoto
{
[Key] public Guid PhotoId { get; set; }
}
I've wrote fetch method like this (Most of the properties omitted for clarity):
public async Task<List<DbSeries>> GetSeriesByUserId(Guid dbUserId)
{
using (var context = new DDropContext())
{
try
{
var loadedSeries = await context.Series
.Where(x => x.CurrentUserId == dbUserId)
.Select(x => new
{
x.SeriesId,
}).ToListAsync();
var dbSeries = new List<DbSeries>();
foreach (var series in loadedSeries)
{
var seriesToAdd = new DbSeries
{
SeriesId = series.SeriesId,
};
seriesToAdd.MeasurementsSeries = await GetMeasurements(seriesToAdd);
dbSeries.Add(seriesToAdd);
}
return dbSeries;
}
catch (SqlException e)
{
throw new TimeoutException(e.Message, e);
}
}
}
public async Task<List<DbMeasurement>> GetMeasurements(DbSeries series)
{
using (var context = new DDropContext())
{
var measurementForSeries = await context.Measurements.Where(x => x.CurrentSeriesId == series.SeriesId)
.Select(x => new
{
x.CurrentSeries,
x.CurrentSeriesId,
x.MeasurementId,
})
.ToListAsync();
var dbMeasurementsForAdd = new List<DbMeasurement>();
foreach (var measurement in measurementForSeries)
{
var measurementToAdd = new DbMeasurement
{
CurrentSeries = series,
MeasurementId = measurement.MeasurementId,
FrontDropPhotoId = measurement.FrontDropPhotoId,
FrontDropPhoto = measurement.FrontDropPhotoId.HasValue
? await GetDbDropPhotoById(measurement.FrontDropPhotoId.Value)
: null,
SideDropPhotoId = measurement.SideDropPhotoId,
SideDropPhoto = measurement.SideDropPhotoId.HasValue
? await GetDbDropPhotoById(measurement.SideDropPhotoId.Value)
: null,
};
dbMeasurementsForAdd.Add(measurementToAdd);
}
return dbMeasurementsForAdd;
}
}
private async Task<DbDropPhoto> GetDbDropPhotoById(Guid photoId)
{
using (var context = new DDropContext())
{
var dropPhoto = await context.DropPhotos
.Where(x => x.PhotoId == photoId)
.Select(x => new
{
x.PhotoId,
}).FirstOrDefaultAsync();
if (dropPhoto == null)
{
return null;
}
var dbDropPhoto = new DbDropPhoto
{
PhotoId = dropPhoto.PhotoId,
};
return dbDropPhoto;
}
}
Relationships configured via FluentAPI:
modelBuilder.Entity<DbSeries>()
.HasMany(s => s.MeasurementsSeries)
.WithRequired(g => g.CurrentSeries)
.HasForeignKey(s => s.CurrentSeriesId)
.WillCascadeOnDelete();
modelBuilder.Entity<DbMeasurement>()
.HasOptional(c => c.FrontDropPhoto)
.WithMany()
.HasForeignKey(s => s.FrontDropPhotoId);
modelBuilder.Entity<DbMeasurement>()
.HasOptional(c => c.SideDropPhoto)
.WithMany()
.HasForeignKey(s => s.SideDropPhotoId);
I need all of this data to populate WPF DataGrid. The obvious solution is to add paging to this DataGrid. This solution is tempting but it will break the logic of my application badly. I want to create plots at runtime using this data, so I need all of it, not just some parts. I've tried to optimize it a bit by make every method to use async await, but it wasn't helpful enough. I've tried to add
.Configuration.AutoDetectChangesEnabled = false;
for each context, but loading time is still really long. How to approach this problem?
