Read list of strings from MySQL Stored Proc in .NET 6

user_2684660 0
Viewed 46

I have a MySQL (not SQL Server) database with a Stored Procedure that returns a tabular result with one (1) column of strings.

I would like to get that result into my .NET application as some sort of IEnumerable<string> or List<string> etc.

What do?

I've tried playing with MySql.EntityFrameworkCore but get stuck quickly. Entity Framework Core either wants to generate tables based on models or models based on tables. I want neither. I just want my strings, plain and simple.

I've tried making a POCO with a single property and the [Keyless] attribute but no dice. If I define a DbSet<Poco> then the table doesn't exist, if I try to do context.Set<Poco>().FromSql('call my_stored_proc();'); then EF core complains the DbSet doesn't exist.

I'm using .NET 6 and the latest versions of above mentioned MySQL EntityFrameworkCore NuGet. Searching for answers is made harder by a lot of answers either assuming SQL Server or using older versions of EF core with methods that my EF core doesn't seem to have. And some results claim that EF core 6 doesn't work with .NET 6?

I'm also happy bypassing EF entirely if that's easier.

entity-framework-core .net-core mysql
2 Answers

What you are asking for will eventually be available in EF Core 7.0 - Raw SQL queries for unmapped types.

Until then, the minimum you need to do is to define a simple POCO class with single property, register it as keyless entity and use ToView(null) to avoid EF Core associate a db table/view with it.

e.g.

POCO:

public class StringValue
{
    public string Value { get; set; }
}

Your DbContext subclass:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<StringValue>(builder =>
    {
        builder.HasNoKey(); // keyless
        builder.ToView(null); // no table/view
        builder.Property(e => e.Value)
            .HasColumnName("column_alias_in_the_sp_result");
    });
}

Usage:

var query = context.Set<StringValue>()
    .FromSqlRaw(...)
    .AsEnumerable() // needed if SP call raw SQL is not composable as for SqlServer
    .Select(r => r.Value);
user_52025630

EF is an ORM, not a data access library. The actual data access is performed by ADO.NET using db-specific providers. You don't need an ORM to run a query and receive results :

await using DbConnection connection = new MySqlConnection("Server=myserver;User ID=mylogin;Password=mypass;Database=mydatabase");
await connection.OpenAsync();

using DbCommand command = new MySqlCommand("SELECT field FROM table;", connection);
await using var reader = command.ExecuteReader();
while (await reader.ReadAsync())
    Console.WriteLine(reader.GetString(0));

ADO.NET provides the interfaces and base implementations like DbConnection. Individual providers provide the data-specific implementations. This means the samples you see for SQL Server work with minimal modifications for any other database. To execute a stored procedure you need to set the CommandType to System.Data.CommandType.StoredProcedure :

using var command = new MySqlCommand("my_stored_proc", connection);
command.CommandType=CommandType.StoredProcedure

In this case I used the open source MySqlConnector provider, which offers true asynchronous commands and fixes a lot of the bugs found in Oracle's official Connector/.NET aka MySQL.Data. The official MySql.EntityFrameworkCore uses the official provider and inherits its problems.

ORMs like EF and micro-ORMs like Dapper work on top of ADO.NET to generate SQL queries and map results to objects. To work with EF Core use Pomelo.EntityFrameworkCore.MySql. With 25M downloads it's also far more popular than MySql.EntityFrameworkCore (1.4M).

If you only want to map raw results to objects, try Dapper. It constructs the necessary commands based on the query and parameters provided as anonymous objects, opens and closes connections as needed, and maps results to objects using reflection. Until recently it was a lot faster than EF Core in raw queries but EF caught up in EF Core 7 :

IEnumerable<User> users = cnn.Query<User>("get_user", 
        new {RoleId = 1},
        commandType: CommandType.StoredProcedure);

No other configuration is needed. Dapper will map columns to User parameters by name and return an IEnumerable<T> of the desired classes.

The equivalent functionality will be added in EF Core 7's raw SQL queries for unmapped types

user_1342040
Related