How to Connect to Azure Synapse Database using Entity Framework?

Question

We have Entity Framework DB First architecture in our project and where we were required to connect to Azure synapse Database from relational Sql server Database for migration purpose. As Azure Synpase is PAAS and not relational Database , we are not able to map Data in Database to those into our models using EF edmx. As we know EF is a ORM i.e object relational mapper which mapps relations from DB to objects.

Error :

Unable to generate the model because of the following exception:
'System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: 'columnproperty' is not a recognized built-in function name.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

while creating edmx from Azure synapse Database we are getting below error

Error :

Unable to generate the model because of the following exception:
'System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: 'columnproperty' is not a recognized built-in function name.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

We needed to connect to Azure synapse Database using Entity framework using any means not only Database first approach it can be any.

Answer 1

For this problem, we tried multiple options for EF Database first approach but here EF Code First approach solved our problem for connection to Azure synapse. Using existing edmx you cannot connect to Azure synapse.

You need to follow below steps for solving you problem.

1. Create new Model classes that are present in your edmx.
2. Create new DBContext class in Models folder.
3. Create new controller
4. In controller get method you can use C# lambda expression to query the data from new DBset like below.

IQueryable<test_customer> result = db.customerDbset 
            .Where(n => n.job.Equals(job))
            .ToList().AsQueryable();`

Add new controller:

Select new model and new Dbcontext class in below fields:

Make below changes in DbContext class for new mapping as per azure db table:

In OnModelCreating method you need to mapp new table from Azure synapse to our new model class like in above image. and add Dbset like below in Dbcontext class For example:

public DbSet<test_customer> customerDbset { get; set; }

After all this is done remove your all reference from existing edmx file and unload that edmx from your project.