Query: Simplify aliases for complex projection if being used in order by clause in new pipeline

[maumar]

query:

ss.Set<Customer>().Select(c => new DTO<string> { Property = c.CustomerID + c.City }).OrderBy(n => n.Property)

translation:

SELECT [c].[CustomerID] + [c].[City] AS [Property]
FROM [Customers] AS [c]
ORDER BY [c].[CustomerID] + [c].[City]

expected translation:

SELECT [c].[CustomerID] + [c].[City] AS [Property]
FROM [Customers] AS [c]
ORDER BY [Property]

This used to work in the old pipeline - see #6703

[smitpatel]

It used work in old pipeline with a lot of bugs

Should be easy step in posttranslation.

[lixaotec]

Any workaround?

I´m struggling to get containstable rank in an fromsqlinterpolated, wish to at last orderby Rank, as 'Rank' is not a property of my entity and is generated

Appreciate your help.

[maumar]

@lixaotec the queries should still work, just the sql we generate is not optimal. Can you provide more info on what exactly you are trying to do and how are you trying to do this? - full code listing would be the best.

[lixaotec]

I wish to project afromsqlinterpolated directly onto a DTO, however its seems not possible as I need to indicate the entity in Db.Set() ...
Perhaps this breaks the pipeline concept of EFCore, but some queries are too complex and don´t need to live within the entities context.

In short, retrieving Rank information from a fromsqlinterpolate with FullTextSearch containstable is not possible if Rank isn´t present as a column in the entity.

I found out that EFCore supports Keyless entity, a thing that seem to allow such thing. as I define an entity that has a Rank field, not necessarily an entity. But the strange thing is that I still have to register it in modelbuilder.

Keyless seem to be the closer concept near to making EFCore know a DTO class.
Wish to hear if is that a good practice.

Thanks

[ajcvickers]

@lixaotec See #10753

[rofenix2]

Any news on this?.

[maumar]

@cdrfenix this issue is assigned to the backlog milestone, meaning that unfortunately we don't plan to implement it in the upcoming release of EF Core.

[Suchiman]

I've started prototyping around with this, do i need to create a new expression type to reference the Alias introduced by the ProjectionExpression?
There's (Concrete)ColumnExpression but that requires a table which i don't have, for the PoC i've abused SqlFragmentExpression

[roji]

@maumar is this a dup of #16038?