Table-valued function or DbQuery with parameters

[Wain123]

I have a PLSQL function which takes some number parameters and returns a table, and I would like to use it in a query. It seems to me that EF supports both table-valued functions and user-defined function translation, but not both at the same time.

If I attempt to solve this with a DbQuery, my Context contains this:

private DbQuery<RangeResult> RangeQuery { get; set; }
public IQueryable<RangeResult> Range(decimal? from, decimal? to) {
	return RangeQuery.FromSql($"SELECT * FROM num.getRange({from}, {to})");
}

My mapping contains this:

modelBuilder.Query<RangeResult>().Property(t => t.ColumnValue).HasColumnName("COLUMN_VALUE");

If I use this to start a query, it works fine:

ctx.Range(1, 10).Select(z => z.ColumnValue).Sum();

Gets translated to:

SELECT SUM("z"."COLUMN_VALUE")
FROM (
    SELECT * FROM num.getRange(:p0, :p1)
) "z"

If I use it in a subquery, it starts getting slightly weird:

ctx.TestTable.Where(z => ctx.Range(1, 10).Select(r => r.ColumnValue).Sum() == z.NumVal).Count();

Gets translated to:

SELECT COUNT(*)
FROM "TEST_TABLE" "z"
WHERE (
    SELECT SUM("r"."COLUMN_VALUE")
    FROM (
        SELECT * FROM num.getRange(1.0, 10.0)
    ) "r"
) = "z"."NUM_VAL"

The parameters to getRange are now literals instead of being properly parametrized. This is not the main issue here, but I don't understand why it happens - if it's working as intended, please explain what's going on.

If the parameters need to be evaluated on the server, it doesn't work at all:

ctx.TestTable.Where(z => ctx.Range(z.NumVal, 10).Select(r => r.ColumnValue).Sum() == 100).Count();

Throws:

System.NotSupportedException : Cannot parse expression '__ctx_0' as it has an unsupported type. Only query sources (that is, expressions that implement IEnumerable) and query operators can be parsed.
  ----> System.ArgumentException : Parameter 'expression.Type' is a 'PI.PI_STD.DataAccess.Generated.Context.IContext', which cannot be assigned to type 'System.Collections.IEnumerable'.
Parameter name: expression.Type

This is unsurprising, since the FromSql approach requires the function Range to be executed on the client.

The second attempt would be with a custom function translation. In this case, I have a static function:

public static class DB {
	public static IQueryable<RangeResult> Range(decimal? from, decimal? to) {
		throw new Exception();
	}
}

And a function mapping:

var method = typeof(DB).GetMethod(nameof(DB.Range));
modelBuilder.HasDbFunction(method).HasTranslation(parameters => new SqlFunctionExpression("num.getRange", method.ReturnType, parameters));

If I use this in a query:

ctx.TestTable.Where(z => DB.Range(z.NumVal, 10).Select(r => r.ColumnValue).Sum() == 100).Count();

This fails with a similar exception:

System.NotSupportedException : Cannot parse expression 'z.NumVal' as it has an unsupported type. Only query sources (that is, expressions that implement IEnumerable) and query operators can be parsed.
  ----> System.ArgumentException : Parameter 'expression.Type' is a 'System.Nullable`1[System.Decimal]', which cannot be assigned to type 'System.Collections.IEnumerable'.
Parameter name: expression.Type

EF Core version: 2.2
Database provider: Microsoft.EntityFrameworkCore.Oracle
Target framework: .NET Framework 4.7
Operating system: Windows 10
IDE: Visual Studio 2017 15.8.6

[ajcvickers]

@smitpatel I think there is more here than we discussed in triage; we should re-visit. It appears in 3.0 that we parameterize on the second case as well.

[ajcvickers]

@smitpatel to explain what is going on here.

[ajcvickers]

Ping @smitpatel

[smitpatel]

In LINQ, if you use variable in a method with/without lambda it generates different expression tree.

var a = 1;
var q = ctx.Table.Skip(a);
var q2 = ctx.Table.Where(t => t.Id = a);

In q since there is no lambda, compiler just execute a and put the value in constant expression in the tree. In q2 since there is lambda, compiler does not evaluate it, rather inject member of current closure.

With above in mind,
Query1: Since using in FromSql without lambda, we treat them as parameter and generate parameter.
Query2: Since it is inside lambda expression, we know that value 1/10 are constant and not from parameters so we generated constant and then evaluated in Range to generate FromSql.
In 3.0 this has changed. So now we evaluate Range function first, so arguments of FromSql started being parameters now.
Query3: You cannot pass range variable to a function call which returns IQueryable. Expression Tree gives us Range function and we cannot evaluate it.
Query4: DbFunction only supports returning a scalar which can be mapped into database not IQueryable. That would TVF support See #4319