Is Linq to SQL any good?

[Jeff Johnson]

Low level Jeff? Come on, Joe Celko as difficult a human being as he is, is
always telling us that SQL is *high level*. I certainly wouldn't hire
someone who wanted to avoid writing sql. Working in a business is ALL
about data and if someone doesn't want to know THE data language then they
should go write games.

Stop splitting hairs, please. It is low level IN RELATION to things like
LINQ-to-SQL. Anything that GENERATES SQL for you is at a higher level in my
view than when you write the SQL yourself, so therefore SQL itself is at the
lowest level of that scenario. Get it?

 

[vanderghast]

Jeff Johnson wrote: (...)

SQL is a crutch for people who don't want to have to write their own code
for matching up items in flat files.

Most people who write their own code make a lot of assumptions and write
just one strategy. That is even close to be proprietary data source, and
most companies are glad to stay away from that kind of dependency.

SQL can decide to use one from many different strategies, so it is not only
the equivalent to write ONE procedure, but to write all those susceptible of
performance, (including structure of the data, ... most relational databases
do not use ''flat files"), to also write some decision part (to select which
strategy seems the most promising given an actual set of conditions) and to
do that in a multi-user environment (lock, security, ... ) with included
possibilities of adding, erasing, updating the data, and to mimic those
operations for a while (transactions) and possibly reverse them.


Not every one need a database, but reducing the skills of those who use
database to just 'laziness is gross.



Vanderghast, Access MVP

Vanderghast, Access MVP




Vanderghast, Access MVP

 

[vanderghast]

I wasn't sure, I didn't take a chance...

Vanderghast, Access MVP

 

[Alois Matthias Stockklauser]

LINQ in itself is a tremendous advantage compared to cut and
paste SQL statements especially in terms of different types.
LINQ to SQL is only halfway.
LINQ to EF suffered the one-way-syndrome (db -> model) in 2008
versions.
Nonetheless, compared to writing SQL-DAOs for 20+ tables is
laborious, and LINQ to EF can straighten that process
perfectly.
In a recently published book, "Introducing .NET 4", Alex
Mackey states that LINQ to EF translates DB objects 1:1 into
classes. First of all, there is no such thing like a DB
object, they are still entities or tables - and that's the
problem: Tables are no objects. Second, LINQ to EF (2008) is
cute enough to cancel association tables required in n-m-ERs
within the static class model, e.g. Issue - IssueRequirement -
Requirement of DB will be automatically represented by Issue -
Requirement with appropriate access properties.
Of course, many people like their xxxSQL stored procedures,
maybe they like coding more than anything else.
I will publish a short lab next week.

 

[Hamish McCreight]

Fact: SQL is the lingua franca for processing SQL databases.

Fact: You write stored procedures and call them as required in code,
typically via a business layer. I will send anyone I find embedding SQL
in their code on a re-education program !

Fact: Millions of books use embedded SQL in their examples,
simply because they require working examples BUT the best books will
also strongly advise that production systems should factor our database
access code into stored procedures. But it seems that a loads of
developers continue to think that embedding SQL in code , or similar,is
how to do it.

Fact: LINQ to SQL has nowhere near the power or constructs of SQL. AND
if you bother to try it with SQL Server and use the Profiler, you will
see that all it does is generate SQL. Just use a stored procedure
instead. Why should you have to learn another data access language? It's
absurd.

 

[Mr. Arnold]

Fact: SQL is the lingua franca for processing SQL databases.

Fact: You write stored procedures and call them as required in code,
typically via a business layer. I will send anyone I find embedding SQL
in their code on a re-education program !

No, one calls them from a Data Access layer and not the Business layer. The
Business layer calls the the Data Access layer.

Fact: Millions of books use embedded SQL in their examples,
simply because they require working examples BUT the best books will
also strongly advise that production systems should factor our database
access code into stored procedures. But it seems that a loads of
developers continue to think that embedding SQL in code , or similar,is
how to do it.

Millions of books huh? Millions of different books about T-SQL have NOT been
written. You show where 1 millon or more different books have been written
about T-SQL.

Fact: LINQ to SQL has nowhere near the power or constructs of SQL. AND
if you bother to try it with SQL Server and use the Profiler, you will
see that all it does is generate SQL. Just use a stored procedure
instead. Why should you have to learn another data access language? It's
absurd.

Linq and Linq-2-SQL in general are about the use of objects and OOP(s),
with the underlying access language being used to access the database being
T-SQL used by Linq-2-SQL. After all, that's why they have ORM(s) Object
Relational Mapping solutions.

You're talking about production systems enterprise level then you had better
figure out how to write them.

http://www.dofactory.com/Framework/Framework.aspx.


What is Object-oriented-programming?

(OOP) is a programming paradigm that uses "objects" and their interactions
to design applications and computer programs.

The key concepts of OOP are the following:

Class

Object

Instance

Method

Message passing

Inheritance

Abstraction

Encapsulation

Polymorphism

Decoupling



http://en.wikipedia.org/wiki/Object-oriented_programming



What is Language Integrated Query?

LINQ is a Microsoft .NET Framework component that adds native data querying
capabilities to .NET languages.

