Re: Are Linq-SQL methods commutative

Jon Skeet [C# MVP] wrote:

On Nov 27, 9:33 am, "Frans Bouma [C# MVP]"
<perseus.usenetNOS...@xxxxxxxxx> wrote:
I don't see why there'd be any explicit need for documentation on
this - it seems pretty obvious to me. If you want to skip some
results and then take a certain number of the rest, you do Skip
and then Take. If you want to take some results and then skip
within that (which is a slightly odd thing to do, admittedly) you
do Skip and then Take.

How is it not just intuitive?

Because skip/take equivalents in SQL work on the same
projection result, while in Linq they don't.

No - in LINQ each step effectively works off the result of the
previous step. That's one of the fundamentals of LINQ, and people
should know it before they write too much code which assumes that
LINQ=SQL.

With that knowledge they can't do a single thing if they are writing a
linq query which has to meet a SQL query they have in mind.

You should look less at specs and more at what users WANT
TO DO. Time and time again you post a reply with how the syntaxis
is, how the specs says how it works etc. etc. All fine, but that's
completely not hte point here: the point is that the user wants to
PAGE through a resultset.

Okay, so create a Page extension method which does a Skip then a Take.
It's really easy to do - and I could certainly agree that it would
have made logical sense to include that in .NET 3.5. However, it's
*also* nice to be able to have those two operations separately.

You mean to be able to just skip or just take? Sure. The sad thing is
that when they're used together in the same projection scope, they are
resulting in a different SQL query than used separately. IMHO that
sounds like they should have been tied together in a single statement.

How do you PREVENT developers to make this error? Exactly,
by specifying a pagenumber and a page size instead of skip/take OR
by specifying that the order doesn't matter.

So write that method, and use that everywhere. Create an FXCop rule
suggesting that Skip/Take should be avoided. Just don't take Skip/Take
away from people who want them :)

I'm not saying they should be dropped :). I suggest that if they are
used together in a query, people should not be bothered with an order,
OR, if they are used in the wrong order, they should be warned. Similar
to use ThenBy first, which is also not correct.

As you can fetch any row set with skip first following by take, they
should have forced it to have that order to avoid problems.

Filtering and ordering are effectively orthogonal, unless you can
build a filter which depends on the order. Skipping and taking are
clearly not orthogonal.

In SQL, order by is done on a projected resultset. In Linq,
this isn't always clear, because you can place an order by in a new
projection scope, and you don't know IF that results in a subquery
(projection scope) or not. My point was that MS should have made
the same limitation to linq as ISO did to SQL: Order By is the last
statement in the query. While order is important for extension
methods like skip/take specified directly, it's not for order by
apparently.

There's no logical reason in LINQ *in general* to force ordering to be
the last operation. (In fact, it never is - you always want to either
group or select at the end.)

that's what the spec says. Ordering is always done after projection,
as otherwise there's no way you CAN order, you can only order
resultsets. Check Warren's blog, I believe it's the last entry where he
introduces a complex piece of code to move up a wrongly placed order by
statement.

You always come at LINQ as if it should be the same as SQL - LINQ is
more than SQL, and if that occasionally makes the SQL side less than
ideal, that's a hit I'm certainly willing to take.

It might look that way, but I'm just hammering on the essential
element that when using Linq for DATABASE (!) queries, you have to
realize that the target is a very expensive resource which can be
brought down to its knees with the wrong queries, and this isn't
obvious.

When I started LLBLGen Pro I too found it logical that people would
simply learn our query api and be done with it. However we quickly
learned that people often think from the other side: they have a stored
proc which has to be migrated to C# code (and in this context thus to a
linq query) or are simply thinking in SQL as that's what they know and
are wrestling with the query api because it doesn't give them the
freedom a SQL statement gives them: the SQL statement is the one
executed on the DB, a linq query is an abstraction layer above that, so
to reach what you want, namely the SQL query, you have to know how that
linq query translates to that SQL query so if you have that SQL query
in your head, you can write the Linq query :)

