Efcore: Question: Allowing RawSql added to a query

[generik0]

I have a tree graph in the relational DB. The query is quit sijmple:

```c#
actual.Elements = db.XmlElements.Include(x => x.Elements)
.Include(x => x.VemAttributes)
.Include(x => x.VemParameters)
.Where(x => x.ComponentId == id)

However, no matte what i do, i cannot order it . EFCore changes the above linq into 4 queries (see below). This is also fine.
But it is added an ID as the order by each time. I would like to have another order by ([FullName]). However. as EFCore changes the linq to sub queries i cannot order by.


Please tell me. Instead of the "[t].[FullName]" cani make EF core order with "[[FullName]" i.e. no "[t]", then it will be global for the query. 
Or is it possible to to add just raw sql to the "order by " part?


Full query:

```C#
FROM [VemXmlElements] AS [x]
WHERE [x].[ComponentId] = @__id_0
ORDER BY [x].[Id], [x].[FullName]',N'@__id_0 int',@__id_0=1015

exec sp_executesql N'SELECT [x.Elements].[Id], [x.Elements].[ComponentId], [x.Elements].[Critical], [x.Elements].[ElementId], [x.Elements].[FullName], [x.Elements].[IsDeleted], [x.Elements].[IsSelected], [x.Elements].[Name], [x.Elements].[XPath]
FROM [VemXmlElements] AS [x.Elements]
INNER JOIN (
    SELECT [x0].[Id], [x0].[FullName]
    FROM [VemXmlElements] AS [x0]
    WHERE [x0].[ComponentId] = @__id_0
) AS [t] ON [x.Elements].[ElementId] = [t].[Id]
ORDER BY [t].[Id], [t].[FullName]',N'@__id_0 int',@__id_0=1015

exec sp_executesql N'SELECT [x.VemAttributes].[Id], [x.VemAttributes].[ComponentId], [x.VemAttributes].[Critical], [x.VemAttributes].[ElementId], [x.VemAttributes].[FullName], [x.VemAttributes].[IsDeleted], [x.VemAttributes].[IsKey], [x.VemAttributes].[IsSelected], [x.VemAttributes].[Name], [x.VemAttributes].[Value], [x.VemAttributes].[VemXmlElementId], [x.VemAttributes].[XPath]
FROM [VemXmlAttributes] AS [x.VemAttributes]
INNER JOIN (
    SELECT [x1].[Id], [x1].[FullName]
    FROM [VemXmlElements] AS [x1]
    WHERE [x1].[ComponentId] = @__id_0
) AS [t0] ON [x.VemAttributes].[VemXmlElementId] = [t0].[Id]
ORDER BY [t0].[Id], [t0].[FullName]',N'@__id_0 int',@__id_0=1015


exec sp_executesql N'SELECT [x.VemParameters].[Id], [x.VemParameters].[ComponentId], [x.VemParameters].[Critical], [x.VemParameters].[ElementId], [x.VemParameters].[FullName], [x.VemParameters].[IsDeleted], [x.VemParameters].[IsKey], [x.VemParameters].[IsSelected], [x.VemParameters].[Max], [x.VemParameters].[Min], [x.VemParameters].[Name], [x.VemParameters].[Type], [x.VemParameters].[Value], [x.VemParameters].[VemXmlElementId], [x.VemParameters].[XPath]
FROM [VemXmlParameters] AS [x.VemParameters]
INNER JOIN (
    SELECT [x2].[Id], [x2].[FullName]
    FROM [VemXmlElements] AS [x2]
    WHERE [x2].[ComponentId] = @__id_0
) AS [t1] ON [x.VemParameters].[VemXmlElementId] = [t1].[Id]
ORDER BY [t1].[Id], [t1].[FullName]',N'@__id_0 int',@__id_0=1015

[Suchiman]

Are you using EF Core 3.0? As of 3.0, EF should only generate a single query for each linq query.

[generik0]

Hi @Suchiman, Very good question. Thx. Using 2.newest.

[ajcvickers]

@Suchiman Can you show the query here you have attempted to add your own ordering (using .OrderBy) and include the generated SQL so we can see where it's not working.

[generik0]

Hi @ajcvickers
The problem is that the
.OrderBy(x=>x.FullName)

ORDER BY [t1].[Id], [t1].[FullName]

I just want the order by to be global for the sub query also.

Ie eg. .OrderByRaw(“FullName”)

Resulting in:

ORDER BY [FullName]

[ajcvickers]

@generik0 Two suggestions:

• Try with 3.0 and post the 3.0 SQL. (We're unlikely to change anything here for the 2.x releases, and the SQL from 3.0 will likely be significantly different.)
• Explain _why_ you need ordering by _only_ the full name.

[generik0]

Hi @ajcvickers

Why

```c#
actual.Elements = db.XmlElements.Include(x => x.Elements)
.Include(x => x.VemAttributes)
.Include(x => x.VemParameters)
.Where(x => x.ComponentId == id)
.OrderBy(o=>o.FullName)

the db.XmlElements is a graph / tree hence the".Include(x => x.Elements)".
EF Core translates the include into an inner query.

```c#
exec sp_executesql N'SELECT [x.Elements].[Id], [x.Elements].[ComponentId], [x.Elements].[Critical], [x.Elements].[ElementId], [x.Elements].[FullName], [x.Elements].[IsDeleted], [x.Elements].[IsSelected], [x.Elements].[Name], [x.Elements].[XPath]
FROM [VemXmlElements] AS [x.Elements]
INNER JOIN (
    SELECT [x0].[Id], [x0].[FullName]
    FROM [VemXmlElements] AS [x0]
    WHERE [x0].[ComponentId] = @__id_0
) AS [t] ON [x.Elements].[ElementId] = [t].[Id]
ORDER BY [t].[Id], [t].[FullName]',N'@__id_0 int',@__id_0=1015

Cannot add ordering to includes

You cannot add ordering to inner queries. so "Include(x => x.Elements.OrderBy(o=>o.FullName)" throws exception:

System.InvalidOperationException: 'The Include property lambda expression 'x => {from VemXmlElement o in x.Elements orderby [o].FullName asc select [o]}' is invalid. The expression should represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, specify an explicitly typed lambda parameter of the target type, E.g. '(Derived d) => d.MyProperty'.

However:

c# exec sp_executesql N'SELECT [x.Elements].[Id], [x.Elements].[ComponentId], [x.Elements].[Critical], [x.Elements].[ElementId], [x.Elements].[FullName], [x.Elements].[IsDeleted], [x.Elements].[IsSelected], [x.Elements].[Name], [x.Elements].[XPath] FROM [VemXmlElements] AS [x.Elements] INNER JOIN ( SELECT [x0].[Id], [x0].[FullName] FROM [VemXmlElements] AS [x0] WHERE [x0].[ComponentId] = @__id_0 ) AS [t] ON [x.Elements].[ElementId] = [t].[Id] **ORDER BY [Id], FullName]'**,N'@__id_0 int',@__id_0=1015

Will order the inner query correctly also ;-)
But right now I have to reorder everything when transferring to the dto = slow.

3.0

We are at the end of a release cycle right now. I cannot do that. I expect i will have other mapping etc issues with 2.2 -> 3.0. but we will see.
I can try soon though. Are you saying that 3.0 does the graph query another way? Or there is a raw order by?

[Suchiman]

@generik0
re 3.0: yes, see Eager loading of related entities now happens in a single query and LINQ queries are no longer evaluated on the client

[generik0]

Hi @Suchiman
Thanks for your answer. Hopefully this wont mess up the graph/tree working. I will test in 2 weeks after reasle = done and holidays = done.
Thanks for you time :-)

[smitpatel]

I cannot reproduce this issue. Repro code used.
```C#
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace EFSampleApp
{
public class Program
{
public static void Main(string[] args)
{
using (var db = new MyContext())
{
// Recreate database
db.Database.EnsureDeleted();
db.Database.EnsureCreated();

            // Seed database
            db.Add(new VemXmlElement
            {
                FullName = "A",
                ComponentId = 1
            });

            db.SaveChanges();
        }

        using (var db = new MyContext())
        {
            // Run queries
            var id = 1;
            var query = db.XmlElements.Include(x => x.Elements)
                .Include(x => x.VemAttributes)
                .Include(x => x.VemParameters)
                .Where(x => x.ComponentId == id)
                .OrderBy(e => e.FullName)
                .ToList();
        }

        Console.WriteLine("Program finished.");
    }
}


public class MyContext : DbContext
{
    private static ILoggerFactory LoggerFactory => new LoggerFactory().AddConsole(LogLevel.Trace);

    // Declare DBSets
    public DbSet<VemXmlElement> XmlElements { get; set; }
    public DbSet<VemXmlAttribute> VemXmlAttributes { get; set; }
    public DbSet<VemXmlParameter> VemXmlParameters { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Select 1 provider
        optionsBuilder
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0")
            //.UseSqlite("filename=_modelApp.db")
            //.UseInMemoryDatabase(databaseName: "_modelApp")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(LoggerFactory);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure model
        modelBuilder.Entity<VemXmlElement>().HasMany(e => e.Elements).WithOne().HasForeignKey(e => e.ElementId).OnDelete(DeleteBehavior.ClientSetNull);
    }
}

public class VemXmlElement
{
    public int Id { get; set; }
    public int ComponentId { get; set; }
    public int? ElementId { get; set; }
    public bool Critical { get; set; }
    public bool IsDelected { get; set; }
    public bool IsSelected { get; set; }
    public string FullName { get; set; }
    public string Name { get; set; }
    public string XPath { get; set; }
    public List<VemXmlElement> Elements { get; set; }
    public List<VemXmlAttribute> VemAttributes { get; set; }
    public List<VemXmlParameter> VemParameters { get; set; }
}

public class VemXmlAttribute
{
    public int Id { get; set; }
    public string FullName { get; set; }
}

public class VemXmlParameter
{
    public int Id { get; set; }
    public string FullName { get; set; }
}

}

Generates 4 SQL as follows
```SQL
      SELECT [x].[Id], [x].[ComponentId], [x].[Critical], [x].[ElementId], [x].[FullName], [x].[IsDelected], [x].[IsSelected], [x].[Name], [x].[XPath]
      FROM [XmlElements] AS [x]
      WHERE [x].[ComponentId] = @__id_0
      ORDER BY [x].[FullName], [x].[Id]

      SELECT [x.Elements].[Id], [x.Elements].[ComponentId], [x.Elements].[Critical], [x.Elements].[ElementId], [x.Elements].[FullName], [x.Elements].[IsDelected], [x.Elements].[IsSelected], [x.Elements].[Name], [x.Elements].[XPath]
      FROM [XmlElements] AS [x.Elements]
      INNER JOIN (
          SELECT [x0].[Id], [x0].[FullName]
          FROM [XmlElements] AS [x0]
          WHERE [x0].[ComponentId] = @__id_0
      ) AS [t] ON [x.Elements].[ElementId] = [t].[Id]
      ORDER BY [t].[FullName], [t].[Id]

      SELECT [x.VemAttributes].[Id], [x.VemAttributes].[FullName], [x.VemAttributes].[VemXmlElementId]
      FROM [VemXmlAttributes] AS [x.VemAttributes]
      INNER JOIN (
          SELECT [x1].[Id], [x1].[FullName]
          FROM [XmlElements] AS [x1]
          WHERE [x1].[ComponentId] = @__id_0
      ) AS [t0] ON [x.VemAttributes].[VemXmlElementId] = [t0].[Id]
      ORDER BY [t0].[FullName], [t0].[Id]

      SELECT [x.VemAttributes].[Id], [x.VemAttributes].[FullName], [x.VemAttributes].[VemXmlElementId]
      FROM [VemXmlAttributes] AS [x.VemAttributes]
      INNER JOIN (
          SELECT [x1].[Id], [x1].[FullName]
          FROM [XmlElements] AS [x1]
          WHERE [x1].[ComponentId] = @__id_0
      ) AS [t0] ON [x.VemAttributes].[VemXmlElementId] = [t0].[Id]
      ORDER BY [t0].[FullName], [t0].[Id]

Used defined ordering is always put before ordering by Id.

[generik0]

Hi @smitpatel
Cool work mate! V2.x or v3.0?

I have to finish some work tomorrow, and have holidays with kids next week. I can provide example in iso week 43 ok?
Br Rik

[smitpatel]

Above is v2.2

