LINQ and SQL in general.

[Alcides]

Hello all,

I learn about LINQ here in this forum. I been a VB.NET programmer for
quite a while and we are using an internal solution for SQL access. I
have some experience with C# and I started to write a blog to share my
experiences with a sql framework.
I would like to hear some opinions about LINQ, is it the main thing
when accessing sql now? what are the main advantages/disavantages
using it. Nothing fancy, just drop a few lines about it...
I read some material but I want to hear the opinion from the field.
I'll be glad to share experience in this area.

Thanks in advance.

Alcides (http://alsql.blogspot.com/).

 

[Cowboy \(Gregory A. Beamer\)]

LINQ is most useful, to me, for easily iterating through objects that use
IEnumerable. This includes sql constructs created by using LINQ to SQL
queries, but it is equally useful, if not more so, for generically querying
sets of objects, etc.

LINQ itself is good for the average web scenario, but can fall flat on high
performance, high load applications. Anything where you have to grab large
amounts of data before a save is not best done with LINQ, for example.

Overall, if you do not think of LINQ as data access, it is a good thing.
When you start thinking of it as a transport "layer", you can end up making
some bad decisions.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************

 

[Michel Walsh]

The main advantage I see is that it can simplify the interfacing between a
database (hierarchy model) and the code (object model).

The big disadvantage is that it is much more complex than SQL to define JOIN
or complex expressions, as when many tables are involved. So, LINQ would
probably be limited to 'query' involving very few tables, or to
view/function stored into the database where the complexity is pre-defined
using SQL.


Vanderghast, Access MVP

 

[Stefan Nobis]

The main advantage I see is that it can simplify the interfacing
between a database (hierarchy model) and the code (object model).

Hierachical databases are from the 70ties and in many use cases do
have much more disadvantages than advantages. Todays DBMS like MS SQL,
Oracle, PostgreSQL etc. are some kind of misguided relational systems
(SQL in many many cases contradicts the relational model and tears
quite some holes in it). BTW XML is a hierachical model (so quite
70ties, not very modern ).

 

[Frans Bouma [C# MVP]]

The main advantage I see is that it can simplify the interfacing
between a database (hierarchy model) and the code (object model).

You ment 'relational model' ? There's nothing hierarchical about a
relational model.

The big disadvantage is that it is much more complex than SQL to
define JOIN or complex expressions, as when many tables are involved.

not necessarily. Perhaps left joins, but joins in general are pretty
straight forward (as in, same keywords as SQL)

So, LINQ would probably be limited to 'query' involving very few
tables, or to view/function stored into the database where the
complexity is pre-defined using SQL.

Can you show an example where the SQL query is simpler than the Linq
query?

FB

Vanderghast, Access MVP


..

--
------------------------------------------------------------------------
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#)
------------------------------------------------------------------------

 

[Michel Walsh]

You are right, I meant relational !

An example? sure. Either a group by with conditions (note the b.kindOf can
be re-written as a WHERE clause, here)

SELECT a.myGroup, SUM(b.myValue)
FROM myTable AS a INNER JOIN myReference AS b
ON a.someID = b.refID AND b.kindOf= 1
GROUP BY a.myGroup
HAVING MIN(b.myValue) >0;


either a ranking (non-equi join)

SELECT a.state, COUNT(*) AS rank
FROM myTable AS a INNER JOIN myTable AS b
ON a.population <= b.population
GROUP BY a.state

which rank each state in accordance with their population.

Even a simple translation:

SELECT a.cityName, COALESCE(b.cityname, a.cityName)
FROM cities AS a LEFT JOIN translations AS b
ON a.cityID=b.cityID


such as 'Byzance' and 'Constantinople' are translated as 'Istanbul, but
'Brasilia, having no match in table translations, stays 'Brasilia. Thanks
thanks to COALESCE, it would be arguably as simple as SQL, in LINQ.


These queries are not elementary, sure, but they do not involve correlated
sub-query either, and only 2 tables.



Vanderghast, Access MVP

 

[Jon Skeet [C# MVP]]

You are right, I meant relational !

An example? sure. Either a group by with conditions (note the b.kindOf can
be re-written as a WHERE clause, here)

SELECT a.myGroup, SUM(b.myValue)
FROM myTable AS a INNER JOIN myReference AS b
ON a.someID = b.refID AND b.kindOf= 1
GROUP BY a.myGroup
HAVING MIN(b.myValue) >0;

That's a groupby followed by a where, as far as I can tell, then select
with a Sum involved.

either a ranking (non-equi join)

SELECT a.state, COUNT(*) AS rank
FROM myTable AS a INNER JOIN myTable AS b
ON a.population <= b.population
GROUP BY a.state

which rank each state in accordance with their population.

Multiple "from" clauses and a "where" clause.

Even a simple translation:

SELECT a.cityName, COALESCE(b.cityname, a.cityName)
FROM cities AS a LEFT JOIN translations AS b
ON a.cityID=b.cityID

such as 'Byzance' and 'Constantinople' are translated as 'Istanbul, but
'Brasilia, having no match in table translations, stays 'Brasilia. Thanks
thanks to COALESCE, it would be arguably as simple as SQL, in LINQ.

I don't know whether using the null coalescing operator in C# will get
translated into a COALESCE in SQL or not, but it's worth a try.

I haven't tried any of the above - and there may well be subtleties I'm
missing - but I personally find LINQ queries *easier* to read because
the flow is always "top to bottom". The logical flow in SQL jumps
around all over the place.

 

[Michel Walsh]

I must add that we generally starts with a problem, then have to find a
solution, in terms of writing in SQL or in LINQ code. Translation from SQL
to LINQ is a little bit artificial. As example, the problem is NOT about to
translate:

SELECT a.workerID
FROM workersSkills AS a INNER JOIN requiredSkills AS b
ON a.skillID=b.skillID
GROUP BY a.workerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM requiredSkills)


into LINQ, but to find the workers having all the required skills. I
STRONGLY suspect someone using LINQ will come with some "for each worker",
get a list of his skills, loop through the required skills list, to see it
they are all contains in the list of skills for the actual worker we just
got, and if so, keep the workerID in a 'result list'. So, yes, that
procedure will be easier to follow, but how many people will need to
'follow' it once it is working? And, it seems to me, the LINQ solution will
be based on simple SQL statements, rather than on ONE, more complex,
statement, but as far as the whole problem has to be solved, the LINQ
solution would be clearly more verbose, because LINQ does not 'lead'
culturally to solve a problem in 'one step' ?

Note that I don't say in any way that SQL is a simple language. You don't
come naturally with the solution above first hour you learn SQL, unless you
are really a genius. But as time passes, you see the 'patterns' as naturally
as a 'for each' loop.


By the way, another great advantage to LINQ: it allows you to write a query
against a database where, in theory, you can be forbidden to write any query
(by some administrative politic).


Vanderghast, Access MVP

 

[Jon Skeet [C# MVP]]

I must add that we generally starts with a problem, then have to find a
solution, in terms of writing in SQL or in LINQ code. Translation from SQL
to LINQ is a little bit artificial. As example, the problem is NOT about to
translate:

SELECT a.workerID
FROM workersSkills AS a INNER JOIN requiredSkills AS b
ON a.skillID=b.skillID
GROUP BY a.workerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM requiredSkills)


into LINQ, but to find the workers having all the required skills. I
STRONGLY suspect someone using LINQ will come with some "for each worker",
get a list of his skills, loop through the required skills list, to see it
they are all contains in the list of skills for the actual worker we just
got, and if so, keep the workerID in a 'result list'.

I would certainly hope that's not the case. It may be until people
actually get used to LINQ - but when devs get more used to thinking in
queries, I would hope they'd do the right thing.

The above is reasonably easy to express in a single LINQ query. Of
course, there are several different ways of solving the problem, even
within a single SQL or LINQ query.

So, yes, that
procedure will be easier to follow, but how many people will need to
'follow' it once it is working? And, it seems to me, the LINQ solution will
be based on simple SQL statements, rather than on ONE, more complex,
statement, but as far as the whole problem has to be solved, the LINQ
solution would be clearly more verbose, because LINQ does not 'lead'
culturally to solve a problem in 'one step' ?

Why do you believe that LINQ doesn't lead to the solution being a
single step? I'd say it does, if you really go for idiomatic LINQ.

Note that I don't say in any way that SQL is a simple language. You don't
come naturally with the solution above first hour you learn SQL, unless you
are really a genius. But as time passes, you see the 'patterns' as naturally
as a 'for each' loop.

Ditto LINQ, I'd say.

By the way, another great advantage to LINQ: it allows you to write a query
against a database where, in theory, you can be forbidden to write any query
(by some administrative politic).

Not sure what you mean by that, to be honest.

 

[Michel Walsh]

Not sure what you mean by that, to be honest.

I mixed two concepts. Once you got the data, locally, you can probably
use LINQ to objects to query the objects, without using the database at all,
without using LINQ to database, in case the admin do not allow your access
to run (write) ad hoc query, on the database (even if they are not action
query).

Vanderghast, Access MVP

 

[Jon Skeet [C# MVP]]

I mixed two concepts. Once you got the data, locally, you can probably
use LINQ to objects to query the objects, without using the database at all,
without using LINQ to database, in case the admin do not allow your access
to run (write) ad hoc query, on the database (even if they are not action
query).

Ah, right. Yes, that's certainly possible - while somewhat inefficient
compared with doing it in the database, of course

Jon

 

[Cowboy \(Gregory A. Beamer\)]

You can simplify a bit more if you move away from LINQ queries and move more
into lambda expressions. The query syntax sometimes paints you into a box,
and there are a few cases where you have to use lambda expressions to create
the syntax you desire, no matter how you try to create the query.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************

 

[Jon Skeet [C# MVP]]

You can simplify a bit more if you move away from LINQ queries and move more
into lambda expressions. The query syntax sometimes paints you into a box,
and there are a few cases where you have to use lambda expressions to create
the syntax you desire, no matter how you try to create the query.

Agreed. This is one of the places where it's nice to know what the
query expression is translated into, so that if you've got a query
expression which you want to modify just a bit (but in a way which the
query expression syntax doesn't directly allow) you can build the non-
q.e. code first and then change it.

I wonder if Resharper 4.0 will have that as a refactoring... it would
be very neat.

Jon

 

[Alcides]

Interesting discussion, it seems linq syntax can get very complex,
naturaly due to complexity of the sql query you are building. If your
application have a lot of complex queries, all the time, may not be
the appropiate solution, I wonder what would be?

But looks like linq can be handle most requests for a standard
aplication, I mean, leaving complex queries to the reports team (using
crystal reports, views and store procedures). Am I right?

 

[Jon Skeet [C# MVP]]

Interesting discussion, it seems linq syntax can get very complex,
naturaly due to complexity of the sql query you are building.

Well, due to the complexity of the query you wish to perform. Don't
forget that LINQ doesn't just target SQL.

If your
application have a lot of complex queries, all the time, may not be
the appropiate solution, I wonder what would be?

I don't know why you'd think LINQ would be inappropriate for complex
queries. If the complexity has to be *somewhere*, I'm happy for it to
be in C# personally.

But looks like linq can be handle most requests for a standard
aplication, I mean, leaving complex queries to the reports team (using
crystal reports, views and store procedures). Am I right?

Well, in some cases a view or stored proc could make sense - but given
that the complexity has to be somewhere, it may well be just as
sensible to do it in the LINQ layer as anywhere else.

 

[Alcides]

Well, due to the complexity of the query you wish to perform. Don't
forget that LINQ doesn't just target SQL.


I don't know why you'd think LINQ would be inappropriate for complex
queries. If the complexity has to be *somewhere*, I'm happy for it to
be in C# personally.


Well, in some cases a view or stored proc could make sense - but given
that the complexity has to be somewhere, it may well be just as
sensible to do it in the LINQ layer as anywhere else.

I got you point. If the query is complex, there's no easy solution. So
keeping that logic in linq/C# make sense.

 

[Christopher Van Kirk]

I thought it was interesting too, but I did some analysis and it
performs like a dog. They'll need to tweak it alot before it's useful
in the real world, I think.

Nice idea though.

 

[Jon Skeet [C# MVP]]

I thought it was interesting too, but I did some analysis and it
performs like a dog. They'll need to tweak it alot before it's useful
in the real world, I think.

I suspect you were doing something wrong if it was performing badly for
you - or at least, there may well have been simple ways to fix the
issue. Can you give details?

 

[Frans Bouma [C# MVP]]

I suspect you were doing something wrong if it was performing badly
for you - or at least, there may well have been simple ways to fix
the issue. Can you give details?

var q = from c in nw.Customers
select new
{
c.Country,
Orders = from o in nw.Orders
where o.CustomerID = c.CustomerID
select new
{
o.OrderID
OrderDetails = from od in nw.OrderDetails
where od.OrderID = o.OrderID
select od
}
};

It takes roughly a second to fetch this little graph using linq to sql
as it executes every nested query on the fly so this leads to:
1 + (#customers) + (#orders) amount of queries.

It can be done in 3 queries. It takes some in-memory lambda
creation/compilation, derived table voodoo, but it's doable. (I'm
almost done with my implementation of this, will blog about it soon)

With loadoptions etc. it's the same thing: they can't deal with
multi-part edges because they use a join of Parent + children to fetch
parent + children. But that's not the way to do it. Fetch children
based on filter on parent, then either use hashes and a merger or
in-memory compiled lambda expressions to merge the rows.

Sure, for flat sets, it's ok to some extend, however with nested sets,
it falls flat on its face, hard. Which is odd, considering the fact
that MS had 'spans' already in objectspaces (although their
implementation in that framework also sucked)

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#)
------------------------------------------------------------------------

 

[Jon Skeet [C# MVP]]

var q = from c in nw.Customers
select new
{
c.Country,
Orders = from o in nw.Orders
where o.CustomerID = c.CustomerID
select new
{
o.OrderID
OrderDetails = from od in nw.OrderDetails
where od.OrderID = o.OrderID
select od
}
};

It takes roughly a second to fetch this little graph using linq to sql
as it executes every nested query on the fly so this leads to:
1 + (#customers) + (#orders) amount of queries.

Right. Definitely bad. I'm certainly not trying to defend LINQ to SQL
as great in all respects - it's definitely a "v1" product, for
starters.

However, I think that Christopher's *blanket* statement of "it performs
like a dog" is *way* too general. If he'd said, "I found some
situations where it executed more queries than I wanted, and I couldn't
work out a way to fix that" it would have been a much more reasonable
statement.

It can be done in 3 queries. It takes some in-memory lambda
creation/compilation, derived table voodoo, but it's doable. (I'm
almost done with my implementation of this, will blog about it soon)

Of course, in the above case you could probably get away with fetching
all the necessary fields from all the rows of the appropriate tables,
then joining it all up in memory. Not so each when you only want
particular rows though

With loadoptions etc. it's the same thing: they can't deal with
multi-part edges because they use a join of Parent + children to fetch
parent + children. But that's not the way to do it. Fetch children
based on filter on parent, then either use hashes and a merger or
in-memory compiled lambda expressions to merge the rows.

Sure, for flat sets, it's ok to some extend, however with nested sets,
it falls flat on its face, hard. Which is odd, considering the fact
that MS had 'spans' already in objectspaces (although their
implementation in that framework also sucked)

Does the Entity Framework do any better on this, do you know?