Efcore: Support For Columstore Index Storage

[StrangeWill]

Currently it appears the only way I can roll Columnstore Indexes (https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-2017) is to basically write a ton of SQL during a migration and leave the database in a state that the model is not really aware about.

Is there any planned support for these unique types of indexes for the SQL Server provider of EF Core?

[ErikEJ]

@StrangeWill You could also use SSDT for database first development

[StrangeWill]

Hmm, yeah I guess going back to SSDT is an option, code-first and EF migrations brought us a _ton_ of benefits though.

[ErikEJ]

EF Core Power Tools can generate DbContext and POCO classes from your SSDT project...

[StrangeWill]

EF Core Power Tools can generate DbContext and POCO classes from your SSDT project...

Yep, it just requires us to do double up/change a lot of our process:

• Application cannot manage it's own database state, we need to add new processes both for dev (create new SSDT changes, re-import new context/POCOs), and our CI process (removing our ability to build on Linux).
• SSDT tools now need to be deployed on everyone's machines (we have some devs that are 100% Linux now being as that was a huge plus to Core and SQL server going Linux).

We'll consider that but it's definitely has a non-zero cost, we'd probably rather just hack it into the migration over lose all the benefits we get with code-first migrations.

[ErikEJ]

Fwiw, you can run sqlpackage on Linux

[tekrarCo]

Is code first EF Core has plan to support Columstore or not? how many years EF Core wants to be behind ?

[StrangeWill]

We just threw some SQL shenanigans into the migration, I'm not a fan of it but the other solutions (SSDT/sqlpackage/etc.) are _significantly_ worse.

It's not a huge deal because we only use it on a handful of tables, I could see it being a huge nightmare on larger projects.

I do like the idea of being able to push the benefits of columnstore indexes (and other possible storage mechanisms provided by other servers?), not sure how abstracted you can be on that.

[tekrarCo]

We Hate SSDT/sqlpackage/etc , If we want do something we like to do it code first or in sql management studio , ef core code first is so lazy with new features of sql server, it seems if they are not even have new version of sql server and they work with so old version that nobody even use , is it possible they even dont know about Columstore Index Storage? 🤔

[tekrarCo]

We just threw some SQL shenanigans into the migration, I'm not a fan of it but the other solutions (SSDT/sqlpackage/etc.) are _significantly_ worse.

It's not a huge deal because we only use it on a handful of tables, I could see it being a huge nightmare on larger projects.

I do like the idea of being able to push the benefits of columnstore indexes (and other possible storage mechanisms provided by other servers?), not sure how abstracted you can be on that.

Can you please share these migration codes? or if you have git/repository about it , can i see?

[StrangeWill]

It's literally just commenting out the table stand up, then using migrationBuilder.SQL to build the columnstore indexes, also you have to remove things like foreign keys into that table.

Very fragile, but it gets the job done.

[tekrarCo]

It's literally just commenting out the table stand up, then using migrationBuilder.SQL to build the columnstore indexes, also you have to remove things like foreign keys into that table.

Very fragile, but it gets the job done.

do you know any link or website ?

[StrangeWill]

Nope, it's pretty basic if you know how to configure columnstore indexes from SQL, and if not you can find guides on that form Microsoft.

[StrangeWill]

As we look forward into more and more heavy data lifting with EF Core (its performance really allows for it), Columstore starts to become a more powerful tool to use for those tasks, any consideration in seeing this on v6?