sliced find in Linq

[Carl Daniel [VC++ MVP]]

...Even using this against a SQL2000 instance works
equally well.

Equally well? Unlikely since SQL 2000 doesn't support row_number/over. I'd
be curious to see what SQL it does generate against SQL2000 though.

-cd

 

[Andrus]

Quite possibly. I'm afraid I'm relatively inexperienced with WinForms

examples, particularly when they're retrieving data directly from
databases - I normally go through another layer (e.g. a web service).

Jon,

I noticed than in this and also in windowsforms.controls in other MS
windowsforms newsgroups and in codeProject windows forms newsgroup there are
very few good answers to windows forms issues.

Can anybody recommend good newsgroup which deals with winforms ?

Andrus.

 

[Jon Skeet [C# MVP]]

I noticed than in this and also in windowsforms.controls in other MS
windowsforms newsgroups and in codeProject windows forms newsgroup there are
very few good answers to windows forms issues.

Can anybody recommend good newsgroup which deals with winforms ?

I don't know of anywhere better than
microsoft.public.dotnet.framework.general.windowsforms I'm afraid.

 

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

I would be very surprized if this worked against SQL 2000. The
ROW_NUMBER ranking function wasn't introduced until SQL Server 2005, IIRC.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

"Jon Skeet [C# MVP]" <[email protected]> wrote in message

Which SQL Server version are you running against?

I suspect it's more likely to be a SQL Server change than a framework
change.

What makes you think the server version would make any difference?

Frans has mentioned before that LINQ to SQL will occasionally make
changes based on which type of database it's talking to (a *very*
limited portability layer!) and I thought that might be happening here.

But the client doesn't even know what DB he's talking against (well he
assumes it's SQL Server) even before he even sends the first T-SQ, granted
the SQL server may transform the T-SQL statement internally, but what I'm
after is the SQL produced by the provider.
Following is a complete sample (using the ADVWorks sample DB), that
illustrates my point. Here I'm displaying the T-SQL to the console, but I
don't even attach to a DB (sure I'm authenticating against the server
instance).

using System;
using System.Data.Linq;
using System.Linq;
using System.Data.Linq.Mapping;

[Table(Name="Sales.Customer")]
class Customer
{
[Column(IsPrimaryKey=true)]
public int CustomerID;
[Column]
public int TerritoryID;
}
class NWCtxt : DataContext
{
public NWCtxt (string conStr) : base(conStr)
{}
public Table<Customer> Customers;
}

class Program
{
static void Main()
{
NWCtxt ctx = new NWCtxt
("server=.\\SQLEXPRESS;database=AdventureWorks;integrated security=sspi");
var query = ctx.Customers.Skip(3).Take(5);
Console.WriteLine(ctx.GetCommand(query).CommandText);
}
}

That's good to know.

Well, actually SQL2000 isn't officiallly supoorted (which means not
tested), but I would be surprised if there were more issues with Linq-SQL
than when compared to SQL2005.

Willy.

 