Trust me, in the newsgroups here we'll see a lot of people having a
SQL statement which they want to convert to Linq queries and have a
hard time.

That's why I find it a bit of a dissapointment that Linq lacks the
fine grained control elements to make that conversion so much more
easier. Because that's what people want: they want to write a Linq
query that mimics what they have in mind in SQL.

That seems pretty sensible to me.

Yes, because you don't understand what the word 'intention'
means when a developer writes code. You don't seem to grasp the
little fact that a developer who makes a mistake by writing ACB,
while the intention was ABC, should be WARNED with an error or
warning that ACB isn't going to work probably.

I think it would be nice if LINQ to SQL barfed at runtime rather than
executing the probably-incorrect query, but I don't think it should be
a compile-time error: that would require the compiler to know way
too much about what's going on.

Don't you agree that an exception at runtime is simply too late?
What's the use of a runtime exception, when code analysis could have
shown a warning up front. ?

You seem to find it 'sensible' that people can do that.
That's what I find odd. But I disgress, according to the
syntax/specs everything seems logical, because it is in the spec,
however precisely that isn't the point at all here.

I think we're coming back to our fundamental disagreement: I don't
think people should expect to be able to use LINQ without making
mistakes if they don't take a little bit of time to learn about it
first. I don't expect that about any technology - why should LINQ be
different?

Because it would help a lot of people out, that's why. :) I was that
stubborn with our own API as well: why do they need sql examples, if
it's understandable by reading the specs/documentation? But keeping the
eyes closed for that doesn't make the problem go away, only the people
will go away and be dissapointed in the o/r mapping technology.

There's a lot of energy to be spend on converting people over to use
Linq instead of stored procedures or inline SQL strings. If it's very
hard to do the more complex SQL statements, it's going to be an uphill
battle.

Taking just a short time to understand the overall nature of LINQ
(rather than just focusing on LINQ to SQL) makes the behaviour of
Skip/ Take obvious and enriches the developer's understanding of the
bigger picture, so they can decide where LINQ is appropriate aside
from LINQ to SQL.

Ok, here's one lesson for you: although Linq is a general purpose
technique, it doesn't matter how it works for linq to objects etc., if
you are going to execute the linq query on a database, as the linq
query isn't the one that's being executed, the generated SQL query from
that linq query is. I.o.w.: it's not as simple as you make it out to
be: living on the level of Linq is nice and if you can afford to ignore
the rest, why bother, but for a lot of people the database they're
using in their app is a resource they can't affort to waste a lot of
time on: queries have to be fast, predictable and therefore scalable.

It's nice for you that you can determine what the SQL will be when you
are handed a random Linq query, but most of the C# developers won't be
able to. That's the main problem here, which is IMHO completely ignored
by MS.

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

Relevant Pages

  • Re: Are Linq-SQL methods commutative
    ... because to translate what is being done in LINQ to ... of the previous query. ... the intention of the linq query with SQL. ... get 2 different resultsets by specifying them in a different order, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Building expression trees to filter derived entity classes
    ... up the inheritance correctly in your LINQ to SQL mapping? ... you project the your LINQ to SQL entity objects onto some other POCO ... LINQ to SQL is still trying to process the entire query, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Are Linq-SQL methods commutative
    ... because to translate what is being done in LINQ to database ... you would actually perform a query on the result set of the previous ... SQL Server, then the ordering of the result set will be on that index, ... But I agree with you, in database land, it makes no sense, because you ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: LINQ to SQL vs. Stored Procedures (Performance)
    ... If it is an oft used LINQ ... query, you will find that SQL Server creates stats and the query will run ... Stored Procedure and a LINQ to SQL command, ...
    (microsoft.public.dotnet.framework)
  • Re: LINQ Where 1=0
    ... The LinqDataSource control stores values for all primary keys ... Before LINQ to SQL updates or deletes data, it checks the values in view ... If the underlying data source contains a timestamp field that is ...
    (microsoft.public.dotnet.framework.adonet)
Loading