Add FromSQL support for column mappings

[peter-dolkens]

Currently, I'm trying to implement the following SQL using EF, and struggling to find a way short of composing the entire SQL myself.

SELECT	[Label],
		COUNT(DISTINCT CONCAT([Column1], [Column2]) [Count]
FROM [Table]
WHERE /* Dynamic Filter */
GROUP BY [Label]

My problem is, I have no way to write that bespoke COUNT in EF Core, and there are many other advanced column mappings which would benefit from having a special type that we could select from.

I propose something similar to

context.Table.Where(q => /* Dynamic Filter */)
    .GroupBy(q => q.Label)
    .Select(q => new {
        Label = q.Label,
        Count = MappedColumn.FromSql("COUNT(DISTINCT CONCAT([Column1], [Column2]))")
    });

The MappedColumn.FromSql constructor would return a special MappedColumn type, which could then be detected, and translated by the generic SQL translator.

[peter-dolkens]

This approach was caused by current issues with the GroupBy method, especially around nesting them and/or using Count with them resulting in client-side execution

[smitpatel]

                var query = db.Blogs.Select(b => new
                {
                    b.Label,
                    Property = b.Column1 + b.Column2
                })
                .Distinct()
                .GroupBy(e => e.Label)
                .Select(g => new
                {
                    g.Key,
                    Count = g.Count()
                })
                .ToList();

      SELECT [t].[Label] AS [Key], COUNT(*) AS [Count]
      FROM (
          SELECT DISTINCT [b].[Label], [b].[Column1] + [b].[Column2] AS [Property]
          FROM [Blogs] AS [b]
      ) AS [t]
      GROUP BY [t].[Label]

[peter-dolkens]

Thanks @smitpatel I'll look into that format for the query. I'd tried to simplify my example for this post, so it's possible something else was tripping it up.

Regardless, I think FromSQL for explicit column mappings could still be of great value, especially when it comes to the numerous functions that aren't natively mapped by the EF providers.

[sandersaares]

I share an appreciation for this request in principle.

I also ran into the limitations of GroupBy translation and ended up just defining views for my queries to get them to work properly. This style of column-scoped SQL mapping might be a benefitial middle ground to help avoid going all the way to a view when I want something that EF translation cannot provide (or even those that I cannot figure out the right EF query syntax for).

[ajcvickers]

Notes from triage: It's quite problematic to support this kind of in-line SQL because it must interact correctly with the SQL generated by EF, which includes knowing what column aliases to use, etc. Also, if we then change the SQL generated by EF, it potentially break the in-line SQL that was working before. That being said, #10768 is a more constrained version of this that could work for by having the mapping in the model and associated with a given entity type property.