LINQ to SQL, ExecuteQuery, etc

This might all be a bit subjective:

Our organization has made a strong attempt to adopt LINQ to SQL as our primary data access method and for the most part this works well. (Let’s leave the EF out of the discussion.)

Some of our developers find LINQ difficult and migrate back to traditional raw SQL via ExecuteQuery. We also utilize OpenQuery in some of our applications to access data on remote servers. OpenQuery canno开发者_如何学编程t be executed via LINQ and always results in code being executed via ExecuteQuery. As an organization we have also made the decision to move away from Stored Procedures and again relying on LINQ.

So, is it fair to say that some queries are just so complex that they can’t be performed with LINQ? We want to avoid business logic in the database so where do we go when you can’t use LINQ? What is the general feeling of ExecuteQuery as a better alternative to ADO.NET Command.Execute()? I think one can make an argument against stored procedures or a the very least that avoiding them is a valid choice but what about querying Views with LINQ as an alternative?

Thoughts on where to land the plane on this? What are others doing?

Thanks,

I've yet to find a query that was so "complex" that it could not be expressed in Linq-to-Sql. In fact, I find the Linq-to-Sql code a bit easier to read than some traditional SQL Statements.

That said, if this is a complex READ command, then I would suggest that you put it into a VIEW and access the view via LINQ. You would have a bit more control over the joins/and ensuring that the underlying SQL is defined for optimum efficiency.

Additionally, you can call Stored Procedures and/or functions in Linq-To-Sql. And the same reasons that you would create a stored procedure in your traditional SQL process is why you would do so in Linq-To-Sql. Let's reiterate that: there is nothing stopping you from running a stored procedure from Linq-to-SQL. I do so when I need to impact a number of records (for example, making a batch change on records).

Use the right tool for the right job. Linq-to-SQL covers a large portion of what you need to do on a daily basis in a typical line-of-business app - so use it there. Devs will get used to it, and will probably also begin to like it - it's really quite powerful and useful!

But yes - there are defintily scenarios where a straight-up SQL query will be a lot easier to use - that's fine, no harm done - that's what ExecuteQuery is for. If you have a bunch of CTE's and complicated joins of various types - you might be able to express that in Linq-to-SQL, but it might be just too much effort and hassle to do it if you already have a T-SQL statement that works....

It takes time to get used to a new way of doing things - give it some time! I'm sure most of your dev will migrate to LINQ step by step. Encourage them, give them tips & tricks, help them where you can. But also accept that in same cases, tricky SQL statements might just be too tricky to rewrite in LINQ (if you already have them and they already work - just keep using them).

So, is it fair to say that some queries are just so complex that they can’t be performed with LINQ?

I would say no for most cases. LINQ to SQL is just an object-relational mapper after all. If a query is difficult with LINQ to SQL, it will be the same or more difficult with plain SQL; plus you have to manage all the database to objects mapping by hand. Anyway as marc_s points, you have ExecuteQuery for exceptional cases.

I have used LINQ to SQL in all my new projects since it was released (ranging from trivial to complex databases), and in 100% cases it has simplified my work and allowed me to write more readable and maintainable code.

Some of our developers find LINQ difficult and migrate back to traditional raw SQL via ExecuteQuery.

I think that hard to believe unless they have plain fear to learn something new. If they can manage to form themselves in LINQ (by reading books, blogs or technical articles; or via courses or mentoring), for sure they will find that it was worth the effort.