Nested objects and BatchUpdateAsync - is this possible? - Sqlite

[Webreaper]

I have the following objects:

class Image
{
     int ImageId { get; set; }
     int FolderId { get; set; }
     string SomeField { get; set; }
}

class ImageMetaData
{
        [Required]
        public virtual Image Image { get; set; }
        public int ImageId { get; set; }
        
        public string SomeOtherField { get; set; )
}

There is exactly one ImageMetaData record for each Image record.

Now, I want to update the ImageMetaData table, for any instances where Image.FolderID = folderToUpdate.

    using var db = new ImageContext();

    var queryable = db.ImageMetaData.Where(img => img.Image.FolderId == folderToUpdate);
    int updated = await db.BatchUpdate(queryable, x => new ImageMetaData { SomeOtherField = "Blah" });

However, this throws with SQLite Error 1: 'near "INNER": syntax error'.. I'm presuming it's something to do with not being able to decipher the img.Image.FolderId.

Is this even possible/supported? If so, any ideas how I should code it?

If it makes a difference:

• EFCore 6
• .Net 6
• MacOS
• EFCore.BulkExtensions v6.4.1
• Database is SQLite

[borisdj]

Not as such. Sqlite does not support JOIN in UPDATE queries: https://sqlite.org/lang_update.html Query that would work should look like:

UPDATE ImageMetaData
SET [SomeOtherField] = "Blah" 
FROM (SELECT ImageId, FolderId FROM Image) AS i
WHERE i."ImageId" = ImageMetaData."ImageId1"
AND i."FolderId" = 1

But currently Batch parser does not do this.

You could try selecting ImageMetaDatas with that FolderId reading them into list and then calling BulkUpdate. It's not efficient as Batch would be but should give same result.

[Webreaper]

Thanks. Useful to know. Any plans to add this as a feature?

I may just see if I can do select the image Ids that match the folder and then execute a Batch Update, or I might just write custom SQL as a temp workaround (because it's possible a folder could contain 25,000 images....).

Could do something like

Update imagemetadata 
set Somefield = 'Blah' 
where imageid in 
  (select imageid from images 
      where folderid = 1234)

Cheers!

[borisdj]

Will consider if and how that could be implemented in the long run. At the moment RawSql is probably good alternative.

[borisdj]

Since Batch ops as of EF7 are now natively supported with ExecuteUpdate/ExecuteDelete, Batch extensions will become Deprecated here. Focus is only on BULK methods which is the main purpose of the lib.