search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.48k stars 3.13k forks source link

Introduce UseAzureSql/UseAzureSynapse as alternatives to UseSqlServer #33816

Open roji opened 1 month ago

roji commented 1 month ago

We're increasingly seeing T-SQL divergence, where certain features are not available e.g. on Azure Synapse (e.g. #33555), and some features are only available Azure SQL (e.g. the new JSON type, the new vector search capabilities). To account for this divergence, we'll allow users to specify which database is being targeted by providing UseAzureSql/UseAzureSynapse alternatives to UseSqlServer.

Note: we'd need to duplicate all current variants of UseSqlServer.

ErikEJ commented 1 month ago

So given the following engine editions:

2 = Standard (For Standard, Web, and Business Intelligence.) 3 = Enterprise (For Evaluation, Developer, and Enterprise editions.) 4 = Express (For Express, Express with Tools, and Express with Advanced Services) 5 = SQL Database 6 = Azure Synapse Analytics 8 = Azure SQL Managed Instance 9 = Azure SQL Edge (For all editions of Azure SQL Edge) 11 = Azure Synapse serverless SQL pool

UseSqlServer: 2, 3, 4, 8 (5, 9)

UseAzureSql: 5, 8 (8 depending on Update policy - default is SQL Server 2022 compatibility)

UseAzureSynapse: 6, 11

roji commented 1 month ago

@ErikEJ are you suggesting we auto-detect based on the engine queried from the database, or something similar? Or allow the user to specify the engine edition directly?

ErikEJ commented 1 month ago

@roji No - just trying to clarify for myself (and maybe others) what the options will actually mean

ErikEJ commented 1 month ago

@roji Just was made aware of this, affecting Azure SQL Managed Instance

roji commented 1 month ago

@ErikEJ interesting, thanks - I wasn't aware of this... It seems to optionally bring SQL Managed Instance closer to SQL Azure in terms of the engine/features. Do you see this as changing the above proposal?

ErikEJ commented 1 month ago

@roji Yeah, just got this in a newsletter, seems like a brand new thing. It does not affect the above proposal, but I have made some small opdates to my list above.

ajcvickers commented 1 month ago

Removing the milestone and adding needs-design to revisit if we are doing the right thing here, and what the mappings should be.

drmcclelland commented 3 weeks ago

Thank you so much for working on this issue - it will be very helpful to users of Azure Synapse like me! The lack of support in Synapse for ESCAPE (in LIKE clauses) and OFFSET (for paging) make it harder to use EF for this particular variant of T-SQL.