CODEWITHSUNDEEP
c#sql.netentity-frameworkasp.net-core
Kurtis Jungersen
Kurtis Jungersen

Reputation: 2554

Add POCO / Entity to DbContext for Custom Query / Procedure Without Creating a Table in Entity Framework Code First

Background

I am using EF Core 3 for an application with a number of POCOs in a DbContext that I want to be created as database tables - no problem here! I use Linq queries to get data here, and life is good.

I also have some raw SQL queries and procedures for some more complex reporting. I've created POCOs for the returned data, and added to the DbContext as a DbSet:

public class FooBarContext : DbContext
{
    // ...

    public DbSet<FooReport> FooReport { get; set; }

    // ...
}

Where FooReport looks like:

public class FooReport
{
    [Key]
    public int Id { get; set; }

    // ...
}

The Problem / Workaround

This creates a migration for creating a new table called FooReport, which isn't what I want.

My workaround right now is to manually remove this action from the Migration that is generated, so that, in essence, I have an empty migration:

public partial class AddFooReport : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // intentionally clear this out, so the entity isn't created / dropped as a table

        // migrationBuilder.CreateTable("FooReport", ... );
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // intentionally clear this out, so the entity isn't created / dropped as a table

        // migrationBuilder.DropTable("FooReport");
    }
}

Then I'm able to call the procedure like so:

var result = this._fooBarContext.Set<FooReport>(@"[SP_FooReport]")
    .FromSqlRaw(sql)
    .ToList();

This does work, but seems hacky.

I also (unsuccessfully) tried to solve this problem by adding the NotMapped decorator to the FooReport POCO, but then the query itself fails.

TL;DR; - Can you define a DbSet as an entity that is specifically NOT a table?

Upvotes: 0

Views: 581

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88852

In EF Core 3+ simply remove the Key from FooReport to make it a Keyless Entity Type

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    
 modelBuilder.Entity<FooReport>().HasNoKey();
 //. . .

}

In EF 5 there's an attribute for this too:

[Keyless]
public class FooReport
{
    public int Id { get; set; }

    // ...
}

Upvotes: 1

Guru Stron
Guru Stron

Reputation: 141565

You can try adding modelBuilder.Ignore<FooReport>(); call to OnModelCreating method on your DbContext or mark FooReport with NotMapped attribute.

Upvotes: 0

Related Questions