Re: Are Linq-SQL methods commutative
- From: "Nicholas Paldino [.NET/C# MVP]" <mvp@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 26 Nov 2007 11:56:14 -0500
Frans,
See inline:
Andrus,
Absolutely different results.
Now that's unexpected. In database land, you can't get 2 different
results.
Not really, because to translate what is being done in LINQ to database
land, you would actually perform a query on the result set of the previous
query. The operations are not performed at the same time.
Assuming the ordering is the same on each of them (because Skip and
Take make no sense without ordering, LINQ to SQL will create an order
for you, which irritates me to no end, but that's a separate thread),
Why? SELECT * FROM Table without ordering has no defined ordering, so
using a limit + skip operator on that set results in a set of undefined
rows, you'll never know what rows will be returned.
Well, that's not completely true. If you are using a clustered index in
SQL Server, then the ordering of the result set will be on that index,
however, without that index, the order is undefined (basically, however it
is stored in the file).
But I agree with you, in database land, it makes no sense, because you
have to define an ordering. LINQ to SQL gets around this by ordering by all
the columns in the table if another order is not found (I believe, I have to
test that part, but without a specific ordering, it orders on every column
in the table).
This is why I said what I said in parenthesis. I hate the fact that
Take and Skip are exposed, and assume some kind of order for you when it
doesn't make sense to not have ordering. It should be mandatory to provide
an order when using any of these extensions. It's also why I said "assuming
the ordering is the same on each of them", because LINQ to SQL will always
produce a default ordering.
they will produce different results.
Say your query will produce the ordered set {1, 2, 3}. Let n = 1,
m = 2.
The first query:
var query = query.Skip(n).Take(m);
Will return the ordered set {2, 3}, while the second query:
var query = query.Take(m).Skip(n);
Will return the ordered set {2}.
The reason for this is that in the first query, the Skip method
skips one element, then takes the remaining two, while in the second
query, the first two elements are taken, and then the first one is
skipped.
I tried it out to be sure, and indeed Linq to Sql generates two
different queries (which really hurt my eyes but that's another story).
The thing is though that on databases, people will use the take/skip
combination to page through a bigger resultset. However consulting the
manual for both shows no word whatsoever about the order in which these
two statements have to be specified.
Well, there is no order that they HAVE to be specified in. Yes, they
are usually used in conjunction, but it is not a requirement that they be
used in conjunction. They can be specified in any order, which is what the
OP's question was arising from. He wanted to know if the order in which the
operations were applied would affect the outcome, which it definitely will.
I do understand the order, but it's a bit strange as well. For
example, Linq apparently has no problem with an Order by placed in
front of a where, however there IS an order in take/skip which are used
combined as a paging mechanism. Of course, this follows from the specs
of both, but semantically, the intention of what people want to do,
e.g. to page, shouldn't require an order in the statements for paging,
if other elements in the query also don't really require an order (they
do, but that's whiped under the rug)
Well, it depends here on what operations we are talking about. You are
making the error of assuming that Take and Skip are both sub parts of a
larger page operation, and assuming that because other elements of the
operation that can be applied (i.e. where) don't require order, these don't.
But that's wrong. Like you said, in db-land, not applying an order
results in an undefined order in the result set. If you are going to page
off that, then the DB is completely within its rights to return a
differently ordered result set between subsequent queries, and your paging
results will simply be wrong. You would be depending on an implementation
detail that the DB would consistently return the undefined order to you.
The reason Take and Skip (should) require an order is because of this
fact. Think of it. The definition of Take is "take the FIRST n elements".
Skip is defined as "skip the NEXT n elements". Because of FIRST and NEXT,
you require order. Without knowing the order, the concepts of FIRST and
NEXT do not exist.
--
- Nicholas Paldino [.NET/C# MVP]
- mvp@xxxxxxxxxxxxxxxxxxxxxxxxxxx
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#)
------------------------------------------------------------------------
.
- Follow-Ups:
- Re: Are Linq-SQL methods commutative
- From: Frans Bouma [C# MVP]
- Re: Are Linq-SQL methods commutative
- References:
- Are Linq-SQL methods commutative
- From: Andrus
- Re: Are Linq-SQL methods commutative
- From: Nicholas Paldino [.NET/C# MVP]
- Re: Are Linq-SQL methods commutative
- From: Frans Bouma [C# MVP]
- Are Linq-SQL methods commutative
- Prev by Date: Re: Assigning a reference to a variable
- Next by Date: Re: How to, not display the tab in a TabControl
- Previous by thread: Re: Are Linq-SQL methods commutative
- Next by thread: Re: Are Linq-SQL methods commutative
- Index(es):
Relevant Pages
|