[Frans Bouma [C# MVP]]

You are of course, right. I was comparing to the DIY approach, which
up until now has often served me well, to give maximum flexibilty (for
optimisation etc). But the downside is that you have more plumbing to
maintain.
I have looked at various ORM tools in the past (NHibernate, etc), and
LINQ is the first one I have seen that truly does what I want (i.e.
pretty much what I would have coded in SQL, with highly specific and
efficient queries), without me having to fight the ORM tool. I'm a
convert, and I suspect a great many other people will be too...


But that is the great thing about extension methods and the composable
nature of LINQ expressions... roll your own ;-p

public static IQueryable<T> TakePage<T>(this IQueryable<T>
query,
int pageIndex, int pageSize) {
return query.Skip(pageSize * pageIndex).Take(pageSize);
}

Then you can just combine as you like:
var query = (from cust in db.Customers
select cust).TakePage(3, 10);

That's what we're doing indeed

(although not by re-using skip/take )

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

 

[Frans Bouma [C# MVP]]

>Willy


But the client doesn't even know what DB he's talking against (well
he assumes it's SQL Server) even before he even sends the first T-SQ,
granted the SQL server may transform the T-SQL statement internally,
but what I'm after is the SQL produced by the provider.

It does know the sqlserver version, it's inside the datacontext, as
the datacontext holds a reference to the sqlprovider instance to use,
be it sqlserver 2000's or sqlserver 2005's.

Well, actually SQL2000 isn't officiallly supoorted (which means not
tested), but I would be surprised if there were more issues with
Linq-SQL than when compared to SQL2005.

Not supported officially? I think it is officially supported, as they
have a full sqlserver 2000 provider in the framework

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

 

[Frans Bouma [C# MVP]]

Carl Daniel [VC++ MVP]

...but be aware that in all likelihood, this will return the
entire query result into memory, then discard the first m rows,
and finally retun the next n rows. The select ... offset...
limit is Oracle-specific SQL syntax, so I wouldn't assume that
Linq to SQL knows about it.

Fortunately it's easy to check this, and in LINQ to SQL against SQL
Server 2005 at least, it works well. I tried this:

var q = db.Customers.Skip(3).Take(5);

foreach (Customer cust in q)
{
Console.WriteLine (cust.CompanyName);
}

and the SQL executed was:

SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName],
[t1]. [ContactTitle], [t1].[Address], [t1].[City], [t1].[Region],
[t1]. [PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CustomerID], [t0].
[CompanyName], [t0].[ContactName], [t0].[ContactTitle],
[t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],
[t0].[Country], [t0]. [Phone], [t0].[Fax]) AS [ROW_NUMBER],
[t0].[CustomerID], [t0]. [CompanyName], [t0].[ContactName],
[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region],
[t0].[PostalCode], [t0]. [Country],[t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]


It's not exactly neat, but the query plan looks reasonable, and it
certainly avoids returning everything to the client.

Cool - I'm impressed. Unfortunately I didn't have an Orcas
installation to test it on, so I'm glad to see that I was wrong about
that.

The only undesirable thiing I see in the SQL above is that order by
over all of the returned columns. If Customers is a large table,
this will result in SQL server doing the sort in tempdb. Since the
original query was unordered, I'd like the above SQL to simply order
by the primary key, since that alone must define a unique ordering,
which is all that's required for this to work. (Perhaps your table
doesn't have a primary key though?)

The ROWNUMBER OVER statement requires an ordering, so normally you'd
place either the ordering of the inner sql there or a new one (the PK
field(s)).

However, it also works if you add a timestamp notification there, so
you don't have to manipulate the ordering of the inner query.

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

 

[Willy Denoyette [MVP]]

Very true, my bad, I actually didn't run the query against SQL2000 (see the
sample [1]). My point is that the exact same T-SQL statement is constructed
whatever SQL version you are connecting to, the query provider assumes at
least SQL2005.


using System;
using System.Data.Linq;
using System.Linq;
using System.Data.Linq.Mapping;

[Table(Name="Sales.Customer")]
class Customer
{
[Column(IsPrimaryKey=true)]
public int CustomerID;
[Column]
public int TerritoryID;
}
class NWCtxt : DataContext
{
public NWCtxt (string conStr) : base(conStr)
{}
public Table<Customer> Customers;
}

class Program
{
static void Main()
{
NWCtxt ctx = new NWCtxt
("server=.\\SQLEXPRESS;database=AdventureWorks;integrated security=sspi");
var query = ctx.Customers.Skip(3).Take(5);
Console.WriteLine(ctx.GetCommand(query).CommandText);
}
}

Willy.

I would be very surprized if this worked against SQL 2000. The
ROW_NUMBER ranking function wasn't introduced until SQL Server 2005, IIRC.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

<snip>

Which SQL Server version are you running against?

I suspect it's more likely to be a SQL Server change than a framework
change.

What makes you think the server version would make any difference?

Frans has mentioned before that LINQ to SQL will occasionally make
changes based on which type of database it's talking to (a *very*
limited portability layer!) and I thought that might be happening here.

But the client doesn't even know what DB he's talking against (well he
assumes it's SQL Server) even before he even sends the first T-SQ,
granted the SQL server may transform the T-SQL statement internally, but
what I'm after is the SQL produced by the provider.
Following is a complete sample (using the ADVWorks sample DB), that
illustrates my point. Here I'm displaying the T-SQL to the console, but I
don't even attach to a DB (sure I'm authenticating against the server
instance).

using System;
using System.Data.Linq;
using System.Linq;
using System.Data.Linq.Mapping;

[Table(Name="Sales.Customer")]
class Customer
{
[Column(IsPrimaryKey=true)]
public int CustomerID;
[Column]
public int TerritoryID;
}
class NWCtxt : DataContext
{
public NWCtxt (string conStr) : base(conStr)
{}
public Table<Customer> Customers;
}

class Program
{
static void Main()
{
NWCtxt ctx = new NWCtxt
("server=.\\SQLEXPRESS;database=AdventureWorks;integrated
security=sspi");
var query = ctx.Customers.Skip(3).Take(5);
Console.WriteLine(ctx.GetCommand(query).CommandText);
}
}

The T-SQL statement is produced at the client (by the Linq To SQL query
provider) and sent to the server. I ran this against SQL2005 Express
and
SQL2005 Standard edition, both show the (obviously) same T-SQL
statement.

Ah.

Even using this against a SQL2000 instance works equally well.

That's good to know.

Well, actually SQL2000 isn't officiallly supoorted (which means not
tested), but I would be surprised if there were more issues with Linq-SQL
than when compared to SQL2005.

Willy.

 

[Willy Denoyette [MVP]]

It does know the sqlserver version, it's inside the datacontext, as
the datacontext holds a reference to the sqlprovider instance to use,
be it sqlserver 2000's or sqlserver 2005's.

Ok I see, I knew I must be missing something, the context is built when the
client initially connects with the SQL Server instance, and this handshake
returns the SQL Version number.
This is part of a network trace frame, it's part of the initial conncet
request to the SQL server Browser (SQL2005)(
......No;Version;9.00.3042.00;tcp;1433;

Anyway, this the T-SQL statement produced:

SELECT TOP 5 [t1].[CustomerID], [t1].[TerritoryID]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CustomerID],
[t0].[TerritoryID]) AS [ROW_NUMBER], [
t0].[CustomerID], [t0].[TerritoryID]
FROM [Sales].[Customer] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] > @p0

