Can we convert all SQL scripts to Linq-to-SQL expressions or there is any limitation?
c#, linq, linq-to-sql, sql-server
Solution
Several features of SQL Server are not supported by Linq to SQL:
- Batch updates (unless you use non-standard extensions);
- Table-Valued Parameters;
- CLR types, including spatial types and `hierarchyid`;
- DML statements (I'm thinking specifically of table variables and temporary tables);
- The `OUTPUT INTO` clause;
- The `MERGE` statement;
- Recursive Common Table Expressions, i.e. hierarchical queries on a nested set;
- Optimized paging queries using `SET ROWCOUNT` (`ROW_NUMBER` is not the most efficient);
- Certain windowing functions like `DENSE_RANK` and `NTILE`;
- Cursors - although these should obviously be avoided, sometimes you really do need them;
- Analytical queries using `ROLLUP`, `CUBE`, `COMPUTE`, etc.
- Statistical aggregates such as `STDEV`, `VAR`, etc.
- `PIVOT` and `UNPIVOT` queries;
- XML columns and integrated XPath;
- ...and so on...
With some of these things you could technically write your own extension methods, parse the expression trees and actually generate the correct SQL, but that won't work for all of the above, and even when it is a viable option, it will often simply be easier to write the SQL and invoke the command or stored procedure. There's a reason that the `DataContext` gives you the `ExecuteCommand`, `ExecuteQuery` and `ExecuteMethodCall` methods.
As I've stated in the past, ORMs such as Linq to SQL are great tools, but they are not silver bullets. I've found that for larger, database-heavy projects, L2S can typically handle about 95% of the tasks, but for that other 5% you need to write UDFs or Stored Procedures, and sometimes even bypass the `DataContext` altogether (object tracking does not play nice with server triggers).
For smaller/simpler projects it is highly probable that you could do everything in Linq to SQL. Whether or not you should is a different question entirely, and one that I'm not going to try to answer here.
Problem
I want to convert all of my db stored procedures to linq to sql expressions, is there any limitation for this work? you must notice that there is some complicated queries in my db.