Entity Framework Core 3, Define DBSet for query but dont create migration entry

Question

Within EF core 3.1, I am trying to run some custom SQL as a test, to see if I can populate a custom object. My custom object is called CustomPerson.

public partial class CustomPerson
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

The code to run the SQL is as follows:

public List<CustomPerson> GetCustomPersonSql()
{
    var persons = _context.CustomPerson.FromSqlRaw("SELECT FirstName, LastName FROM Person");
    return persons.ToList();
}

I have declared it as follows in the Context file, note the HasNoKey().

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

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

This works and returns the data, but when I run the following migration to check if it thinks anything has changed.

Add-Migration anythingNew

It creates the CustomPerson table! which I don't want. This needs to be a query only object and not stored or added to the database.

I have tried the following instead in the OnModelCreating, but this then stops the query from working.

modelBuilder.Ignore<CustomPerson>();

Can anyone help me get this working. So I want it to be used to query custom SQL responses, but not be included in any migrations?

You could remove everything related to that "entity" from the generated migration's `Up()` and `Down()` and it will cause no problems at runtime. Migrations are not sacrosanct. Also, what does doing `modelBuilder.Entity().ToQuery("query")` produce in the migration? — Tanveer Badar, Oct 16 '20 at 14:37
Ok, it felt like the migrations were very important and tied heavily to everything, which I don't really like. We currently use fluent migrator which seems a lot easier, Database first which I prefer :) Im not sure how to use ToQuery that syntax is not supported with a string — Andrew, Oct 16 '20 at 14:55
Similar question answer in here: https://stackoverflow.com/questions/22038924/how-to-exclude-one-table-from-automatic-code-first-migrations-in-the-entity-fram — Bruno, Oct 16 '20 at 15:01

Andrew · Accepted Answer · 2020-10-19T06:51:08.760

I got this working by appending the .ToView on the end as shown below

modelBuilder.Entity<CustomPerson>(entity =>
{
    entity.HasNoKey()
          .ToView(null);
});

So the HasNoKey seems to allow me to use it such as this

var persons = _context.CustomPerson.FromSqlRaw("SELECT FirstName, LastName FROM Person");
return persons.ToList();

Then the ToView makes sure to ignore it if you create a migration

If I do try and use this as someone mentioned

modelBuilder.Ignore<CustomPerson>();

then I don't get the migration, but I cant use the object as a domain object then! as it blows up when I do the SELECT and try and pull it back as my object.

That's a pretty weird, but apparently valid, usecase for `ToView()`. — Tanveer Badar, Oct 19 '20 at 07:36

score 1 · Answer 2 · answered Oct 16 '20 at 16:05

You could remove everything related to that "entity" from the generated migration's Up() and Down() and it will cause no problems at runtime.

Migrations are not sacrosanct, or black magic. It is advisable to always go through the migrations that EF generates and make any modifications as needed. For instance, you could modify all foreign key constraints with a NO CHECK in SQL server to leave your FKs as documentation only.

Entity Framework Core 3, Define DBSet for query but dont create migration entry

2 Answers2