, by running :
var query = ctx.Customers.Skip(3).Take(5);
Console.WriteLine(ctx.GetCommand(query).CommandText);
irrespective the version of SQL2005 (Express and Standard, all SP2 ).

Not supported officially? I think it is officially supported, as they
have a full sqlserver 2000 provider in the framework

Oh great, that means the clients binds the provider based on the Version
info returned when connecting to the SQL server instance.
Well, I should have to check this against SQL2000 running remotely, that way
I can use a network monitor to inspect the TCP and TDS streams in order to
verify whether the SQL sent to the server is the same as the one produced by
:
"ctx.GetCommand(query).CommandText".

Willy.

 

[Marc Gravell]

My point is that the exact same T-SQL statement is constructed

whatever SQL version you are connecting to, the query provider assumes at
least SQL2005.

No - surely the same /ESQL/ query is generated, but the /T-SQL/ varies
radically, and does seem to support 2000 for common operations. I just
ran a quick paging test on SQL2000 (sorry, no Northwind or Pubs on my
2000 boxes ;-p):

var query = ctx.ORDER_HEADERs.Skip(10).Take(20);
txt2 = ctx.GetCommand(query).CommandText;
List<ORDER_HEADER> orders = new
List<ORDER_HEADER>(query);

and the T-SQL generated was:

SELECT TOP 20 [t0].[Order_Header_ID] -- other columns snipped for
clarity
FROM [dbo].[ORDER_HEADER] AS [t0]
WHERE NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM (
SELECT TOP 10 [t1].[Order_Header_ID]
FROM [dbo].[ORDER_HEADER] AS [t1]
) AS [t2]
WHERE [t0].[Order_Header_ID] = [t2].[Order_Header_ID]
))

(similar queries for 2005 already discussed)

So the T-SQL definitely is very different, which it must be without
things like ROW_NUMBER()

Marc

 

[Willy Denoyette [MVP]]

My point is that the exact same T-SQL statement is constructed
whatever SQL version you are connecting to, the query provider assumes at
least SQL2005.

No - surely the same /ESQL/ query is generated, but the /T-SQL/ varies
radically, and does seem to support 2000 for common operations. I just
ran a quick paging test on SQL2000 (sorry, no Northwind or Pubs on my
2000 boxes ;-p):

var query = ctx.ORDER_HEADERs.Skip(10).Take(20);
txt2 = ctx.GetCommand(query).CommandText;
List<ORDER_HEADER> orders = new
List<ORDER_HEADER>(query);

and the T-SQL generated was:

SELECT TOP 20 [t0].[Order_Header_ID] -- other columns snipped for
clarity
FROM [dbo].[ORDER_HEADER] AS [t0]
WHERE NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM (
SELECT TOP 10 [t1].[Order_Header_ID]
FROM [dbo].[ORDER_HEADER] AS [t1]
) AS [t2]
WHERE [t0].[Order_Header_ID] = [t2].[Order_Header_ID]
))

