Can linq2db create query that has ORDER BY by specific column but without including that column in SELECT?

Question

I am using linq2db as ORM for my project (ASP.NET Core 2.2) with Oracle database. I want to get list of products from database ordered by a specific column but this column doesn't need to be shown on the front-end client.

SQL query that I am looking for should be something like this:

SELECT Id, Name 
FROM Products
ORDER BY InternalOrder

So when I use linq2db in my C# code like this:

using (var db = new ProductsDao())
{
    return db.Products.Select(p => new Product
        {
             Id = p.Id,
             MarketName = p.MarketName
        })
        .OrderBy(p => p.InternalOrder)
        .ToArray();
}

then linq2db converts this to the following SQL query:

SELECT
    t1.Id,
    t1.Name
FROM
    Products t1
ORDER BY
    NULL

Notice the "ORDER BY NULL" which effectively won't do any ordering.

However if I add InternalOrder in the Select() like this:

using (var db = new ProductsDao())
{
    return db.Products.Select(p => new Product
        {
             Id = p.Id,
             MarketName = p.MarketName,
             InternalOrder = p.InternalOrder  // this is added
        })
        .OrderBy(p => p.InternalOrder)
        .ToArray();
}

then I get correct ORDER BY clause in the generated SQL query:

SELECT
    t1.Id,
    t1.Name,
    t1.InternalOrder
FROM
    Products t1
ORDER BY
    t1.InternalOrder

However, in this case I had to include InternalOrder column in the SELECT statement which is something I want to avoid because I don't need that field in the results.

Note, in the real application I might have very big tables and with multiple columns that I want to sort by but don't want to include in the result. Not getting values from database for these columns should save me some performance costs.

For code completeness here are my linq2db related classes that I used in the example above:

[Table(Name = "Products")]
public class Product
{
    [Column(Name = "Id")] 
    public decimal Id { get; set; }

    [Column(Name = "Name")] 
    public string Name { get; set; }

    [Column(Name = "InternalOrder", CanBeNull = false)] 
    public int InternalOrder { get; set; }
}       

public class LiveEventServiceDao : LinqToDB.Data.DataConnection
{
    public LiveEventServiceDao() : base("MyOracleDb")
    {
    } 
    public ITable<Product> Products => GetTable<Product>();
}

Is there a way to accomplish this with linq2db?

Answer 1

Try following :

return db.Products.Select(p => new {Order = p.InternalOrder, Prod = p})
         .OrderBy(p => p.Order)        
         .Select(p => new Product {
        {
             Id = p.Prod.Id,
             MarketName = p.Prod.MarketName
        })

        .ToArray();