LINQ to SQL is a pretty cool way to work with data from a relational database when you’re programming with .NET.
One of the big advantages of LINQ is that it makes writing SQL statements a thing of the past. So you don’t have to remember which SQL statement does or doesn’t work on what version of your database. Another great thing is that LINQ generates your SQL dynamically, depending on what you’re trying to do with it
The trick is that in most cases LINQ is better at writing good SQL statements than you are. Ok, you might be a SQL guru and you might be able to write a kick-ass super-performing query, but a lot of people aren’t. LINQ just knows the tricks it takes to get your (probably Microsoft) database to do your stuff in the best way possible. At least, that’s the whole idea.
But (and here it comes) in some cases LINQ writes something that makes a wintered DBA cringe in terror as he sees those automatically generated SQL statements fly by in his SQL profiler.
So does LINQ suck because it does this? Are you better of writing your own statements like we did in the past? Hell no! You’d be giving up on a lot of good stuff just because a few SQL statements turned out to be monstrously malformed bastard children from the depths of table-scan!
What you should be doing however is profile the SQL statements your application spews out towards your database and see what they look like. If you see something ugly that you know is going to give you a performance headache someday, it’s time to rewrite that LINQ query into something that makes the SQL queries look better.
One thing I noticed you should be careful about is when you use a groupby statement in your LINQ to SQL code. This doesn’t always result in a GROUP BY clause in SQL as well, as you might expect. What it can turn into is out a loop where all your data is read sequentially and then grouped in memory before it’s returned to your C# code. As long as your data set isn’t too large this won’t be a problem. In case it is, you’re better of not using the groupby. One thing that works is fetching the data as is and group it in code yourself using LINQ to objects once it’s loaded.
Your SQL code might also show a lot of nested selects with the where-clause in the wrong place. This causes a lot of data to be read first and filtered out later. You can see this when you watch the SQL execution plan. What you really want is this filter to kick in as early as possible and not waste all those precious resources loading and processing data you don’t need anyway.
I found you can fix this by splitting up the LINQ statement into separate statements. Make sure your where conditions are in the first one. Then use that LINQ query as the base of your next one and you’ll see that the where-clause in your resulting SQL statements will pop up in the right place.
So basically LINQ is a great way to access your data, and keep your code clean from SQL statements IMO. Just keep in mind you still have to take a peek at that SQL code LINQ generates though. So maybe the tips above can help.