(similar queries for 2005 already discussed)

So the T-SQL definitely is very different, which it must be without
things like ROW_NUMBER()

Marc

Well, it turns out that my previous test against SQL200 were run against an
SQL2005 instance, I don't dispose of a local SQL2000 instance, so I had to
connect to a remote instance which was (wrongly) described as SQL2000 in the
AD, I should have been more careful (and never do this on a Sunday), sorry
about the confusion.
Now, I ran [ctx.Customers.Skip(3).Take(5);] against a SQL2000 SP4 server and
the T-SQL looks like:

Output of: [ctx.GetCommand(query).CommandText ] is

SELECT TOP 5 [t0].[CustomerID], [t0].[TerritoryID]
FROM [Sales].[Customer] AS [t0]
WHERE NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM (
SELECT TOP 3 [t1].[CustomerID]
FROM [Sales].[Customer] AS [t1]
) AS [t2]
WHERE [t0].[CustomerID] = [t2].[CustomerID]
))

The TDS stream (network trace) contains the exact same contents as above.
The network trace however, doesn't hold any version info returned by
SQL2000, that means that the client assumes the server to be a down-level
SQL version and as such selects the appropriate (SQL2000?) provider.

Note that you don't need the ADVWorks database to be present to see the
T-SQL generated, you can specify the master DB for instance, all that
matters is the context, sure you can't actually run the query.

Willy.

 

[Frans Bouma [C# MVP]]

My point is that the exact same T-SQL statement is constructed
whatever SQL version you are connecting to, the query provider
assumes at least SQL2005.

No - surely the same ESQL query is generated, but the /T-SQL/ varies
radically, and does seem to support 2000 for common operations. I just
ran a quick paging test on SQL2000 (sorry, no Northwind or Pubs on my
2000 boxes ;-p):

var query = ctx.ORDER_HEADERs.Skip(10).Take(20);
txt2 = ctx.GetCommand(query).CommandText;
List<ORDER_HEADER> orders = new
List<ORDER_HEADER>(query);

and the T-SQL generated was:

SELECT TOP 20 [t0].[Order_Header_ID] -- other columns snipped for
clarity
FROM [dbo].[ORDER_HEADER] AS [t0]
WHERE NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM (
SELECT TOP 10 [t1].[Order_Header_ID]
FROM [dbo].[ORDER_HEADER] AS [t1]
) AS [t2]
WHERE [t0].[Order_Header_ID] = [t2].[Order_Header_ID]
))

(similar queries for 2005 already discussed)

So the T-SQL definitely is very different, which it must be without
things like ROW_NUMBER()

Hmm, I hope they also are capable of doing paging with more advanced
queries as this type of 'paging' isn't going to work with e.g. joins.
You then need a temptable on sqlserver 2000 to be sure for 100% you'll
be able to page over every possible query's resultset.

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

 

[Marc Gravell]

Easy enough to investigate if you have some scenarios in mind?

Marc

 

[Frans Bouma [C# MVP]]

Easy enough to investigate if you have some scenarios in mind?

take the 2nd page in a query on northwind employee filtered on order
(1:n relation)

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

 

[Marc Gravell]

take the 2nd page in a query on northwind employee filtered on order
I went for customers, but same horse...
The query below is deliberately not particularly well-done, so no
jubes please!
So basically - /some/ things will work on SQL Server 2000, but you may
wish to limit yourself to simple paged operations. I didn't want to
start getting into workarounds for this *specific* query (perhaps a
distinct field or view etc) - the more important point is "tread
carefully" - or upgrade to SQL Server 2005 if you want to do anything
non-trivial with LINQ.

C#/LINQ:
string country = "France";
var query = from c in ctx.Customers
join o in ctx.Orders on
c.CustomerID equals o.CustomerID
where o.ShipCountry == country
orderby c.CompanyName
select c;
query = query.Skip(10).Take(10);

Using SQL 2000:
[Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Standard Edition on Windows NT 5.0 (Build 2195; Service Pack 4)]

Throws NotSupportedException:
<q>This provider supports Skip() only over ordered queries returning
entities or projections that contain all identity columns, where the
query is a single-table (non-join) query, or is a Distinct, Except,
Intersect, or Union (not Concat) operation.</q>

Using SQL Express 2005:
[Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)]

SELECT TOP 10 [t2].[CustomerID], [t2].[CompanyName],
[t2].[ContactName], [t2].[ContactTitle], [t2].[Address], [t2].[City],
[t2].[Region], [t2].[PostalCode], [t2].[Country], [t2].[Phone],
[t2].[Fax]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CompanyName]) AS
[ROW_NUMBER], [t0].[CustomerID], [t0].[CompanyName],
[t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City],
[t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone],
[t0].[Fax]
FROM [dbo].[Customers] AS [t0]
INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] =
[t1].[CustomerID]
WHERE [t1].[ShipCountry] = @p0
) AS [t2]
WHERE [t2].[ROW_NUMBER] > @p1
ORDER BY [t2].[CompanyName]
-------------------------------
@p0 [String]: 'France'
@p1 [Int32]: 10

