Last week I have trying to load hierarchical data from the same table (Parent/Child). Although I have made it in the past, I was searching the way to accomplish it with LINQ to SQL without SP or at least with the minimum required (in any case, I had a "fallback"). After 4 hours of searching the net without success (there are plenty of articles, nothing met my requirements see here and here).
Then I thought to give LINQ a chance, and I just used it querying the association created by the designer. I was astonished to reveal it caused to only one T-SQL statement (!!!) to load the full hierarchy(bottom to top, meaning each item as its full ancestors list accessed by .Parent property, does not include children), and the performance? total of 50,000 (10,000 per level, 5 levels in hierarchy) loaded in less than 2.5 seconds! which is great for our purpose (in no case I will query the full table without any filtering criteria).
My satisfaction is based on some assumptions:
1. Each T-SQL written to the log (DataContext.Log) is a roundtrip to the server.
2. This test is by far almost 10x than what we would face in production environment.
3. The test was made on my dev machine without network roundtrip, but based on assumption 1, it will be 1 roundtrip which is necessary in any case.
4. I didn't tweak the LINQ engine for the best performance (like read-only datacontext, LoadWith etc.)
Why should I eliminate / minimize the use of SPs / T-SQL and use almost-only LINQ to SQL?
In one word, maintenance, which is, for my opiniongn, the most valuable aspect for most of business applications, because
business change and so your application, it will be hard to maintain
you will pay a lot of money for each chae and this will minimize or worst, eliminate all your profit. Using LINQ to SQL affects maintenance in several aspects, I will elaborate two of them:
1. Most of development resources are, well, developers. And in C# (or .NET at all) they know C# the most, they have much advanced tools and IDE to use with that language, they are in friendly environment. When those developers are required to write T-SQL / SPs, they are trying to "escape" or eventually do this but the results are fairly bad so you need a DBA to re-write those procedures
2. DataBase is more "static" than the code, more over, changing the database will probably affect many parts (modules) of the application and it is very hard to maintain compared to compiled code.
Conclusion,
Move to LINQ to SQL (L2S), it saves time, streamlines the development and maintenance process!