Performance problems with SQLite and Linq Selection

I can not manage to select the data in a flat structure and detailed information in a array of the same structure together in one select.

If I do it one after the other, the performance is pretty bad.

Perhaps someone can help me, here is an extract from the structures, tables and the select statement.

Database

View Classes

Select

foreach (MovieMaster record in database.MovieDb)
{
movieItem = new MediaItem();
mediaItem = MappingMediaItem(record);
// --- Now, select the actors
movieItem.Actors = database.ActorsInMovieDatabaseView.AsNoTracking().Where(x => x.MovieGuid == record.MovieGuid).ToList();
// Select(a => new
// {
// DisplayName = a.DisplayName,
// Role = a.Role
// }
//).ToList();
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka K S5y ago
It is not good practice to hit database inside forloop.So call actor list outside forloop and store it in a variable. Use that inside Forloop.
var actorList =database.ActorsInMovieDatabaseView.ToList();
foreach (MovieMaster record in database.MovieDb)
{
movieItem = new MediaItem();
mediaItem = MappingMediaItem(record);
// --- Now, select the actors
movieItem.Actors = actorList.Where(x => x.MovieGuid == record.MovieGuid).ToList();
// Select(a => new
// {
// DisplayName = a.DisplayName,
// Role = a.Role
// }
//).ToList();
}
Piyush Pansuriya5y ago
Hello,
If you replace your code with below may be it works
movieItem.Actors = database.ActorsInMovieDatabaseView.AsEnumerable().Where(x => x.MovieGuid == record.MovieGuid);