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.