Skip to content

Getting errors when mixing DbFunction and SqlQueryRaw  #32680

@ErikEJ

Description

@ErikEJ

Running this console app with a valid connection string causes the following runtime error:

The entity type 'tvpResult' requires a primary key to be defined. If you intended to use a keyless entity type, call 'HasNoKey' in 'OnModelCreating'. For more information on keyless entity types, see https://go.microsoft.com/fwlink/?linkid=2141943.

Removing OnModelCreating makes no difference.

using Microsoft.EntityFrameworkCore;

var context = new TestContext(new DbContextOptionsBuilder<TestContext>().UseSqlServer("Server=.\\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;Encrypt=false").Options);

var result = await context.Database
    .SqlQueryRaw<RawResult>("SELECT Name from sys.databases")
    .OrderBy(d => d.Name)
    .ToListAsync();

foreach (var item in result)
{
    Console.WriteLine(item.Name);
}


public class TestContext : DbContext
{
    public TestContext(DbContextOptions<TestContext> options) : base(options) { }

    [DbFunction("tvp", "dbo")]
    public IQueryable<tvpResult> tvp(int? storeid)
    {
        return FromExpression(() => tvp(storeid));
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<tvpResult>().HasNoKey();
    }
}

public partial class tvpResult
{
    public int Id { get; set; }
    public string Name { get; set; } = default!;
    public decimal Total { get; set; }
}

public partial class RawResult
{
    public string? Name { get; set; }
}
CREATE FUNCTION [dbo].[tvp] (@storeid int)
RETURNS TABLE
AS
RETURN
(
    SELECT 1 AS Id, 'Name' AS [Name], 0.666 AS 'Total'
);
GO

Include stack traces

The entity type 'tvpResult' requires a primary key to be defined. If you intended to use a keyless entity type, call 'HasNoKey' in 'OnModelCreating'. For more information on keyless entity types, see https://go.microsoft.com/fwlink/?linkid=2141943.
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidateNonNullPrimaryKeys(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.SqlServer.Infrastructure.Internal.SqlServerModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelRuntimeInitializer.Initialize(IModel model, Boolean designTime, IDiagnosticsLogger`1 validationLogger)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.AdHocMapper.AddEntityType(Type clrType)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.AdHocMapper.GetOrAddEntityType(Type clrType)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.SqlQueryRaw[TResult](DatabaseFacade databaseFacade, String sql, Object[] parameters)
   at Program.<<Main>$>d__0.MoveNext() in C:\Users\xxx\source\repos\ConsoleApp13\Program.cs:line 5

Include provider and version information

EF Core version: 8.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system:
IDE: Visual Studio 2022 17.8

Metadata

Metadata

Assignees

No one assigned

    Type

    Projects

    No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions