Is Linq to SQL any good?

[Michael C]

I'm a big fan of linq but in my opinion linq to sql is a complete waste of
time. Why on earth take something that is already complex (sql) and make it
more complex and difficult to write by creating a translation layer. I see
people here struggling to write linq to sql queries. It also creates the
problem of being locked into a particular product with regards to the
database which is exactly the problem I thought a translation layer would be
designed to solve. I was assuming that this would be largely reflected in
community opinion but this doesn't seem to be the case. Am I missing
something? I'd be interested to hear from people who love it and people who
loath it. If you do loath it please reply also as generally in newsgroups on
those who disagree reply.

Thanks in advance,
Michael

 

[Arne Vajhøj]

I'm a big fan of linq but in my opinion linq to sql is a complete waste of
time. Why on earth take something that is already complex (sql) and make it
more complex and difficult to write by creating a translation layer. I see
people here struggling to write linq to sql queries. It also creates the
problem of being locked into a particular product with regards to the
database which is exactly the problem I thought a translation layer would be
designed to solve. I was assuming that this would be largely reflected in
community opinion but this doesn't seem to be the case. Am I missing
something? I'd be interested to hear from people who love it and people who
loath it. If you do loath it please reply also as generally in newsgroups on
those who disagree reply.

If we ignore the LINQ to SQL vs LINQ to EF aspect. You should use
LINQ to EF not LINQ to SQL.

..NET wanted an ORM.

Many people including me like ORM's for certain types of usage.

There is a bad tradition for creating special query languages
for ORM. Hibernate has HQL etc.etc..

I don't like the idea idea of creating another query
language, when we have a standard query language SQL.

MS decided to put the language directly into the
programming language and not as a string.

I don't see many benefits from doing that. The usual
excuse is the compile time check. But I don't see
SQL syntax errors as a major problem.

You are correct that both to SQL and to EF are behind
other ORM frameworks regarding database independence.

So to summarize my view:
- it is something
- it works
- but I am not that impressed

Arne

 

[Mr. Arnold]

I'm a big fan of linq but in my opinion linq to sql is a complete waste of
time. Why on earth take something that is already complex (sql) and make it
more complex and difficult to write by creating a translation layer. I see
people here struggling to write linq to sql queries. It also creates the
problem of being locked into a particular product with regards to the
database which is exactly the problem I thought a translation layer would be
designed to solve. I was assuming that this would be largely reflected in
community opinion but this doesn't seem to be the case. Am I missing
something? I'd be interested to hear from people who love it and people who
loath it. If you do loath it please reply also as generally in newsgroups on
those who disagree reply.

Well, if one went to Linq-2-SQL, then one is going to stick with SQL
Server as the database.

Really, how many companies have you seen that actually changed to
another database like Oracle or MySQL from SQL server? Most companies
will not make such a change.

You shouldn't under estimate Linq-2-SQL as it does have its place,
particularly with N-Tier architect applications.

<http://lostintangent.com/2007/12/12/adonet-data-services-part-1-services-contd/>

<http://www.silverclouddevelopment.com/mydocs/Chapter 2 - WCF RIA SL BUS APP start .pdf>

I myself I like ADO.Entity Framework, that allows one to use other
database vendors, other than, MS SQL Server.

If a Windows based application was to consume a service like a classic
ASP.NET Web service or one of the WCF services such as a Web, TCP/IP,
Named Pipe, or MSMQ service, then most likely Linq-2-SQL or ADO.NET
Entity Framework is going to be the technology use to access the
database on the back-end.

It's too much work to use sproc or t-sql inline with a datareader to
build objects of data, when those objects already exist on the virtual
database model with Link-2-SQL or ADO.NET Entity Framework,and objects
traveling between the client and a service in a N-Tier application.

The purpose of Linq is what?

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.

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.


The above holds true for ADO.NET Entity Framework, an ORM, and
Linq-2-Object and object encapsulation,inheritance and all that comes
with an object.

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

One gets use to using objects and things become simple.

 

[Mr. Arnold]

On 29-03-2010 19:55, Michael C wrote:

I don't see many benefits from doing that. The usual
excuse is the compile time check. But I don't see
SQL syntax errors as a major problem.

You are correct that both to SQL and to EF are behind
other ORM frameworks regarding database independence.

How mature are either one of them compared to nHibernate or others? They
just came out the gate. But as far a SOA, neither one of them are that
bad, and I can only expect that each, particularly EF, will only get
better.

But as far as EF or Link-2-SQL in a desktop, console or NT service
application, I'll take Linq-2-SQL or EF and the CURD operations over
T-SQL, sproc, datatable and dataset any day of the week.

 

[PIEBALD]

Dunno. I have no use for it. I prefer embedded SQL.

 

