4

I'm using Entity Framework 6 in my ASP.NET MVC project using database first approach.... I have an .edmx file with all the table associations.

Now, the sql developers do not want to have any direct calls to the tables, so they would like me to use views instead.

I'm trying to understand what are the benefits of using Views vs Tables?

Also, I'm afraid if I use Views and apply all the PK and FK in the edmx and i regenerate something the changes will be lost?

Any thoughts on this please?

piris
  • 1,547
  • 3
  • 22
  • 26
  • Benefits / tradeoffs would be broad here, it really depends on what you need and where you are heading to. I'm guessing you are not allowed to write any views yourself? If so then you might not be able to use EF as you'd expect at all... – Crono Feb 24 '15 at 19:07
  • I'm able to create views, but they'd prefer to use views instead of the tables directly. i could still use ef with the views wouldn't i? – piris Feb 24 '15 at 19:11
  • It is more cumbersome. View support is poor in EF. – Philip Stuyck Feb 24 '15 at 19:19
  • Project is already in EF - no way out of that. – piris Feb 24 '15 at 19:19
  • 2
    Can't the database developers explain the benefits to you (or to themselves)? This sounds like needless rigidity, if not a turf war. – Gert Arnold Feb 24 '15 at 22:01

1 Answers1

5

Your SQL developers are idiots. Please feel free to tell them so. The only thing using a view of a table does versus the using the table directly is disallow write operations. If that's the goal, there's better ways to achieve that by doing things as simple as just having the application use a user without write privileges.

By using views, you're forgoing primary keys, foreign keys and indexing, all of which serve to make queries more performant. In other words, your SQL developers are asking you to hit the database harder and make less performant queries against it. In other words, they have no idea what they're talking about.

If the application needs writes (which frankly there's very few applications that don't), then views are out anyways. If they're really all that concerned about Entity Framework making writes, then they can also create stored procedures for tasks like CREATE, UPDATE, etc. and you can integrate those stored procedures into Entity Framework. However, I can almost assure you that Entity Framework is generating better SQL than they can write by hand, if for no other reason than it's being used and contributed to by far many more people than you have on staff.

Chris Pratt
  • 232,153
  • 36
  • 385
  • 444
  • 2
    Not that i am in agreeing with those SQL developers, I am not, but there is https://msdn.microsoft.com/en-us/library/ms191432.aspx and this https://msdn.microsoft.com/en-us/library/ms180800.aspx The simple problem is just that views are not very well supported in EF. – Philip Stuyck Feb 24 '15 at 19:44
  • ouch that's a harsh statement. I can argue that. Is there any benefit by having views not being dependent on the table structure? Meaning we are not dependent if the table structure changes? – piris Feb 24 '15 at 20:07
  • 1
    The argument is here: http://stackoverflow.com/questions/4378068/when-to-use-a-view-instead-of-a-table – piris Feb 24 '15 at 21:28
  • 2
    Those are some good points, but I stand by my statement. I've never seen a good, practical reason for using a view that couldn't be solved in a better and more performant way, especially when talking about something like a website that ideally should be handling 1000s of simultaneous requests (that's the dream anyways). Maybe if you're using it for reporting or some other internal, low volume task, there might be an argument somewhere for using a view. Otherwise, I don't see it. – Chris Pratt Feb 24 '15 at 21:33
  • @piris That's a benefit, yes, but 1) that's the only one I can think of and 2) I hardly think it's worth going through all the issues this may cause, now and further down the road. IMHO, proper security management is a far better way to control who / what can do what. – Crono Feb 25 '15 at 13:12
  • Chris - Do you think Views will affect the performance in any way? We do have a search functionality with 5+tables of 50+million rows. Also our Web App will be used by a significant amount of requests. Also, these tables shouild be strictly readonly from the web app. – piris Feb 25 '15 at 15:33
  • Crono - what kind of issues this may cause? – piris Feb 25 '15 at 15:33
  • @piris For example, issues at the ORM level. What if the structure changes so much that the view isn't updateable anymore? What if the ORM doesn't support views at all? – Crono Feb 25 '15 at 16:24
  • 1
    If you're doing search, especially with 50+ million data points, you should be using a real search engine, not querying a database. Look into something like Elasticsearch. It's one of the best out there and has a C# client via the NEST Nuget package. It's even what the StackExchange network uses. – Chris Pratt Feb 25 '15 at 18:12