LINQ to SQL, ExecuteQuery, etc

Question

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 cannot 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,

Answer 1

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).