Interesting.... very interesting...

Marc

 

[Marc Gravell]

For anybody interested, I've forwarded this to the LINQ forum

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2347563&SiteID=1

Marc

 

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

I've been thinking about this a lot, and I have to say, I really dislike
the implementation of skip and take on the T-SQL side.

The problem I have is that the T-SQL side, the ROW_NUMBER ranking
function is being used to produce an ordered result, and that order is used
to determine what is taken, and what is skipped.

The reason I have a problem with this is that the ROW_NUMBER function
requires an order, an order that is not expressed on the .NET side of
things. It just orders by the fields as they occur in the database. While
it's great that they are going to do this consistently, it's something that
just shouldn't be allowed when there is not an order operation on the query
itself. Without an order specified, in SQL (as it should be in LINQ, I
believe) the result should be indeterminate, which would make Take and Skip
worthless.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Carl Daniel [VC++ MVP]

...but be aware that in all likelihood, this will return the entire query
result into memory, then discard the first m rows, and finally retun the
next n rows. The select ... offset... limit is Oracle-specific SQL
syntax,
so I wouldn't assume that Linq to SQL knows about it.

Fortunately it's easy to check this, and in LINQ to SQL against SQL
Server 2005 at least, it works well. I tried this:

var q = db.Customers.Skip(3).Take(5);

foreach (Customer cust in q)
{
Console.WriteLine (cust.CompanyName);
}

and the SQL executed was:

SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].
[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].
[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CustomerID], [t0].
[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address],
[t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].
[Phone], [t0].[Fax]) AS [ROW_NUMBER], [t0].[CustomerID], [t0].
[CompanyName], [t0].[ContactName], [t0].[ContactTitle],
[t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].
[Country],[t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]


It's not exactly neat, but the query plan looks reasonable, and it
certainly avoids returning everything to the client.

(Note that even if it *did* do everything in the client, it wouldn't
have to load all the results into memory before starting to skip
things.)

 

[Carl Daniel [VC++ MVP]]

The ROWNUMBER OVER statement requires an ordering, so normally you'd
place either the ordering of the inner sql there or a new one (the PK
field(s)).

Yes, of course it does. But in this case, the query as stated in C# didn't
specify the ordering, so any ordering could have been used. It appears that
LINQ chose to use the most expensive ordering possible, when it should use
the least expensive when no ordering was specified in the original
expression. Hence my question to Jon - did this table have a primary key?
If it didn't (and no unique keys either), then this is the best LINQ could
do, but if any unique keys exist, LINQ should use one of those - perhaps it
does, but I can't tell from this example.

-cd

 

[Jon Skeet [C# MVP]]

On Oct 31, 3:31 pm, "Carl Daniel [VC++ MVP]"

Yes, of course it does. But in this case, the query as stated in C# didn't
specify the ordering, so any ordering could have been used. It appears that
LINQ chose to use the most expensive ordering possible, when it should use
the least expensive when no ordering was specified in the original
expression. Hence my question to Jon - did this table have a primary key?

Sorry for not answering it before - I don't have the database in front
of me, but it was just Northwind, so I assume it has an appropriate
primary key. And yes, it would be nice if it had ordered the results
on that for speed...

Jon

 

[Marc Gravell]

A valid perspective... and as a developer I would be perfectly happy
if it threw an exception if I tried paging without defining an
explicit order. Pleased, even - it could avoid hard to track bugs.

As a caveat, not all back-ends are the same; some sources (xml nodes,
for example) do have implicit ordering - so this would only make sense
at the provider level.

It may be academic though (as in: late for changes) - but perhaps
worth further discussion on the LINQ forum.

Marc