Question: why to order by a field, it needs to be in the select?

[marciogoularte]

Hi,

I'm using SQL Server and I have queries that make certain fields select and order by another

exemple:
base.Query<TEntity>(TableName(), fields: Field.From("Id", "Name"), orderBy: new List<OrderField>() { new OrderField("Created", Order.Descending) });

Throw exception here:
https://github.com/mikependon/RepoDB/blob/master/RepoDb.Core/RepoDb/StatementBuilders/BaseStatementBuilder.cs#L727

because without that it works too. What is the reason for this?

[mikependon]

We purposely have added this validation when composing a SQL statement.

Have you tried including the Created column from the list of fields you are querying? Technically, SQL Server could not order your result without it being listed as part of the selected columns.

[mikependon]

In short, the query below is not feasible.

SELECT Id, Name FROM TableName ORDER BY Created ASC;

But the query below would work.

SELECT Id, Name, Created FROM TableName ORDER BY Created ASC;

EDITED: Or, maybe, I am combining the sub query selections and other providers 😄

[marciogoularte]

In my test I don't need Created (datetime). But I need to get Id and Name of the last or first users sorted by Created (datetime)

ignore the order of IDs because the created of id 3 was changed to old date

SQL Server sorts even if the column is not part of the select

with Created

but I don't need this column. I only need the Id and Name, but the order has to be the newest or the oldest

[mikependon]

Right, this is really a bug on the code. We will issue a fix for you, but we are glad if you can PR this simple fix.

It sounds like we need to remove the line 726-740, and also, the one in the QueryAll operation at line 792-806. It might affect some Unit Test, but the one we can always adjust to guard this logic.

Once fixed (either via PR or us), we will issue the release for you immediately.

Looking forward if you can PR this one mate.

[mikependon]

@SergerGood made the PR for this. The fix to this will be available the next releases > RepoDb v1.12.10-beta1.

[mikependon]

The fix for this is at PR #985