Can EF Core Code First Approach Be Used with Azure Synapse?

Question

I have a requirement to fetch all the client data using an API and then store it in Azure Synapse. I need to create the tables in Synapse and sync data every day based on the Last Modified Timestamp in Synapse.

Can EF Core Code First approach be used to create the database schema, or is there another recommended way to achieve this?

Additionally, is there an SDK for Synapse that can be used to sync data?

Answer 1

@sonal khatri

Thanks for using Microsoft Q&A forum and posting your query.

Yes, you can use the EF Core Code First approach with Azure Synapse, but there are some considerations to keep in mind. EF Core is primarily designed to work with relational databases, and while Azure Synapse Analytics supports SQL-based operations, it may not fully support all EF Core features out of the box.

Creating Database Schema with EF Core

1. Define Your Models: You can create your entity classes as usual.
2. DbContext Configuration: Set up your DbContext to connect to Azure Synapse. You’ll need to ensure that your connection string is correctly configured for Azure Synapse.
3. Migrations: Use EF Core migrations to create and update your database schema. However, be aware that some advanced features of EF Core might not be supported in Synapse.

Syncing Data

For syncing data based on the Last Modified Timestamp, you can implement a scheduled job or a background service in your application that:

• Fetches the latest data from your API.
• Compares it with the existing data in Synapse.
• Inserts or updates records as necessary.

SDK for Azure Synapse

Azure Synapse does not have a dedicated SDK for syncing data like some other Azure services. However, you can use:

• Azure Data Factory: This is a powerful tool for data integration and can be used to orchestrate data movement and transformation.
• Azure Functions: You can create serverless functions to handle data processing and syncing tasks.

Summary

While EF Core can be used to create the schema in Azure Synapse, consider using Azure Data Factory or Azure Functions for efficient data syncing. This combination allows you to leverage the strengths of both EF Core for schema management and Azure services for data integration

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

be sure to use EF 9, and .UseAzureSynapse():

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-9.0/whatsnew