How can I add column to an existed sqlite database?

melon NG 291

Currently the new version of my program needs to add a column to the database for a new feature.

The program is made by WPF and EFCore. And also, the database I am using is SQLite.

The program needs to check whether the column existed. If not, then it will create a new column.

In addition, the old data of the database should be kept

How can I achieve this by EFCore?

Thank you.

Jack J Jun 24,281 Reputation points Microsoft Vendor

2022-07-08T08:48:37.343+00:00

@melon NG ，Welcome to Microsoft Q&A, I think Viorel-1' s answer is good to add a column the database in the ef core.
You could also refer to the answer to use the related code to check if the column existed.
Entity Framework check if column exists during OnModelCreating
melon NG 291 Reputation points

2022-07-08T09:14:34.997+00:00

It is just check whether the column exists, not automatedly add the column while the column does not exist.
Jack J Jun 24,281 Reputation points Microsoft Vendor

2022-07-08T09:16:54.217+00:00

@melon NG , thanks for the feedback, I mean that you could combine my method and Viorel-1's method to get what you want. Viorel-1's method is used to add the column while the column does not exist.
melon NG 291 Reputation points

2022-07-12T02:30:38.277+00:00

Your code works only on Entity Framework but not Entity Framework Core. I tried to convert them to Entity Framework Core code but can't.

In OnModelCreating can no longer create new DbContext, efcore will report an error:
An attempt was made to use the context instance while it is being configured. A DbContext instance cannot be used inside 'OnConfiguring' since it is still being configured at this point. This can happen if a second operation is started on this context instance before a previous operation completed. Any instance members are not guaranteed to be thread safe.
melon NG 291 Reputation points

2022-07-12T03:02:24.213+00:00

I created a new Stack overflow question, here is the detail of my code:
https://stackoverflow.com/questions/72946558/how-can-i-run-sql-script-in-onmodelcreating-by-efcore

Accepted answer

Jack J Jun 24,281 Reputation points Microsoft Vendor

2022-07-12T10:07:29.933+00:00

@melon NG , based on my test, I suggest that you could try the following code to add a column to the database if the column does not exist.

We could call it in button_click event directly.

Hope this could help you.

Best Regards,
Jack

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
melon NG 291 Reputation points

2022-07-13T02:24:13.023+00:00

I tried your code. However, VS reports this error:
Microsoft.Data.Sqlite.SqliteException
HResult=0x80004005
Message=SQLite Error 1: 'near "IF": syntax error'.

It seems there is something wrong with the SQL command. I tried to modify the SQL command and run it directly in SQLite Expert, but the error is still there.

Jack J Jun 24,281 Reputation points Microsoft Vendor

2022-07-13T10:43:09.777+00:00

@melon NG , sorry for that, I used sql database to test it so it caused the problem, Based on my further test, I find that there is no direct way to check if column exist then to add the column. I updated my answer to use ado.net to do it.

melon NG 291 Reputation points

2022-07-14T00:36:21.583+00:00

Thank you.
Sign in to comment

1 additional answer

Viorel 111.8K Reputation points

2022-07-08T08:17:45.813+00:00

Consider the ExecuteSqlRaw function: https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlraw.

Execute a statement like this: ALTER TABLE MyTable ADD COLUMN newColumn int null.

Intercept and ignore the errors that say "column already exists".
Please sign in to rate this answer.

0 comments No comments
Sign in to comment