How to configure an entity property's generated sql to be a subquery or call to UDF?

[rachael-ross]

Given the following entity model, is there a way I could instruct/configure the EntityQueryProvider to generate a parameterized subquery or call to a UDF in place of a column name?

public class Plan
    {       
        public string Id { get; set; }
       
        public string Name { get; set; }
      
        public string DisplayName { get; set; }  // want display name to be a subquery
     
        public string RestrictedName { get; set; }
}

By default, the generated SQL would look something like this:

Select Id, Name, DisplayName, RestrictedName From Plans 

But I'd like to have alternate SQL generated for the DisplayName: (where "211" is dynamically passed in from calling code)

Select Id, Name,
    CASE
	WHEN [dbo].getPlanAccess(Id,211) = 0 THEN [Name] ELSE RestrictedName
    END as [DisplayName],
RestrictedName
 From Plans 

I've successfully tried using FromSqlRaw(), but not sure how to do something like this for when Plans are "Included" in another associated DBSet, such as:

DbContext.Users.Include(nameof(Users.Plans))

Is there was a way to do this at field level configuration, rather than the entire entity level? Or at a global level once that applies to all calls to Plans?

protected override IQueryable<Plan> GetAll()
        {
            return DbContext.Set<Plan>().FromSqlRaw(
                @$"SELECT [Id]
                  ,[Name]
                  , CASE
                      WHEN [dbo].getPlanAccess([dbo].[Plans].Id,{AuthService.User.UserProfileId()}) = 0 THEN [dbo].[Plans].[Name] ELSE [dbo].[Plans].RestrictedName
                    END as [DisplayName]
                  ,[RestrictedName]                 
            FROM [dbo].[Plans]");
        }

NOTE: The particulars of the requirements, constraints, in addition to our use of a framework that builds dynamic queries onto the default Set at runtime that would no longer be useful with any other approach. Additionally, the results requires a sort by DisplayName and will be delivered in pages of results - not the entire result set.

EF Core version: 3.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 3.1
Operating system:
IDE: Visual Studio 2019 16.3

[ajcvickers]

Note for triage: see #10768.

[rachael-ross]

Why was this closed? The referenced issue doesn't solve this.

[AndriySvyryd]

The translation requirement in #10768 is similar to what you are looking for. Why do you say it wouldn't apply for your scenario?

[rachael-ross]

@AndriySvyryd Thanks for your response.... I'll try to explain further what I'm running up against.

I'm using an open source framework that internally calls the DbContext.Set<TEntity>() and then further builds out the query with sorting, filtering, etc.

Is there a way to define the sample I gave above:

• At a global level so that regardless of where Set() gets called, the DisplayName in the SELECT portion of the generated SQL is replaced with some alternate SQL
• And, the alternate SQL is defined in a way that the current user Id can be injected
• And, the solution works if Plans are queried as part of an "Include", like DbContext.Users.Include(nameof(Users.Plans))

I've looked at overriding the Set method in the DbContext, but FromRawSql() returns an IQueryable rather than DbSet

[AndriySvyryd]

There's currently no way of doing something like that, but #10768 should make it possible.

And, the alternate SQL is defined in a way that the current user Id can be injected

If you don't control the query creation then the used id would need to be injected at the model level.