SQL generated in 3.0

      SELECT [x].[Id], [x].[ComponentId], [x].[Critical], [x].[ElementId], [x].[FullName], [x].[IsDelected], [x].[IsSelected], [x].[Name], [x].[XPath], [x0].[Id], [x0].[ComponentId], [x0].[Critical], [x0].[ElementId], [x0].[FullName], [x0].[IsDelected], [x0].[IsSelected], [x0].[Name], [x0].[XPath], [v].[Id], [v].[FullName], [v].[VemXmlElementId], [v0].[Id], [v0].[FullName], [v0].[VemXmlElementId]
      FROM [XmlElements] AS [x]
      LEFT JOIN [XmlElements] AS [x0] ON [x].[Id] = [x0].[ElementId]
      LEFT JOIN [VemXmlAttributes] AS [v] ON [x].[Id] = [v].[VemXmlElementId]
      LEFT JOIN [VemXmlParameters] AS [v0] ON [x].[Id] = [v0].[VemXmlElementId]
      WHERE ([x].[ComponentId] = @__id_0) AND @__id_0 IS NOT NULL
      ORDER BY [x].[FullName], [x].[Id], [x0].[Id], [v].[Id], [v0].[Id]

[generik0]

Interesting. I am looking in the sql profiler to pull our the sql. Are you sure what you are seeing is actually being sent to the DB?

In retrospect, I might actually be using 2.1 (if it ain’t broke, don’t fix it). Will double check tomorrow l, and try an upgrade if not at 2.max.. If the ordering and everything else is working on 2.2 and I am on 2.1, then it is broke, and I will upgrade to fix it ;-)

[generik0]

Just double checked on azure devops. Running 2.1.4.
We have been so behind on features/features, I haven’t dared to update, in case some of the more trick orm queries broke.
I will check / update tomorrow. Tanks...

[smitpatel]

Generated SQL on 2.1 is same as 2.2.
Sql verified on command line logging and SQL profiler (profiler version sends sproc due to parameters.

[generik0]

@smitpatel Thanks!
Ig 2.1 = 2.2 = 3.0 in this query, then i have no idea why ef core is creating an inner slect query for me.
I am not really in control of that :-)
All i can do now, is provide a mini example for you guys, and whilst doing that, maybe I will find the cause.
It is my last day before the holidays, we are hardening while I am gone.. I need to ensure my is working today.

I still think that efcore should allow the "global" ordering / whereing on queries e.g:

SELECT * FROM [XmlElements] AS [x] LEFT JOIN [XmlElements] AS [x0] ON [x].[Id] = [x0].[ElementId] WHERE **[ComponentId]** = @__id ORDER BY **[FullName]**
it it could be an option for the linq:
c# OrderBy<TSource, TKey>(this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector, bool isGloabl = false)
And then EF does not include the table alias in the where/order.
But I Guess I made this issue as a question, not a request :-)

[generik0]

oh @smitpatel I just noticed something very interesting. what i need to get the ordering correct is:
`
SELECT [x].[Id], [x].[ComponentId], [x].[Critical], [x].[ElementId], [x].[FullName], [x].[IsDelected], [x].[IsSelected], [x].[Name], [x].[XPath], [x0].[Id], [x0].[ComponentId], [x0].[Critical], [x0].[ElementId], [x0].[FullName], [x0].[IsDelected], [x0].[IsSelected], [x0].[Name], [x0].[XPath], [v].[Id], [v].[FullName], [v].[VemXmlElementId], [v0].[Id], [v0].[FullName], [v0].[VemXmlElementId]
FROM [XmlElements] AS [x]
LEFT JOIN [XmlElements] AS [x0] ON [x].[Id] = [x0].[ElementId]
LEFT JOIN [VemXmlAttributes] AS [v] ON [x].[Id] = [v].[VemXmlElementId]
LEFT JOIN [VemXmlParameters] AS [v0] ON [x].[Id] = [v0].[VemXmlElementId]
WHERE ([x].[ComponentId] = @__id_0) AND @__id_0 IS NOT NULL
ORDER BY [x].[FullName], [x].[Id],

[x0].[FullName],

[x0].[Id], [v].[Id],

[v].[FullName], [v0].[Id],

[v0].[FullName]
`

The ef core 3 you generated only has "ORDER BY [x].[FullName]". But I guess you were showing the inner join rather than inner select query :thumbsup:

[ajcvickers]

Closing this as there doesn't seem to be any actionable outcome.

[generik0]

Hi @ajcvickers

The actionable outcome = 2 things :

1. i provide an example project after the holidays, with v3, that has the next issue with ordering the tree. (Or I find out it works in v3)

2. you guys consider if it should be possible to make orders and where’s without the alias / table / view name prefix to to the column. (For v3)

Can you guys think about point 2 for me, and get back to me?

[ajcvickers]

@generik0

1. If you create a repro then we will re-open and investigate
2. This is not something we're planning to implement.