Best way to query Azure Synapse serverless/dedicated pool from .Net core 8 API

Hi Abul Hassan,

Given your performance challenges with EF Core and the read-only nature of your operations, I suggest using Dapper for this task.

Dapper is a "micro-ORM" that provides a lightweight and high-performance way to execute raw SQL queries and map the results to your .NET objects.

Dapper advantages:

• Performance: Dapper is faster than EF Core for read-heavy operations because it has less overhead in query generation and change tracking. It directly translates SQL results to objects with minimal fuss. Many benchmarks show that Dapper is faster than EF Core
• Full SQL Control: You write the exact SQL queries, giving you granular control over optimization. This is crucial for Synapse, where specific query patterns (e.g., proper distribution key usage, optimized joins, WITH (NOLOCK) for serverless) are vital for performance.
• Handles Complex Joins and Views: Since you're writing the SQL, you can craft highly optimized joins, including those involving numerous tables or complex views in Synapse, without EF Core's potential for generating suboptimal SQL.
• Parameterized Queries: Dapper fully supports parameterized queries, which is essential for preventing SQL injection vulnerabilities.
• Asynchronous Support: Dapper offers excellent asynchronous methods (e.g., QueryAsync, ExecuteAsync), which are crucial for building responsive and scalable APIs in .NET Core.

Here's an example:

// Example repository with Dapper
public class SynapseRepository
{
    private readonly IDbConnection _connection;
    public async Task<IEnumerable<YourEntity>> GetOptimizedDataAsync(int id)
    {
        var sql = @"
            SELECT e.Id, e.Name, r.RelatedData
            FROM MainEntity e
            INNER JOIN RelatedEntity r ON e.Id = r.EntityId
            WHERE e.Id = @Id";
        return await _connection.QueryAsync<YourEntity>(sql, new { Id = id });
    }
}

Also check out these relevant documentations:

1. Official Dapper Repository: GitHub - DapperLib/Dapper - Primary source for Dapper documentation and examples
2. Comprehensive Dapper Guide: Code Maze provides an excellent tutorial on using Dapper with ASP.NET Core Web API, covering queries, executions, transactions and repository patterns Using Dapper with ASP.NET Core Web API - Code Maze
3. Azure Sample Repository: Microsoft provides a complete sample REST API using .NET, Dapper and Azure SQL GitHub - Azure-Samples/azure-sql-db-dotnet-rest-api: REST API using .Net, Dapper and Azure SQL on GitHub
4. Performance Best Practices: Microsoft Learn documentation covers best practices for dedicated SQL pools, including query optimization and statistics management Best practices for dedicated SQL pools - Azure Synapse Analytics | Microsoft Learn

If you have any problem, feel free to reach out.