Support for SQL Server sparse columns

[borisdj]

I know I can do this with raw SQL in Up method, but can this be done with fluent API.
If it can How, and if not, are there plans to add this feature in future versions?

Further technical details

EF Core version: 1.1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017

[smitpatel]

Documentation link: https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-sparse-columns

@borisdj - There is no fluent api at present.

[ajcvickers]

@borisdj It would be interesting to add fluent API extension methods for this for the SQL Server provider. However, it is not a high priority for us, so putting it on the backlog. We would consider at a high-quality PR from the community for this.

[garfbradaz]

Hey @ajcvickers : I have some time spare on Friday, so I can give this a go?

Questions

• Would you want this to be an PropertyBuilder extension in here for SQL Server?

https://github.com/aspnet/EntityFrameworkCore/blob/469177a49d251bb81184b42ce4cd2ffe623b345a/src/EFCore.SqlServer/Extensions/SqlServerPropertyBuilderExtensions.cs

• Would it require a DataAnnotation to identify a SPARSE column?

[ajcvickers]

@garfbradaz Yes, that's the appropriate place for the fluent API. It's fine to not have data annotation support for this--there are many places where the fluent API is the only way to configure something. Also, provider-specific attributes for mapping is a space that we haven't yet ventured into.

[ajcvickers]

/cc @AndriySvyryd @bricelam @divega

[garfbradaz]

Smashing. @ajcvickers . Spent Friday familiarizing myself with the code (and today as well). Quite a complex beast (which is fun to learn).

Questions if I may:

• There are some SPARSE restrictions - Do these need to be applied as a SqlServerConventionSetBuilder where appropriate?

• I couldn't find a SQL Server implementation of Microsoft.EntityFrameworkCore.Migrations.Operations.CreateTableOperation & Microsoft.EntityFrameworkCore.Migrations.Operations.AlterTableOperation - I presume a SqlServerCreateTableOperation & SqlServerAlterTableOperation will be needed then to satisfy this feature, including the SPARSE elements.

Obviously any tests associated with the new code.

[ErikEJ]

have a look at: https://github.com/aspnet/EntityFrameworkCore/blob/master/src/EFCore.SqlServer/Migrations/SqlServerMigrationsSqlGenerator.cs#L455
and

https://github.com/aspnet/EntityFrameworkCore/blob/master/src/EFCore.SqlServer/Migrations/SqlServerMigrationsSqlGenerator.cs#L196

[garfbradaz]

Awesome thanks @ErikEJ

[henrikdahl8240]

Has Entity Framework Core come to support SPARSE columns in the meantime?

I have made an SQL script by hand, which drops indexes, alter columns to be SPARSE and re-adds indexes because you cannot alter a column to be SPARSE if there's an index, so it could be rather convenient with up-front support for SPARSE so I can get rid of this script.

[ajcvickers]

@henrikdahl8240 This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 5.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[roji]

Note: as mentioned by @henrikdahl8240 in #20880 (comment), SPARSE columns could be a good fit for TPH.

[jonlouie]

@ajcvickers Is the EF Core team still open to accepting a solution from the community? If so, I'd like to spend a few more days preparing a PR for review.

With the help of the prior discussions in this issue, I was able to locate most places that will require code changes and spent some time getting familiar with PropertyBuilder's and MigrationsSQLGenerator's. Please let me know if you have any suggestions and/or if I should put a hold on this effort.

[ajcvickers]

@jonlouie Yes, still interested, and I don't think there is any reason to put it on hold. :-)

[eugbaranov]

@jonlouie, just wondering if you had any luck with preparing PR for this?

[jonlouie]

@eugbaranov Unfortunately, I did not end finding the time to complete my PR. If you are looking to pick up this issue, here are some places that likely need modifications or additions:

• src/EFCore.SqlServer/Extensions/SqlServerPropertyBuilderExtensions.cs
• src/EFCore.SqlServer/Extensions/SqlServerPropertyExtensions.cs
• src/EFCore.SqlServer/Internal/SqlServerModelValidator.cs
• src/EFCore.SqlServer/Metadata/Internal/SqlServerAnnotationNames.cs
• src/EFCore.SqlServer/Metadata/Internal/SqlServerAnnotationProvider.cs
• src/EFCore.SqlServer/Migrations/SqlServerMigrationsSqlGenerator.cs