Software design approach for large relational database

Question

I am working on a personal side project that will utilize a complex and large relational database. During the design of the database I had some co-workers give advice to how I should approach my application; they advised to use the entity framework.

From what I've read and what I am watching it sounds like the entity framework is the right tool for the job. However what I have come to programmer.stackexchange for is to ask whether or not somebody who has just going going in their career should do.

Let me rephrase; I have 3 years web design / development experience (html/css/js/php/mysql) and now about 1 year experience with actual software engineering C#/VB. So the question is should I utilize the entity framework or should I build this rather complex and ambitious project without the entity framework; ergo building all the SQL that will handle massive amounts of relational data?

The reason why I ask is because I want to get the most ROI (knowledge and experience) from the project.

Answer 1

Both approaches will lead to you learning useful, but different skills.

One will help you learn the intricacies of EF, the other will allow you to better learn the raw tsql.

I would consider both important. So, shouldn't you pick what is best for this particular case (EF) and learn it?

There is endless knowledge that can be acquired, concentrate your efforts on the most immediately beneficial.

Answer 2

will utilize a complex and large relational database

OR mappers like EF can help you to avoid tedious work with "complex" models. You have to write less code, especially less CRUD code. On the other hand, when handling a "large" database (in terms of massive amounts of relational data, as you wrote), you have to be careful that the OR mapper does not produce more problems than it solves for you. For such kind of DB, you will typically have to make use of fine-tuned SQLs, SQL bulk processing, stored procedures etc., all things where ORM won't be helpful.

And even when large just means "lot of tables and relations", EF may become a bottleneck, see the links behind this forum post:

http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/a0f6c057-558c-45d1-98e6-231cc0a41613

Those SO links may be helpful for you, too:

https://stackoverflow.com/questions/6185918/entity-framework-4-1-for-large-number-of-tables-715

https://stackoverflow.com/questions/2981054/entity-framework-4-handling-very-large-1000-tables-data-models

Answer 3

In terms of learning:

Both are useful for most developer careers.

• If you use EF in your project, you'll obtain experience you can use with other ORMs.
• If you don't use EF in this project, you'll have more experience with raw SQL which will serve you later for other databases.

Depending on your career:

• EF would be better if you know that you will probably spend most of your time in the future developing CRUD applications. They often benefit a lot from ORMs, so most are relying on ORMs with practically no direct access to the database.

• No EF would be better if you know that you will handle applications which doesn't use ORMs either because they have a structure too inappropriate for ORMs or because they use lots of legacy code or just because they are written in languages where there are no good quality ORMs.

Note that if you have little experience with actual software engineering, you must be careful when using EF: be sure to understand how your code is translated into SQL queries by EF, and also be sure to understand the queries themselves. The best way to do it is through extensive SQL profiling. If you see something you didn't expect, search and find why the code was translated this way.

Seriously, do it. Lots of people who start using ORMs with little or no experience in SQL are finding themselves writing terrible code which doesn't do what they are expecting, and when, once on production server, they notice that their code runs the exact same query several hundred times instead of one, it's too late too expensive to solve.

In terms of performance:

EF is neither slow nor fast. If you're using it correctly, your application will be fast enough. If something is slow, use the profiler to find where is the bottleneck, and if you find that the slowness is caused by EF, and not how you use it, then do a direct query to solve this bottleneck.