Is Linq to SQL worthwhile

[Michael C]

Seems to me like it a bit of a doomed technology. Why bother writing linq
when you can just write the SQL? Seems to me that the point of linq is to
give you sql like abilities on stuff that is *not* in a database. Opinions?

Cheers,
Michael

 

[Arne Vajhøj]

Seems to me like it a bit of a doomed technology. Why bother writing linq
when you can just write the SQL? Seems to me that the point of linq is to
give you sql like abilities on stuff that is *not* in a database. Opinions?

First: my understanding is that LINQ for Entities is effectively
replacing LINQ for SQL.

Second: I agree with you. I prefer plain SQL over all the SQL like
query languages added by various ORM frameworks. But not everyone
agrees with that.

Arne

 

[Frans Bouma [C# MVP]]

Seems to me like it a bit of a doomed technology. Why bother writing linq
when you can just write the SQL? Seems to me that the point of linq is to
give you sql like abilities on stuff that is *not* in a database.

var q = from c in ctx.Customer
group c by c.Country into g
select g;

have fun doing that in SQL

Anyway, Linq gives you the ability to utilize the entity model in your
code to query the DB without the details of the database. For one, you
don't need to write the low-level crap for parameter creation, nor do
you have to care about relationships, nullable FK's etc. You also don't
have to care about typo's as they'll get caught by the compiler.

For example, if I want to sort a list of orders on the country of the
related customer, I can do:
var q = from o in ctx.Order
orderby o.Customer.Country ascending
select o;

How 'customer' and 'order' are related, I don't care, it's taken care of.

As a person who has written a large O/R mapper and a full Linq provider
for that O/R mapper, I can tell you that Linq queries are much more
compact than the SQL it will end up in and therefore less complex than
the SQL it will produce.

Another benefit, although not related to Linq to Sql, is that you can
write generic queries without worrying the database details like 'is
this running on oracle?' etc., that's abstracted away for you. Or the
ability to use .NET methods inside the projection without any effort.

'Linq' isn't just the query, it's the whole pipeline from Linq query to
SQL query to resultset to projection engine to object materializer to
resulting object sequence. So comparing a linq query with a 'sql' query
is apples compared to oranges: you need a whole machinery to execute the
SQL query, to consume the resultset in whatever format, to materialize
objects and to send them back up to the caller in a usable format.
_THAT_ is something you have to write yourself IF you want to use SQL in
..NET code. Did you add that to the equation? Or better: did you realize
that writing that plumbing code is actually similar to writing your own
grid control because you think it's useless to use one off the shelve?

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Andy]

Well, L2S specifically I'm not sure where it's going, but linq to
entities has the huge advantage of compiler checking.

Consider this:
var dr = cmd.ExecuteReader( "select productid, productname, unitprice
from product inner join price on product.productid = price.productid
where price.pricelistid = 1" );

vs.

var prodPrices =
from prods in ctx.Product
join prices in ctx.Price on prods.ProductId equals
prices.ProductId
where prices.PriceListId = 1
select prods;

The latter gets you type checking, you know your sql will break if you
change the database because you'll regen your entities. In the
former... well have fun doing manual search and replace in all your
code.

The other advantage of linq in general is that I can use the same
query over POCOs or Xml.

 

[Jeff Johnson]

Second: I agree with you. I prefer plain SQL over all the SQL like
query languages added by various ORM frameworks. But not everyone
agrees with that.

Count me in as one who agrees with you. I see LINQ to SQL as a crutch for
people who just don't "get" SQL, much like I see DTS and SSIS as a crutch
for non-programmers (although I find SSIS much better than DTS).

 

[Nicholas Paldino [.NET/C# MVP]]

Michael (as well as Arne and Jeff),

I wouldn't debate that there are people who just don't "get" SQL and
that it would seem more intuitive to some to code the statements yourself.
There are people who feel comfortable on both sides of the issue.

However, one of the benefits of LINQ to SQL (or LINQ to Entities) is
that you get compile-time checking against your queries (assuming that the
mapping between the representation in code and your underlying data source
is correct), something you don't get with query strings in code.

Of course, one can put those query strings in stored procedures, but
that's another thread...

 

[Jeff Johnson]

Of course, one can put those query strings in stored procedures, but
that's another thread...

What a nice way of saying "virtually-guaranteed flame war"!

 

[proxyuser]

What a nice way of saying "virtually-guaranteed flame war"!

How so? If you don't mind explaining to someone not in the know....

 

[Nicholas Paldino [.NET/C# MVP]]

Because there is usually a very heated debate about which is a "better"
approach, putting hard coded query strings in your code, or stored
procedures.

 

[Jeff Johnson]

How so? If you don't mind explaining to someone not in the know....

What Nicholas said.

You get a combination of logic vs. religion with the programmer vs. DBA
twist thrown in.

 

[Michael C]

Because there is usually a very heated debate about which is a "better"
approach, putting hard coded query strings in your code, or stored
procedures.

I'm suprised that is even a debate, you should use the most appropriate
method at the time, not overusing/abusing one method. If you're writing
large slabs of sql that builds other sql and then calls sp_executesql then
that is an abuse of sql. If you have large slabs of sql in C# that could be
a stored proc then that is an abuse in the other direction. But certainly
one should lean towards putting as much sql in a stored proc as possible. I
would have thought that would be fairly well established by now.

Michael

 

[Michael C]

Well, L2S specifically I'm not sure where it's going, but linq to
entities has the huge advantage of compiler checking.

I know what you mean but I don't think moving all your sql to linq is the
answer.

Michael

 

[Frans Bouma [C# MVP]]

I know what you mean but I don't think moving all your sql to linq is the
answer.

No-one will say so, however you haven't really provided any argument
where 'SQL' is more efficient to write and better to maintain. Mind you:
'SQL' doesn't run on itself, you have to provide the whole framework to
get a query created, parameters filled, resultset retrieved and objects
materialized.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------