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
- DML statements (I'm thinking specifically of table variables and temporary tables);
- Recursive Common Table Expressions, i.e. hierarchical queries on a nested set;
- Optimized paging queries using
ROW_NUMBERis not the most efficient);
- Certain windowing functions like
- Cursors - although these should obviously be avoided, sometimes you really do need them;
- Analytical queries using
- Statistical aggregates such as
- 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
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.
What does this mean? Does this mean you want to use L2S to call your stored procedures, or do you want to convert all the T-SQL statements in your stored procs to L2S? If it's the later, you should not have too many problems doing this. Most T-SQL statements can be represented in Linq without problem.
I might suggest you investigate a tool like Linqer to help you with your T-SQL conversion. It will convert most any T-SQL statement into Linq. It has saved my quite a bit of time in converting some of my queries.
If you already have tested and working stored procedures, why convert them at all? That's just making work for no reason.
If you were starting a new product from scratch and were wondering whether to use stored procedures or not, that would be an entirely different question.
There are many constructs in T-SQL which have no parallel in LINQ to SQL. Starting with flow control, ability to return multiple row sets, recursive queries.
You will need to approach this on a case by case basis. Remembering any times the SP does significant filtering work on the database much of that filtering may end up on the client, so needing to move far more data from server to client.
I've found that in almost all cases where I've done a new project with L2S, I've completely removed the need for stored procedures. In fact, many of the cases where I would have been forced to use a stored proc, multivariable filters for instance, I've found that by building the query dynamically in LINQ, I've actually gotten better queries in the vast majority of cases since I don't need to include those parts of the query that get translated to "don't care" in the stored proc. So, from my perspective, yes -- you should be able to translate your stored procs to LINQ.
A better question, thought, might be should you translate your stored procs to LINQ? The answer to that, I think, depends on the state of the project, your relative expertise with C#/VB and LINQ vs SQL, the size of the conversion, etc. On an existing project I'd only make the effort if it improves the maintainability or extensibility of the code base, or if I was making significant changes and the new code would benefit. In the latter case you may choose to incrementally move your code to pure LINQ as you touch it to make changes. You can use stored procs with LINQ so you may not need to change it to make use of LINQ.
I'm not a fan of this approach. This is a major architectural change, because you are now removing a major interface layer you previously put in place to gain a decoupling advantage.
With stored procedures, you have already chosen the interface your database exposes. You will now need to grant users SELECT privileges on all the underlying tables/views instead of EXECUTE on just the application stored procedures and potentially you will need to restrict column read rights at the column level in the tables/views. Now you will need to re-implement at a lower level every explicit underlying table/view/column rights which your stored procedure was previously implementing with a single implicit EXECUTE right.
Whereas before the services expected from the database could be enumerated by an appropriate inventory of stored procedures, now the potential database operations are limited to the exposed tables/views/columns, vastly increasing the coupling and potential for difficulty in estimating scope changes for database refactorings and feature implementations.
Unless there are specific cases where the stored procedure interface is difficult to create/maintain, I see little benefit of changing a working SP-based architecture en masse. In cases where LINQ generates a better implementation because of application-level data coupling (for instance joining native collections to database), it can be appropriate. Even then, you might want to LINQ to the stored procedure on the database side.
If you chose LINQ from the start, you would obviously have done a certain amount of work up front in determining column/view/table permissions and limiting the scope of application code affecting database implementation details.
- Can we convert all SQL scripts to Linq-to-SQL expressions or there is any limitation?
- Convert string to int in one line of code using LINQ
- Is there a good source that gives an overview of linq optimizations?
- How to convert between Linq expressions with different return types?
- Is there any way to create a LINQ query as a variable without having the data source (yet)?
- How to pass LinQ Expressions from F# to C# code
- LINQ Source Code Available
- Cannot convert source type to target type List<KeyValuePair> Linq
- Is there a bug in this code from 101 LINQ Samples on MSDN? (Update: Fixed)
- Convert SQL to Linq where there is in clause
- convert foreach loop to linq code
- creating Linq to sqlite dbml from DbLinq source code
- Stubbing Code for Test With Linq Expressions and Lambdas
- Does LINQ convert code to SQL queries
- How do i convert this linq code to inline sql
- Is possible to convert linq lambda expressions to SQL using dart?
- Is there a more efficient way to convert LINQ result to string or List<string>?
- Is there a way to view LINQ Generated query expressions in CRM Dynamics?
- How to convert this recursive code to Linq
- Convert this LINQ code back to a Loop (or Why is this object sometimes null)
- convert linq to object query to sql query (no linq to sql code or datacontext)
- Convert piece of code into LINQ (short syntax)
- Convert code that uses Linq to code that does not
- Linq - how to convert this code to linq
- Can I can convert this C# code into some Linq code?
- source code for LINQ 101 samples
- Make VB code that replaces LINQ expressions
- Is there a better way to code this LINQ fragment?
- Convert VB.NET Linq to C# Linq code
- How to convert a LINQ query to use lambda expressions
- Issue with search functionality using Linq
- Get Func<T1, T2> from PropertyInfo
- Use LINQ to GroupBy two columns and build a dictionary with them?
- LINQ select distinct items and subitems
- ng-repeat not update after insert new record. it is updated after click refresh
- Getting back the number of added digits
- Linq slow while SQL fast
- Dynamic select sentence with LINQ - Join 2 DataTables
- Incorrect LINQ Expression
- Linq Call - There is already an open DataReader associated with this Command which must be closed first
- Linq query for referal manager
- Access any data that is not contained in grouped element
- Linq query for the Multi list in singe out put
- Copy Properties of one object to another object
- Unexpected behavior of the `Where` clause
- How to get attributes from xml/scxml
- aggregation result from SQL view to gridview
- SQL to LINQ with outer joins and count
- Does Select() on a List lose track of the size of the collection?
- How to do an "in" query in entity framework?