Entity is incorrectly detaching from tracking EF Core

Question

I am trying to query objects from a database, loop through them and check if a column has a value and, if it does not, create a value and assign it to that column and save it to the database. The problem I'm having is that the entity is detaching after the query so I cannot save the changes. Below is the code I am using to query and update the entity.

    DateTime runTime = passedDateTime ?? DateTime.Now;
    await using DiscordDatabaseContext database = new();
    DateTime startOfWeek = exactlyOneWeek ? runTime.OneWeekAgo() : runTime.StartOfWeek(StartOfWeek);
    //Add if not in a Weekly Playlist already and if the video was submitted after the start of the week
    List<PlaylistData> pld = await database.PlaylistsAdded.Select(playlist => new PlaylistData
    {
        PlaylistId = playlist.PlaylistId,
        WeeklyPlaylistID = playlist.WeeklyPlaylistID,
        Videos = playlist.Videos.Where(
                video => (video.WeeklyPlaylistItemId == null || 
                          video.WeeklyPlaylistItemId.Length == 0) &&
                         startOfWeek <= video.TimeSubmitted)
            .Select(video => new VideoData
            {
                WeeklyPlaylistItemId = video.WeeklyPlaylistItemId,
                VideoId = video.VideoId
            }).ToList()
    }).ToListAsync().ConfigureAwait(false);
    int count = 0;
    int nRows = 0;
    foreach (PlaylistData playlistData in pld)
    {
        if (string.IsNullOrEmpty(playlistData.WeeklyPlaylistID))
        {
            playlistData.WeeklyPlaylistID = await YoutubeAPIs.Instance.MakeWeeklyPlaylist().ConfigureAwait(false);
        }
        foreach (VideoData videoData in playlistData.Videos)
        {
            PlaylistItem playlistItem = await YoutubeAPIs.Instance.AddToPlaylist(videoData.VideoId, playlistId: playlistData.WeeklyPlaylistID, makeNewPlaylistOnError: false).ConfigureAwait(false);
            videoData.WeeklyPlaylistItemId = playlistItem.Id;
            ++count;
        }
    }
    nRows += await database.SaveChangesAsync().ConfigureAwait(false);

The query works correctly, I get all relevant Playlist and Video Rows to work with, they have the right data in only the specified columns, and the query that is logged looks good, but saves do not work and calling database.Entry() on any of the Playlists or Video objects show that they are all detached. What am I doing wrong? Are collections saved a different way? Should my query be changed? Is there a setting on initialization that should be changed? (The only setting I have set on init that I feel like may affect this is .UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery) but the query logged isn't even split as far as I can see)

Answer 1

You work with projected objects

• PlaylistData
• VideoData

Projected objects does not tracked by EF core as far as I know. So the solution is to select DbSet's entity objects (mean types that specified in database.PlaylistsAdded and playlist.Videos properties) or select those objects before update and then update them.

UPDATE:

Example code for second option:

foreach (PlaylistData playlistData in pld)
{
    var playlist = database.PlaylistsAdded
        .Include(x=> x.Videos)
        .First(x => x.PlaylistId == playlistData.playlistData);
    if (string.IsNullOrEmpty(playlistData.WeeklyPlaylistID))
    {
        playlist.WeeklyPlaylistID = await YoutubeAPIs.Instance.MakeWeeklyPlaylist().ConfigureAwait(false);
    }
    foreach (VideoData videoData in playlistData.Videos)
    {
        var video = playlist.Videos.First(x=> x.VideoId == videoData.VideoId);
        PlaylistItem playlistItem = await YoutubeAPIs.Instance.AddToPlaylist(videoData.VideoId, playlistId: playlistData.WeeklyPlaylistID, makeNewPlaylistOnError: false).ConfigureAwait(false);
        video.WeeklyPlaylistItemId = playlistItem.Id;
        ++count;
    }
}

NOTICE: this would produce double select's so first option is more preferred