CODEWITHSUNDEEP
c#entity-framework-core
Norcino
Norcino

Reputation: 6587

EF Core 2.2.6 - Map Entity or Query to a SQL Statement (I don't want to create a view)

I really hope this is not a duplicate, I read dozens of related/similar questions.

I am creating a read only DbContext, first, confident that it was possible, I created DbSets and I tried to configure them to map to a SQL statement, I thought I did it in the past but looks like I am wrong.

So I changed strategy and decided Change all DbSets to DbQuery. After doing it I was actually happy because this really reflect the nature of the DbContext, it is read only like a Query but not like DbSet.

So now with DbQuery I can:

  1. Use ToView to map the query to a View on the Database
  2. Use ToQuery to retrieve the data through a query on existing entities in the context

This is a limitation for me, I don't want to create views and I don't want to map all entities in the context, is there an alternative solution for me?

Code for case 1:

protected override void OnModelCreating(ModelBuilder modelBuilder) {
    modelBuilder.Query<MyEntity>().ToView("my-view-name");

[...]

Code for case 2:

public virtual DbSet<MyEntity> MyEntities { get; set; }
public virtual DbSet<MyEntity2> MyEntity2s { get; set; }
public virtual DbSet<MyEntity3> MyEntity3s { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder) {
    modelBuilder.Query<MyEntity>().ToQuery(() => MyEntity2.Include(MyEntity3).Select(me2 => new MyEntity(me2.Property1, me2.Property2, me2.MyEntity3.Property1 ....) );

[...]

Upvotes: 2

Views: 1450

Answers (1)

Norcino
Norcino

Reputation: 6587

The only way I found is as shown below:

public virtual DbQuery<MyEntity> MyEntities { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder) {
    const string SQL = "SELECT whatever FROM wherever";
    modelBuilder.Query<MyEntity>().ToQuery(() => MyEntities .FromSql(SQL).AsQueryable());

[...]

To me this looks terrible, it works but I strongly believe this is not the right way. The DbQuery MyEntities is used in it's own configuration, like a dog biting it's own tail. Also I don't think I can have the configuration in a separate file like I do for entity mapping, but I need to further look into this.

Upvotes: 1

Related Questions