Microsoft LINQ defines a set of query operators that can be used to query,
project and filter data in arrays, enumerable classes, XML, relational
database, and third party data sources. While it allows any data source to
be queried, it requires that the data be encapsulated as objects. So, if the
data source does not natively store data as objects, the data must be mapped
to the object domain. Queries written using the query operators are executed
either by the LINQ query processing engine or, via an extension mechanism,
handed over to LINQ providers which either implement a separate query
processing engine or translate to a different format to be executed on a
separate data store (such as on a database server as SQL queries). The
results of a query are returned as a collection of in-memory objects that
can be enumerated using a standard iterator function such as C#'s foreach.

Many of the concepts that LINQ has introduced were originally tested in
Microsoft's C? research project. LINQ was released as a part of .NET
Framework 3.5 on November 19, 2007.

http://en.wikipedia.org/wiki/Language_Integrated_Query



What is Linq-to-SQL?

LINQ to SQL, a component of Visual Studio Code Name "Orcas", provides a
run-time infrastructure for managing relational data as objects without
losing the ability to query. It does this by translating language-integrated
queries into SQL for execution by the database, and then translating the
tabular results back into objects you define. Your application is then free
to manipulate the objects while LINQ to SQL stays in the background tracking
your changes automatically.

http://msdn.microsoft.com/en-us/library/bb425822.aspx

 

[Alberto Poblacion]

Fact: You write stored procedures and call them as required in code,
typically via a business layer. I will send anyone I find embedding SQL
in their code on a re-education program !

This is somewhat outdated. While this was true in the times of Sql
Server version 7 and earlier, the need for storing all of your sql inside
stored procedures is nowadays greatly diminished.
One of the reasons for doing this was that the sql got parsed and
optimized at the time of creation of the SP, while this had to be done each
time that the query was run if the query was sent from the client code. This
no longer happens: Modern servers contain a cache that preserves the
compiled query, so it doesn't have to be compiled each time that the client
sends it.
Another reason was to avoid SQL injection attacks, since the stored
procedure used parameters which avoided concatenating user input to the
queries. But once again, modern clients such as ado.net allow you to
parameterize the queries, so this reason for using SPs no longer holds
valid.
A third reason for using SPs was the ability to control security. You
could, for instance assign permissions to a user to execute a procedure, but
not grant permissions on any of the underlying tables used by the procedure.
This is still a valid reason for using an SP. But most of the times the
developers and DBAs do not assign permissions at this level, so this feature
is left unused and therefore it is not enough justification to require
moving all the code into SPs.

So, in summary, the need for using SPs is vastly diminished in modern
servers as compared to old ones. While they are still a very valid tool, and
any _complex_ sql logic should still be moved into SPs, it is no longer true
that each and every simple query should be moved there. It is perfectly
valid to leave simple SELECTs (for instance) embedded in the client code.
(Note: "Embedded" in the client code does _not_ mean "scattered everywhere
over all layers of client code").

 

[vanderghast]

You ASSUME that LINQ is about replacing SQL while it is about interfacing
data with OOP. Indeed, if your data is relational, spanning multiple tables,
SQL does a great job as far as you need a 'rectangular' spreadsheet like
presentation of the data, but a tabular representation is not necessary
the prefered representation with objects technology and that is one of
LINQ job to bridge over these two different worlds.

So even *if* your claims were still valid, in themselves, they are nothing
more than about what you THINK of LINQ, not about what LINQ is.


Vanderghast, Access MVP

 

[Arne Vajhøj]

This is somewhat outdated. While this was true in the times of Sql
Server version 7 and earlier, the need for storing all of your sql
inside stored procedures is nowadays greatly diminished.
One of the reasons for doing this was that the sql got parsed and
optimized at the time of creation of the SP, while this had to be done
each time that the query was run if the query was sent from the client
code. This no longer happens: Modern servers contain a cache that
preserves the compiled query, so it doesn't have to be compiled each
time that the client sends it.
Another reason was to avoid SQL injection attacks, since the stored
procedure used parameters which avoided concatenating user input to the
queries. But once again, modern clients such as ado.net allow you to
parameterize the queries, so this reason for using SPs no longer holds
valid.
A third reason for using SPs was the ability to control security. You
could, for instance assign permissions to a user to execute a procedure,
but not grant permissions on any of the underlying tables used by the
procedure. This is still a valid reason for using an SP. But most of the
times the developers and DBAs do not assign permissions at this level,
so this feature is left unused and therefore it is not enough
justification to require moving all the code into SPs.

So, in summary, the need for using SPs is vastly diminished in modern
servers as compared to old ones. While they are still a very valid tool,
and any _complex_ sql logic should still be moved into SPs, it is no
longer true that each and every simple query should be moved there. It
is perfectly valid to leave simple SELECTs (for instance) embedded in
the client code. (Note: "Embedded" in the client code does _not_ mean
"scattered everywhere over all layers of client code").

The use of parameters is not exactly a feature of modern clients.
It is part of ADO. It is part of ODBC. I can not find any docs
by I would assume that it has always been in ODBC => since 1992.
For Windows programming that means practically always.

There are a fourth reason for using SP's: executing multiple
statements with single round trip between client and database.

Arne