Efcore: Support separation of query and update mapping

[bdebaere]

Copied from my StackOverflow post.

Consider the model below. I have an Order class and an OrderLine class. The Order.TotalAmount is calculated through a view which performs an OUTER APPLY across all the Order.OrderLines.

    [Table("SelectOrder")]
    public class Order
    {
      public decimal TotalAmount { get; set; }

      public virtual ICollection<OrderLine> OrderLines { get; set; }
    }

    [Table("SelectOrderLine")]
    public class OrderLine
    {
      public decimal Amount { get; set; }

      public virtual Order Order { get; set; }
    }

I have decorated my classes with the TableAttribute to enable Entity Framework Core to get the data from the views to the entity. The TableAttribute actually points to the view instead.

Now I would like to perform inserts, updates and deletes. This poses a problem as it's not possible to use a view with an OUTER APPLY for these changes. I've tried using query types for this but you cannot define an entity as both a query type and an entity type. Doing so results in an error for me. So adding a TableAttribute with the actual table e.g. Order in combination with modelBuilder.Query<Order>().ToView("SelectOrder"); does not work.

I could create a separate class SelectOrder which is mapped to the view and map my Order entity to the table. Or I could build a custom attribute and perform some custom SQL generation by overriding the SqlServerQuerySqlGenerator.

But before I go down these roads... Is it really not possible to map an entity to both a view for selects and a table for inserts, updates and deletes?

I've since gone through the code and noticed that SqlServerQuerySqlGenerator is not used to generate non-SELECT queries, am I correct? So I'm left with adjusting the code to a custom EF Core version and add a ViewAttribute which it uses during INSERT INTO SELECT ... FROM generation or multiple classes which is the least preferred option by the person pulling my strings. Is there nothing better?

[sdanyliv]

@bdebaere, no EF do not support such operations. Check this extension and you will be able to do that.

[ajcvickers]

Notes from triage: we should consider this as part of greater flexibility in update versus query mapping. This has overlap with:

• Using of stored procedures for updates
• CQRS patterns that involve different models for query/update

[bdebaere]

@ajcvickers I have working code which listens to an extension method ToView(). If there is no view mapped to the entity, the table name is taken. I can create a PR soon that you can take a look at.

[ajcvickers]

See https://github.com/aspnet/EntityFrameworkCore/pull/15699#issuecomment-493603778 for an additional workaround and some more comments on this feature.

Also, see #245 for the overall stored-proc mapping feature

[bdebaere]

@ajcvickers Thank you very much for your response. Unfortunately I cannot use FromSql as, unless I'm mistaken, this doesn't function well together with IQueryable.

[ajcvickers]

@bdebaere Can you give some more details on what you mean by that?

[bdebaere]

@ajcvickers As far as I'm aware there is no way to include navigation properties, filter on them, select on them, et cetera.

[ajcvickers]

@bdebaere The IQueryable returned from FromSql can be composed on in the same way as the IQueryable from DbSet. For example:
```C#
var entities
= context.View("BlogsView")
.Include(e => e.Posts)
.Where(e => e.Title == "OneUnicorn")
.ToList();

translates to
```sql
      SELECT [e].[Id], [e].[Title]
      FROM (
          SELECT * FROM [BlogsView]
      ) AS [e]
      WHERE [e].[Title] = N'OneUnicorn'
      ORDER BY [e].[Id]

      SELECT [e.Posts].[Id], [e.Posts].[BlogId], [e.Posts].[Title]
      FROM [Post] AS [e.Posts]
      INNER JOIN (
          SELECT [e0].[Id]
          FROM (
              SELECT * FROM [BlogsView]
          ) AS [e0]
          WHERE [e0].[Title] = N'One Unicorn'
      ) AS [t] ON [e.Posts].[BlogId] = [t].[Id]
      ORDER BY [t].[Id]

[bdebaere]

@ajcvickers That's nice but I need something which will do that automatically for the entities and their includes because, using OData, by default I have no control over the creation and execution of the expression tree.

[ajcvickers]

@bdebaere I'm out of ideas.

[AndriySvyryd]

Note to implementor:

• Consider allowing to define overrides of mappings for update, query or migrations.

[ajcvickers]

@AndriySvyryd I'm looking at this in terms of new feature. Does this work in the update pipeline yet? This:
C# modelBuilder.Entity<Blog>(b => { b.ToTable("Blogs"); b.ToView("BlogsView"); });
Causes SaveChanges to throw.

Assuming it doesn't yet work end-to-end, we should probably not announce this in preview 2, right?

/cc @JeremyLikness

[smitpatel]

@ajcvickers - What is the exception?

[AndriySvyryd]

@ajcvickers Correct I'm just starting the work on the update pipeline. All of this will only start being usable when TPT is done.