5

I just jumped into a new project with a new company using Entity Framework and ASP.NET MVC 4. I am no expert on Entity Framework, but I think I have a decent grasp of how to use it.

From what I can tell, my models should reflect a table in the database omitting the columns I don't need. Also, we can use stored procedures with Entity Framework, or an IoC to map the model.

I need to connect the relationship with two objects, whose relationship is pretty nested. I could create a stored procedure to do this or create a bunch of models (based on the tables in the database) and use an IoC configuration to have a table from the database for each model, then query the models in the repository layer for the data I need using Linq to Entities. Seems like I would have a lot of ".Include(x=>x.SomeModel)"

My question is: which option is better for maintenance and integration in the future of the project? A stored procedure or Linq to entities?

Or am I not understanding Entity Framework properly?

Improve this question

2 Answers 2

Reset to default
5

I think you've definitely got the gist of EF, that's a good question.

I would recommend that you go down the Linq to Entities option. Don't be afraid to use .Include(x => x.a), and remember that because this joins on another table you'll want to make sure your joins (foreign keys) have indexes on them. To me the maintenance benefit of the entity framework is being able to modify your table structure (add/move/rename columns) and then be able to re-compile your c# code and pickup what's broken - man, this would have saved me DAYS of fixing up stored procs a few years ago after making DB changes. If your logic is inside a stored proc then you don't get this benefit.

Only if your c# is starting to get too complicated to maintain would you consider using a stored proc to do the work, which might be better for maintenance purposes. Or if you're querying a database that can't be effectively used by the entity framework (compound foreign keys or something else).

Improve this answer
2

I would recommend creating a view and creating a specific entity that maps to that view.

Technically you don't even need to create the view in the DB, you can model the query purely in the EDMX storage model, but that's a little more on the advanced side of things (e.g. not supported in GUI). That said, if you have the ability to create the view in the DB I would highly suggest it as you can provide more optimizations to the view at the SQL level if need be (e.g. indexes specific to the view, etc.).

Improve this answer
7
  • 3
    Creating a view? I would not recommend this. You'll end up with lots of views to maintain, and whenever you make a table design change all of your views will break. Not fun. People might then put logic into the views (eg filtering) which other people might not expect. I've come across too many systems that have lots and lots of views for really no reason. It's just another headache.
    – Rocklan
    Jan 20, 2013 at 23:33
  • Well, I guess we disagree then. What you're suggesting with .Include will result in pulling back all fields for both entities when, in this kind of context, you probably only want N fields from Table A and N fields from Table B. If your concern is "maintaining views" and you think that this is too much work at the DB level then that's why there is a tradeoff to define the view totally inside the storage model of the EDMX itself. That way you can still completely control exactly which fields you want to come back for that specific entity, but not have to involve a DBA for maintenance.
    – Drew Marsh
    Jan 21, 2013 at 0:17
  • I should add that an alternative is to do projection of exactly which fields you want into an anonymous type with an EF query that would be much better than simply .Include()'ing everything blindly, but then you give up the fidelity of types (at least at the entity layer). You can still provide a domain object at a business/repository level of course with this technique. That said it's still more complex and costly than creating a specific entity over a view (EDMX or DB).
    – Drew Marsh
    Jan 21, 2013 at 0:20
  • I've never found it to be an issue where I'm retrieving too much data (eg extra columns) from a table. I guess it could be a problem if you have some columns that were very large (BLOB or whatever) but for me anyway it's never come up. The cost of retrieving a few extra columns (which in my case is negligable) is nothing compared to adding complexity. But yeah it all depends on data size. I didn't know that you can define a view inside the EDMX itself, that actually sounds pretty cool, but only if neccesary, premature optimisation yak yak. So we probably agree :)
    – Rocklan
    Jan 21, 2013 at 0:45
  • 1
    Yes, I agree, it completely depends on the "weight" of the entities involved. If you're just avoiding a few moderately sized columns coming back then it is probably not worth the optimization. As always the only way to really know the answer is to performance test your scenarios.
    – Drew Marsh
    Jan 21, 2013 at 0:54

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.