Allow FromSql on IQueryable<T>

[Neme12]

EF Core 3.0 made a change to remove FromSql on IQueryable<T> and replace it with FromSql* methods on DbSet<T> itself, so it's no longer composable. Why is this no longer supported?

My use case was:
I was overriding ASP.NET Core's UserStore like this:

public override IQueryable<ApplicationUser> Users => base.Users.Include(u => u.UserProfiles);

to make sure that the UserProfiles collection of ApplicationUser is always included, because we are using it everywhere we're using ApplicationUser itself.

In a controller, we implemented search manually using SQL because EF Core did a poor job translating the LINQ we attempted to write:

var users = searchText is null
    ? _userManager.Users
    : _userManager.Users.FromSql($"...");

Now with EF Core 3.0, I can't use the UserManager abstraction anymore and i'm forced to drill down to the DbContext to get the DbSet itself and copy the Include here. This seems like a regression 😕

[ajcvickers]

@smitpatel This seems to work correctly in 2.2. That is, an IQueryable that is not a DbSet can have FromSql used on it and the results we get back still honor the Include. If FromSql was allowed on IQueryable, would this work with the 3.0 query pipeline?

[smitpatel]

If FromSql was allowed on IQueryable, would this work with the 3.0 query pipeline?

Yes, it will work. I believe, when adding the restriction on DbSet, we explicitly decided that user would have to specify FromSql before Include. Are we changing it?

[ajcvickers]

@smitpatel No decision to change it yet; we will discuss.

[ajcvickers]

Discussed in triage and given that FromSql always replaces the query root, even if it is not called at the root, we think that it makes sense to keep enforcing that it only be applied to the root. This can break certain ways of writing repositories (or similar) that are valid, like here, but these can generally be refactored to inject the root rather than expose the root as a simple property.

[Neme12]

@ajcvickers Then should it also be disallowed to use FromSql on a DbSet that it mapped to a query (using ToQuery()) for consistency? That seems like it would let people bypass this limitation and have the same effect as using FromSql on an IQueryable created using LINQ.

[ajcvickers]

@Neme12 This is an interesting point, probably related to #18903 and #17270 which I referenced in another response to you.

Reopening to discuss in triage.

[smitpatel]

That is a bug which would go away when fixing #18903

[ajcvickers]

Discussed in triage and, as Smit says, we will handle this as part of #18903.