zzzprojects
BatchUpdate number increment failing in SQLite a PostgreSQL
I need to increment number in EF6 using Batch update in SQLite and PostgreSQL databases. Something like this:
update persons set number = number + 1;
I tried:
db.Persons.Update(p => new Person() { Number = p.Number + 1 });
But it is failing in both databases I'm using. Simple number update works though.
db.Persons.Update(p => new Person() { Number = 10 });
Can you please have a look on it? Maybe I'm doing something wrong.
Thank you
Here is the exception detail:
MySql.Data.MySqlClient.MySqlException: 'Unknown column 'w.xxx' in 'field list'' Inner Exception MySqlException: Unknown column 'w.xxx' in 'field list'
Hello @ondrasvoboda , @lousaibiao ,
Unfortunately using this kind of expression is not supported with SQLite while some other providers such as SQL Server support it.
That's due because we didn't find how to make an UPDATE with JOIN in SQLite.
If you have an idea how to make this kind of UPDATE, just let us know and we will try. Keep in mind, we cannot use sub-query like in this answer: https://stackoverflow.com/a/21074659/5619143 in the SET statement since we don't fully control the query.
Best Regards,
Jonathan
Thank you for your answer. I use mysql this time, and checked that this sub-query is valid in mysql. So, if it possible that making this kind of expression only works in several sql, like ms-sql, mysql and so on.
Isn't there a way how to do it without join? Because join is not really needed in this case.
Can this be used for PostgreSQL? https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql
Hello @ondrasvoboda ,
We will look at it and the reason why we didn't used this syntax in the first place.
Best Regards,
Jonathan
Hello @ondrasvoboda ,
We are currently completing re-writing that features in EF Classic (https://entityframework-classic.net/),
Once we complete it here, we will start to move the new code in EFE (https://entityframework-extensions.net/) and support other providers like PostgreSQL (it will be available for free and will replace the Batch Update for EF Plus)
So the code will be soon be all modified.
We hope that the new code will be released on next month.
Best Regards,
Jonathan