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?

Improve this question
5
  • 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... Commented Feb 24, 2015 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? Commented Feb 24, 2015 at 19:11
  • It is more cumbersome. View support is poor in EF. Commented Feb 24, 2015 at 19:19
  • Project is already in EF - no way out of that. Commented Feb 24, 2015 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. Commented Feb 24, 2015 at 22:01

1 Answer 1

Reset to default
7

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Not that i am in agreeing with those SQL developers, I am not, but there is msdn.microsoft.com/en-us/library/ms191432.aspx and this msdn.microsoft.com/en-us/library/ms180800.aspx The simple problem is just that views are not very well supported in EF.
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?
The argument is here: stackoverflow.com/questions/4378068/…
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.
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.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.