Get a single, simple type value from a stored procedure with Entity Framework Core and raw SQL

[eebeeDEV]

Hi,

One can use generics in the class definition:

public class ValReturn<T>
{
    public T Value { get; set; }
}

when building the model, create a builder for each type one will need:

partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ValReturn<bool>>().HasNoKey();
    modelBuilder.Entity<ValReturn<int>>().HasNoKey();
    modelBuilder.Entity<ValReturn<DateTime>>().HasNoKey();
    modelBuilder.Entity<ValReturn<string>>().HasNoKey();
}

and then to execute:

using (var db = new NorthwindContext())
{
    var result = db.Set<ValReturn<int>>()
        .FromSqlRaw("exec dbo.Scalar")
        .AsEnumerable()
        .First().Value;

    Console.WriteLine(result);
}

This makes it more versatile

Best, E.

[ErikEJ]

@eebeeDEV Interesting shortcut.

I wonder if I could take advantage of that in my SqlQuery library/NuGet package?

[michaelcsikos]

Hi Erik. I have been using this approach with EFCore 3, but with EFCore 6 new migrations are generating a Table for the ReturnValue<T>:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "ReturnValue<int>",
        columns: table => new
        {
            Value = table.Column<int>(type: "int", nullable: false)
        },
        constraints: table =>
        {
        });
}

Any idea how to prevent this?

[michaelcsikos]

Ah, I missed the bit about ToView(null)

modelBuilder.Entity<ReturnValue<int>>().HasNoKey().ToView(null);

[njscorpion]

Can i make this asynchronous?

[ErikEJ]

Sure just use FirstAsync