Query: FromSql with DbParameters throws Must declare the scalar variable/No mapping to a relational type ... SqlParameter

[Hajisharifi]

Hi,
The 'FromSql' lost the parameters after 'Contains', 'GroupBy' ...

Exception message1: Must declare the scalar variable "@Param1"
Exception message2: No mapping to a relational type can be found for the CLR type 'SqlParameter'

Steps to reproduce

CREATE TABLE [Table1]
(
[ID1] INT IDENTITY PRIMARY KEY NOT NULL
);
GO
CREATE TABLE [Table2]
(
[ID2] INT IDENTITY PRIMARY KEY NOT NULL,
[ID1] INT NOT NULL
);
GO
CREATE FUNCTION [MyFunc](@param1 int)
RETURNS TABLE AS
RETURN ( SELECT * FROM Table1 WHERE ID1 >= @param1 );
GO

var q_sql_func = db.Table1.FromSql("SELECT * FROM [MyFunc] (@Param1)", new SqlParameter("@Param1", 123));

var q_sql_func2 = q_sql_func.Select(r => r.ID1);

//OK
var test1 = q_sql_func2.ToList();

//OK
var test2 = db.Table2.ToList();

//OK
var test3 = db.Table2.Join(q_sql_func, t2 => t2.ID1, t1 => t1.ID1, (t2,t1) => t1).ToList();

//ERROR: Must declare the scalar variable "@Param1"
var test4 = db.Table2.Where(r => q_sql_func2.Contains(r.ID1)).ToList();

//ERROR: No mapping to a relational type can be found for the CLR type 'SqlParameter'
var test5 = db.Table2
   .GroupBy(r => r.ID2)
   .Select(g => g.Key)
   .Where(id => q_sql_func2.Contains(id))
   .ToList();

Further technical details

EF Core version: 2.0.0-preview2-25179
Target framework: net46
Database Provider: SqlServer

Project (Console-net46):
EFCoreFromSqlParam.zip

I'm sorry for my English grammar.
Thanks.

[smitpatel]

@anpete @maumar - Did we support syntax like this? Notice FromSql inlined inside predicate.

var result3 = db.Posts.Where(p => db.Blogs.FromSql("SELECT * FROM Blogs WHERE Id >= @Param1", new SqlParameter("@Param1", 123)).Select(r => r.Id).Contains(p.BlogId)).ToList();

[anpete]

@smitpatel Yeah, seems like it should work.

[smitpatel]

At present, FromSql with parameters inside lambda function does not work. (also tested in 1.1.2)
If FromSql is inlined (like above) then parameter extraction doesn't extract parameters because of it being inside of lambda.
If FromSql is stored in a variable then ET has ConstantExpression of EntityQueryable which again does not extract parameters.
So we end up with FromSql in our query model with SqlParameter as arg and fails to generate SQL.

[anpete]

@smitpatel I fixed some cases in 7a18610, might want to check if it hits a different code path in parameter extraction

[smitpatel]

It fails in VisitConstant part so parameters are never extracted and above code path is not reached.

[smitpatel]

Manually verified that above repro code works with #8885