How is using Entity + LINQ not just essentially hard coding my queries?

Question

So I've been developing with Entity + LINQ for a bit now and I'm really starting to wonder about best practices. I'm used to the model of "if I need to get data, reference a stored procedure". Stored procedures can be changed on the fly if needed and don't require code recompiling. I'm finding that my queries in my code are looking like this:

List<int> intList = (from query in context.DBTable
                     where query.ForeignKeyId == fkIdToSearchFor
                     select query.ID).ToList();

and I'm starting to wonder what the difference is between that and this:

List<int> intList = SomeMgrThatDoesSQLExecute.GetResults(
                                  string.Format("SELECT [ID]
                                                 FROM DBTable
                                                 WHERE ForeignKeyId = {0}",
                                  fkIdToSearchFor));

My concern is that that I'm essentially hard coding the query into the code. Am I missing something? Is that the point of Entity? If I need to do any real query work should I put it in a sproc?

Answer 1

The power of Linq doesn't really make itself apparent until you need more complex queries.

In your example, think about what you would need to do if you wanted to apply a filter of some form to your query. Using the string built SQL you would have to append values into a string builder, protected against SQL injection (or go through the additional effort of preparing the statement with parameters).

Let's say you wanted to add a statement to your linq query;

IQueryable<Table> results = from query in context.Table
                            where query.ForeignKeyId = fldToSearchFor
                            select query;

You can take that and make it;

results.Where( r => r.Value > 5);

The resulting sql would look like;

SELECT * FROM Table WHERE ForeignKeyId = fldToSearchFor AND Value > 5

Until you enumerate the result set, any extra conditions you want to decorate will get added in for you, resulting in a much more flexible and powerful way to make dynamic queries. I use a method like this to provide filters on a list.

Answer 2

I personally avoid to hard-code SQL requests (as your second example). Writing LINQ instead of actual SQL allows:

• ease of use (Intellisense, type check...)
• power of LINQ language (which is most of the time more simple than SQL when there is some complexity, multiple joins...etc.)
• power of anonymous types
• seeing errors right now at compile-time, not during runtime two months later...
• better refactoring if your want to rename a table/column/... (you won't forget to rename anything with LINQ becaues of compile-time checks)
• loose coupling between your requests and your database (what if you move from Oracle to SQL Server? With LINQ you won't change your code, with hardcoded requests you'll have to review all of your requests)
• LINQ vs stored procedures: you put the logic in your code, not in your database. See discussion here.

if I need to get data, reference a stored procedure. Stored procedures can be changed on the fly if needed and don't require code recompiling

-> if you need to update your model, you'll probably also have to update your code to take the update of the DB into account. So I don't think it'll help you avoid a recompilation most of the time.

Answer 3

Yes, if you're good at SQL, you can get all that with stored procs, and benefit from better performance and some maintainance benefits.

On the other hand, LINQ is type-safe, slightly easier to use (as developers are accustomed to it from non-db scenarios), and can be used with different providers (it can translate to provider-specific code). Anything that implements IQueriable can be used the same way with LINQ.

Additionally, you can pass partially constructed queries around, and they will be lazy evaluated only when needed.

So, yes, you are hard coding them, but, essentially, it's your program's logic, and it's hard coded like any other part of your source code.

Answer 4

Is LINQ is hard-coding all your queries into your application? Yes, absolutely.

Let's consider what this means to your application.

If you want to make a change to how you obtain some data, you must make a change to your compiled code; you can't make a "hotfix" to your database.

But, if you're changing a query because of a change in your data model, you're probably going to have to change your domain model to accommodate the change.

Let's assume your model hasn't changed and your query is changing because you need to supply more information to the query to get the right result. This kind of change most certainly requires that you change your application to allow the use of the new parameter to add additional filtering to the query.

Again, let's assume you're happy to use a default value for the new parameter and the application doesn't need to specify it. The query might include an field as part of the result. You don't have to consume this additional field though, and you can ignore the additional information being sent over the wire. It has introduced a bit of a maintenance problem here, in that your SQL is out-of-step with your application's interpretation of it.

In this very specific scenario where you either aren't making an outward change to the query, or your application ignores the changes, you gain the ability to deploy your SQL-only change without having to touch the application or bring it down for any amount of time (or if you're into desktops, deploy a new version).

---

Realistically, when it comes to making changes to a system, the majority of your time is going to be spent designing and testing your queries, not deploying them (and if it isn't, then you're in a scary place). The benefit of having your query in LINQ is how much easier it is to write and test them in isolation of other factors, as unit tests or part of other processes.

The only real reason to use Stored Procedures over LINQ is if you want to share your database between several systems using a consistent API at the SQL-layer. It's a pretty horrid situation, and I would prefer to develop a service-layer over the top of the SQL database to get away from this design.

Answer 5

I also wondered about that, but the mentality that database interactivity is only in the database tier is an outmoded concept. Having a dedicated dba churn out stored procedures while multiple front end developers wait, is truly an inefficient use of development dollars.

Java shops hold to this paradigm because they do not have a tool such as linq and hold to that paradigm due to necessity. Databases with .Net find that the need for a database person is lessoned if not fully removed because once the structure is in place the data can be manipulated more efficiently (as shown by all the other responders to this post) in the data layer using linq than by raw SQL.

If one has a chance to use code first and create a database model from the ground up by originating the entity classes first in the code and then running EF to do the magic of actually creating the database...one understands how the database is truly a tool which stores data and the need for stored procedures and a dedicated dba is not needed.