[Andy O'Neill]

If we ignore the LINQ to SQL vs LINQ to EF aspect. You should use
LINQ to EF not LINQ to SQL.

.NET wanted an ORM.

Many people including me like ORM's for certain types of usage.

There is a bad tradition for creating special query languages
for ORM. Hibernate has HQL etc.etc..

I don't like the idea idea of creating another query
language, when we have a standard query language SQL.

MS decided to put the language directly into the
programming language and not as a string.

I don't see many benefits from doing that. The usual
excuse is the compile time check. But I don't see
SQL syntax errors as a major problem.

You are correct that both to SQL and to EF are behind
other ORM frameworks regarding database independence.

So to summarize my view:
- it is something
- it works
- but I am not that impressed

Arne

I thought exactly that.
Been writing SQL for 15 years now, didn't fancy another database access
language.
LINQ has kind of grown on me.
I find I rarely have to write complex LINQ.
When I do, I put the logic in a stored procedure instead or break it down
into layers and use interceptinh filter/ pipeline filter or whatever you
like to call the pattern.

Linq to sql was always intended as a stopgap while they did entity
framework. I think MS should really have signposted that rather better..

 

[Harlan Messinger]

I'm a big fan of linq but in my opinion linq to sql is a complete waste of
time. Why on earth take something that is already complex (sql) and make it
more complex and difficult to write by creating a translation layer.

When I first started to see what was involved in creating the
translation layer, I felt the same way, and then I got to the part where
SQLMetal does the whole translation for you, and my concerns disappeared.

I see
people here struggling to write linq to sql queries.

We struggle with every new technology Microsoft adds. I struggled
just to figure out LINQ and I'm still learning it, but I love it. I only
started learning LINQ-to-SQL two weeks ago, and I like it.

It also creates the
problem of being locked into a particular product with regards to the
database which is exactly the problem I thought a translation layer would be
designed to solve.

True, but then I've also never bothered to worry about whether my SQL
queries are written in ANSI SQL. So far I've never had to move an
application from SQL Server to another DBMS--and if we did, we'd also
have to replace every report created using Reporting Services, every
notification routine that uses SQL Server's mailing agent, etc.

I was assuming that this would be largely reflected in
community opinion but this doesn't seem to be the case. Am I missing
something? I'd be interested to hear from people who love it and people who
loath it. If you do loath it please reply also as generally in newsgroups on
those who disagree reply.

Features I like:

* Intellisense.

* Economy of expression in joins over foreign key relationships:

item.Category.CategoryName

is much easier to write than

SELECT categoryName FROM Items
INNER JOIN Category
ON Items.categoryId = Category.categoryId WHERE ....

This is something I've wished for years that they'd build into SQL! I
long ago got tired of having to "remind" the database, repeatedly and
tediously, what it already "knows" about how two tables are related.

* Not having to manually create the connection and set up the query and
execute it and receive the result in a DataSet or DataReader or
DataTable over and over and over and over. The autogenerated data
context file does all that for you.

Now, people say you ought to use EF, and I agree that that goes even one
step further toward Nirvana, because it makes those pesky joins even
more transparent in what I think of as the "look-up" relationships, like
where you have an Employee and want a query that includes the name of
his Division, his Employee Type, his Address, etc., and you need joins
to get all of that because the Employee table only has divisionId,
employeeTypeId, and addressId. It's fairly easy in LINQ-to-SQL:
employee.Division.DivisionName, employee.Address.City, etc., but EF
spares you from even that, and you wind up with Employee2.DivisionName,
Employee2.City. You can also accomplish this by defining a *view* called
Employee2 in SQL Server and querying that, but EF is about meeting your
app's needs and applying business rules rather than data layer rules.

But having said that, for a relatively small application where the
people who code the app are the same people who build the data model,
LINQ-to-SQL seems like an effective compromise.

 

[vanderghast]

You don't use LINQ to write SQL


but to GET the data with the 'right impedance' : data in relational
databases is not an instant match with object oriented languages, like C#,
where inter table relations become C# collections (as example). So, you can
use AdoNet, or LINQ, and if you USE relations between your tables, those
models get the 'collections' objects created for you, with or without strong
name safeguard, client side (disconnected data is the norm) instant
validations, with more or less effort. LINQ is not a replacement to SQL, but
a kind of "HDMI cable" to your data, from C#.

Using 'ad hoc' query is not equivalent to LINQ, since an SQL statement does
not hold any of the relations. Try adding a record on an 'ad hoc' query
involving MANY tables. Sure, if your database has only one huge
'spreadsheet' like table, or if your problem involves only one table, you
can do a lot of mileage with ad hoc query, but that is another story,
totally, if you really have RELATIONS between your db-tables: the 'ad hoc'
statement method sounds, at least to me, a serious impedance mismatch for a
generic case!



Vanderghast, Access MVP

 

[Michael C]

Have you tried embedding SQL into C# code? It's ugly, hard to
maintain, has no compile-time checking, little support for making data
types match up, etc.

But don't you just put the sql into a stored proc? The only sql code I've
got in C# is very simple code. Most of the time I find the sql in stored
procs gets to the level of complexity that it would be impossible or at
least highly impractical in linq. In the case of complex sql it's difficult
enough to get an optimised result without adding another level of
translation. Surely then linq is only suitable for fairly simple sql
statements which defeats the advantages of compile time checking.

You may find this helpful: (Link off Oreilly Books site)

http://www.linqpad.net/WhyLINQBeatsSQL.aspx

Thanks, I'll have a look. I'm getting an error message "service unavailable"
but I presume that is just temporary.

 

[Michael C]

You may find this helpful: (Link off Oreilly Books site)

http://www.linqpad.net/WhyLINQBeatsSQL.aspx

I agree with most of what he said in the article and I DO think linq is
significantly better than sql. I just think that translating linq to sql is
not the solution. I don't think the decision as to whether to put an sql
statement onto the server or client should be based on what tools you are
using. If the sql belongs in a stored proc then that's were it belongs. If
it makes sense to write the sql on the client then that is fine but I
wouldn't say that using linq was a valid reason. What ever happened to the
idea of making tables only available via a stored proc? We don't make
decisions to abandon methologies like that based on a tool available in the
language of choice.

If we assume we have a complex query to write and we are pretty good at
writing sql, then the sql we write is going to be close to as efficient as
possible. This can only go down when we use linq as it's an extra
translation level and will reduce our ability to optimise the query.

The article also appears to be missing all the "latest" features introduced
5 years ago in sql 2005. Considering we can only talk to sql server at this
time via linq it seems silly to ignore all it's latest features.

Cheers,
Michael

 

[Michael C]

Have you tried embedding SQL into C# code? It's ugly, hard to
maintain, has no compile-time checking, little support for making data
types match up, etc.

You may find this helpful: (Link off Oreilly Books site)

http://www.linqpad.net/WhyLINQBeatsSQL.aspx

I just wanted to add that I think this article highlights what I really
don't like about linq. It's like introducing this fantastic new language
with all these really cool features and then saying, BTW, it converts to the
old language to compile it. Think asp.net that converts to asp script.

Michael

 

[Harlan Messinger]

I agree with most of what he said in the article and I DO think linq is
significantly better than sql. I just think that translating linq to sql is
not the solution. I don't think the decision as to whether to put an sql
statement onto the server or client should be based on what tools you are
using.

The author doesn't suggest this. In fact, he indicates the opposite in
the section titled "When not to use LINQ".

If the sql belongs in a stored proc then that's were it belongs.
Yes.

If
it makes sense to write the sql on the client then that is fine but I
wouldn't say that using linq was a valid reason. What ever happened to the
idea of making tables only available via a stored proc? We don't make
decisions to abandon methologies like that based on a tool available in the
language of choice.

If we assume we have a complex query to write and we are pretty good at
writing sql, then the sql we write is going to be close to as efficient as
possible. This can only go down when we use linq as it's an extra
translation level and will reduce our ability to optimise the query.

I bet you don't write your programs in assembler language because you
can do a better job of optimizing your code than the C# compiler can!
Sure, if there's a very special case where you're getting a result that
is substantially suboptimal, then deal with it. But (a) don't
underestimate the effectiveness of your tools and (b) don't overlook
reasonable compromises between the utmost in runtime efficiency and the
need for efficiency in coding and maintainability.

 

[Harlan Messinger]

I just wanted to add that I think this article highlights what I really
don't like about linq. It's like introducing this fantastic new language
with all these really cool features and then saying, BTW, it converts to the
old language to compile it. Think asp.net that converts to asp script.

The C# compiler converts all your code into MSIL. You should just write
the MSIL yourself, right? And Visual Studio, with its automatically
generated Windows forms and Web forms and so on, is a crutch. All it
does is create code you could write yourself directly.

 

[Mr. Arnold]

I agree with most of what he said in the article and I DO think linq is
significantly better than sql. I just think that translating linq to sql is
not the solution. I don't think the decision as to whether to put an sql
statement onto the server or client should be based on what tools you are
using. If the sql belongs in a stored proc then that's were it belongs. If
it makes sense to write the sql on the client then that is fine but I
wouldn't say that using linq was a valid reason. What ever happened to the
idea of making tables only available via a stored proc? We don't make
decisions to abandon methologies like that based on a tool available in the
language of choice.

The thing is that a lot of developers don't want to be bothered with
inline T-SQL or sprocs. That's the bottom line. It's kind of clear here
that you have not done any N-Tier application development that thrives
on using a technology such as Linq-2-SQL

If we assume we have a complex query to write and we are pretty good at
writing sql, then the sql we write is going to be close to as efficient as
possible. This can only go down when we use linq as it's an extra
translation level and will reduce our ability to optimise the query.

You ever used SQL Server's Profiler to look a the T-SQL being executed,
and make adjustments to the T-SQL being produced by a Linq query.

You ever work with compiled linq queries? You ever work with precompiled
views of the model? What you say is simply not correct. And in todays
database environment such as SQL server, a T-SQL batch is created by
the Linq query and is submitted to SQL server to be executed with a
sproc functionality that is cached on SQL Server.

So, the Linq translation only happens once with a compiled linq query,
as the compiled linq query stays in memory to be executed again, again
and again with no translation needed to T-SQL only parameter
substitution on the tree, because it has been translated onetime to
T-SQL and doesn't not need to be translated over and over.

In addition to this, with SQL Server's ability to cache execution plans
for a previously exexuted T-SQL batch makes things very efficient with
Linq-2-SQL or ADO.NET Entity Framework.

The article also appears to be missing all the "latest" features introduced
5 years ago in sql 2005. Considering we can only talk to sql server at this
time via linq it seems silly to ignore all it's latest features.

????????

 

[Jeff Johnson]

The thing is that a lot of developers don't want to be bothered with
inline T-SQL or sprocs. That's the bottom line.

I cannot stress how correct this comment is. I think the majority of
programmers who aren't big fans of LINQ-to-SQL are what I call "data
programmers." They understand BOTH their programming language/platform AND
their DBMS. Those of us who are data programmers have difficulty
understanding that a lot of people AREN'T data programmers and don't want to
be bothered with all that low-level SQL stuff. Those folks are the ones who
like things like LINQ-to-SQL, and we data programmers tend to think they're
using a crutch. (They are, and they're happy to have it.)

 

[Mr. Arnold]

I cannot stress how correct this comment is. I think the majority of
programmers who aren't big fans of LINQ-to-SQL are what I call "data
programmers." They understand BOTH their programming language/platform AND
their DBMS. Those of us who are data programmers have difficulty
understanding that a lot of people AREN'T data programmers and don't want to
be bothered with all that low-level SQL stuff. Those folks are the ones who
like things like LINQ-to-SQL, and we data programmers tend to think they're
using a crutch. (They are, and they're happy to have it.)

I have written my fair share of sprocs from 1999 up until recently the
last year or so, when Linq-2-SQL and ADO.NET Entity Framework came upon
the scene. I have also used nHibernate in the past.

I always considered it tedious work writing sprocs and testing them. And
on top of this, some organizations have so many sprocs they don't know
what to do with them. They are always going through some kind of pruning
sprocs process.

Thank God for Linq-2-SQL and ADO.NET Entity Framework, because I don't
have to be bothered with T-SQL or sprocs.

 

[Harlan Messinger]

I cannot stress how correct this comment is. I think the majority of
programmers who aren't big fans of LINQ-to-SQL are what I call "data
programmers." They understand BOTH their programming language/platform AND
their DBMS.

In my case, I understand SQL queries very well and have written very,
very intricate ones--and I find them very, very tedious to write and I'm
thrilled by any innovation that cuts deeply into the tedium.

Those of us who are data programmers have difficulty
understanding that a lot of people AREN'T data programmers and don't want to
be bothered with all that low-level SQL stuff. Those folks are the ones who
like things like LINQ-to-SQL, and we data programmers tend to think they're
using a crutch. (They are, and they're happy to have it.)

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

 

[Jeff Johnson]

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

REAL programmers use:

COPY CON myprog.exe

 

[Michael C]

The C# compiler converts all your code into MSIL. You should just write
the MSIL yourself, right? And Visual Studio, with its automatically
generated Windows forms and Web forms and so on, is a crutch. All it does
is create code you could write yourself directly.

I was expecting answer somewhere along these lines. The point is there are
good and bad ways to do this. Converting to MSIL is obviously a good way to
do things, converting to assembler and then compiling that would be ok.
Coverting C# to perl would obviously be a bad way to do things and I think
linq to sql is similar. Linq can only ever have less ability than sql.

 

[Michael C]

The author doesn't suggest this. In fact, he indicates the opposite in the
section titled "When not to use LINQ".

But most of my sql is in stored procs, everything except for "SELECT * FROM
X". THe author is suggesting this new tool is useful so I presume he's
suggesting to move sql out of stored procs.

I bet you don't write your programs in assembler language because you can
do a better job of optimizing your code than the C# compiler can!

See my post below to answer this one.

Michael