Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support separation of query and update mapping #15671

Closed
Tracked by #827
bdebaere opened this issue May 9, 2019 · 14 comments · Fixed by #20054
Closed
Tracked by #827

Support separation of query and update mapping #15671

bdebaere opened this issue May 9, 2019 · 14 comments · Fixed by #20054
Labels
area-query closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-enhancement
Milestone

Comments

@bdebaere
Copy link

bdebaere commented May 9, 2019

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
Copy link

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

@ajcvickers ajcvickers changed the title Map entity to view and table Support separation of query and update mapping May 10, 2019
@ajcvickers
Copy link
Member

ajcvickers commented May 10, 2019

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

@ajcvickers ajcvickers added this to the Backlog milestone May 10, 2019
@bdebaere
Copy link
Author

@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
Copy link
Member

See #15699 (comment) for an additional workaround and some more comments on this feature.

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

@bdebaere
Copy link
Author

@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
Copy link
Member

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

@bdebaere
Copy link
Author

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

@ajcvickers
Copy link
Member

@bdebaere The IQueryable returned from FromSql can be composed on in the same way as the IQueryable from DbSet. For example:

var entities 
    = context.View<Blog>("BlogsView")
        .Include(e => e.Posts)
        .Where(e => e.Title == "OneUnicorn")
        .ToList();

translates to

      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
Copy link
Author

@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
Copy link
Member

@bdebaere I'm out of ideas.

@AndriySvyryd
Copy link
Member

AndriySvyryd commented Jan 31, 2020

Note to implementor:

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

@AndriySvyryd AndriySvyryd self-assigned this Jan 31, 2020
AndriySvyryd added a commit that referenced this issue Feb 12, 2020
Use the new model in migrations and update pipeline

Part of #12846, #2725, #8258, #15671, #17270
AndriySvyryd added a commit that referenced this issue Feb 12, 2020
Use the new model in migrations and update pipeline

Part of #12846, #2725, #8258, #15671, #17270
AndriySvyryd added a commit that referenced this issue Feb 13, 2020
Use the new model in migrations and update pipeline

Part of #12846, #2725, #8258, #15671, #17270
AndriySvyryd added a commit that referenced this issue Feb 13, 2020
Use the new model in migrations and update pipeline

Part of #12846, #2725, #8258, #15671, #17270
AndriySvyryd added a commit that referenced this issue Feb 13, 2020
Use the new model in migrations and update pipeline

Part of #12846, #2725, #8258, #15671, #17270
@AndriySvyryd AndriySvyryd modified the milestones: Backlog, 5.0.0 Feb 25, 2020
@AndriySvyryd AndriySvyryd removed their assignment Feb 25, 2020
@AndriySvyryd AndriySvyryd added closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. and removed consider-for-current-release labels Feb 25, 2020
@ajcvickers ajcvickers modified the milestones: 5.0.0, 5.0.0-preview2 Mar 13, 2020
@ajcvickers
Copy link
Member

@AndriySvyryd I'm looking at this in terms of new feature. Does this work in the update pipeline yet? This:

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
Copy link
Member

@ajcvickers - What is the exception?

@AndriySvyryd
Copy link
Member

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

AndriySvyryd added a commit that referenced this issue Apr 9, 2020
Throw if no model or types are provided

Part of #15671
AndriySvyryd added a commit that referenced this issue Apr 9, 2020
Throw if no model or types are provided

Part of #15671
AndriySvyryd added a commit that referenced this issue Apr 10, 2020
Throw if no model or types are provided

Part of #15671
@ajcvickers ajcvickers modified the milestones: 5.0.0-preview2, 5.0.0 Nov 7, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area-query closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-enhancement
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants