How to programmatically rebuild all indexes in code first (C# and EntityFrameworkCore) created SQL Server tables after inserting large dataset

c# database-indexes entity-framework-core sql-server

Question

I have a code first create database table which I truncate and insert approximately 12000 records in with a c# script and Entity Framework Core 2.2.6.

I have 5 indexes on the table which I need to recreate after I do my database work. I can of course do this manually each time after I run my script, but being a programmer, that feels weird.

I tried finding a way to do this with EntityFrameworkCore, but I cannot seem to find it.

As a last resort I can execute a SQL command of course, but I was wondering whether there is some EntityFrameworkCore functionality that I am overlooking.

Or are there other ways of doing this more efficiently?

edit:

I run my script each time I receive a new DB from a third party to create our own from it, now in development that is roughly each month, later that will be less, so manually is an option, but I have an allergy for doing things manually

1
0
3/5/2020 3:38:43 PM

Popular Answer

I ended up using a stored procedure which does the below taken from here

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80 
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
0
3/5/2020 8:28:36 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow