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

Question

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?

David Browne - Microsoft · Accepted Answer · 2020-06-22 20:49:59Z

1

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

    // ...
}

edited Jun 22, 2020 at 20:49

answered Jun 22, 2020 at 19:58

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kurtis Jungersen Over a year ago

From the docs linked that looks like exactly what I want, however the IDE says it's not defined - am I missing a package reference perhaps? I would have expected it to be in Microsoft.EntityFrameworkCore.Design

Kurtis Jungersen Over a year ago

Ah, I see now it's only available in EF Core 5+, which is in preview now :-/ (stackoverflow.com/questions/61396213/…)

David Browne - Microsoft Over a year ago

See update. The feature is in EF Core 3, just not the attribute. EF Core 3 there you have to use Fluent Configuration in OnModelCreating.

Guru Stron · Accepted Answer · 2020-06-22 19:52:44Z

0

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

answered Jun 22, 2020 at 19:52

Guru Stron

150k11 gold badges186 silver badges231 bronze badges

Collectives™ on Stack Overflow

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

Background

The Problem / Workaround

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Background

The Problem / Workaround

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

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

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