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.
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.
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.
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'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.
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.
- Can we convert all SQL scripts to Linq-to-SQL expressions or there is any limitation?
- Is there any way to intercept all Linq to SQL queries?
- How can I convert my LINQ query to use the lambda syntax and is there any advantage to doing this?
- Is there any way to "name" or "tag" a LINQ query so that it can be identified by a tool like Ignite SQL easier?
- How can I convert this SQL Query into LINQ (OVER (PARTITION BY Date))
- Is there any way to create a LINQ query as a variable without having the data source (yet)?
- Are there any parts of LINQ I should avoid for SQL 2000?
- Is there any way to use Linq to SQL without adding the .dbml file?
- Is there any open source software for converting SQL statements to LINQ?
- Convert SQL to Linq where there is in clause
- If using LINQ to SQL is there any good reason to learn SQL queries/syntax anymore?
- How can I select all in linq to sql
- 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?
- Convert linq with All to SQL
- How can I convert this SQL to LINQ
- convert linq to object query to sql query (no linq to sql code or datacontext)
- Linq union all equivalent of sql code
- Is there any difference between sql linq
- How can I convert this SQL to VB.NET LINQ
- How can I convert a SQL script into Linq
- Is there any way to encapsulate a simple LINQ query in an extension method that can be used with LINQ to Entities query?
- How can I convert my working SQL query to Linq (C#)?
- How can I convert SQL to lambda expressions
- Can I can convert this C# code into some Linq code?
- Convert Sql all operator to linq
- LINQ expressions can not be translated to Sql when combining specifications in Specification Pattern
- How can I convert Sql query to Linq and its equivalent with Join() method in Entity Framework Core
- How can I convert sql to linq
More Query from same tag
- How to Sort a Model Based on a Property in IList<T> within that Model
- OrderBy Linq with US Dates
- C# LINQ - Select statement that compares all properties of a class with a different instance of the same class?
- Parsing XML file with LINQ getting wrong information
- .NET reflection: how to get properties defined on partial class
- Entity-Framework using expressions to build global and reusable filter/query-rules
- Compiled query using list of class objects in C#
- LINQ to Entities does not recognize the method
- How can I add a new text box to my WPF from a DataSet?
- Using Linq comparision with previous record
- Linq select into model and set properties
- Expression.Invoke in Entity Framework?
- Can I check if a string contains an array's element in it?
- How do you count the words in an array of strings with LINQ?
- MoreLinq's Scan and For Loop returns different results
- LINQ Joining in C# with multiple conditions
- C# LINQ Ignoring empty values in datatable
- Linq select object while changing some variables
- Join Linq column with minimum value column
- LINQ C#, sum column of same field
- Moving code from the view into the LINQ query
- how to group by letter and put in same letter size
- How to update multiple items in list
- Linq to SQL get distinct records grouped by date
- Linq query for NOT IN on joined table
- Translate an SQL Query to Linq to Entities
- Data Loading Strategy/Syntax in EF4
- How to Serialize a Control to JSON?
- Linq to XML Error
- LINQ: Select distinct from a lst and append different properties into one