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

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.

CodePudding user response：

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; }
}

OnModelCreating:

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);