Calling raw SQL with return type IQueryable<T>

[garkushin]

Good afternoon, and I am using Microsoft.EntityFrameworkCore 5.0.11 and linq2db.EntityFrameworkCore 5.0.8, I am trying your library for the first time and I have a few questions. I have:

public enum EdgeType
{
    ...
}

public class Edge
{
    public Guid FromId { get; set; }
    public Node From { get; set; }
    public Guid ToId { get; set; }
    public Node To { get; set; }
    public EdgeType Type { get; set; }
}

public abstract class Node
{
    public string OwnerType { get; set; }
    public Guid OwnerId { get; set; }
    public ICollection<Edge> Edges { get; set; }
}

public class GraphPath
{
    public Guid[] Path { get; set; }
    public Guid FromId { get; set; }
    public Guid ToId { get; set; }
    public EdgeType Type { get; set; }
    public int Depth { get; set; }
}

public class DataContext : DbContext
{
    ...
    public DbSet<Node> Nodes { get; set; }
    public DbSet<Edge> Edges { get; set; }
    public DbSet<GraphPath> GraphPath { get; set; }
    public IQueryable<GraphPath> SearchGraph(Node root, EdgeType[] types, int depth, int limit)
    {
        ...
    }
    ...
}

SQL function with signature

create or replace function search_graph(IN "root_id" uuid,
                                        IN "types" edge_type[] default array[]::edge_type[],
                                        IN "depth" int default 99999,
                                        IN "limit" int8 default 2000000000,
                                        out "Path" uuid[],
                                        out "FromId" uuid,
                                        out "ToId" uuid,
                                        out "Type" edge_type,
                                        out "Depth" int)
    returns setof record as
$$
declare
    sql text;
begin
    sql := format($_$ WITH RECURSIVE search( "FromId", "ToId", "Type", "Depth", "Path" ) AS
    (select ...)
$_$, "root_id", "types", "limit", "depth");
    return query execute sql;
end;
$$ language plpgsql strict;

which returns a table with multiple rows of data.

I want to get: I need to implement requests like

{
    ...
    var q = from node in db.Nodes
            let grath = db.SearchGraph(node, types, 10, 10)
            where grath.Any()//or other conditions with ".Where"
            select new { node, grath = grath.ToList() };

    var result = q.ToLinqToDB().ToList();
    ...
}

How I tried to implement this:

i had several attempts to implement this

version 1, use FromSqlRaw EF Core

public IQueryable<GraphPath> SearchGraph1(Node node, EdgeType[] types, int depth, int limit)
{
    var nameTranslator = NpgsqlConnection.GlobalTypeMapper.DefaultNameTranslator;
    var typesStr = string.Join("','", types.Select(t => nameTranslator.TranslateMemberName(t.ToString())));

    var sql = $"select * from search_graph({node.OwnerId}, array['{typesStr}']::edge_type[], {depth}, {limit})";
    return GraphPath.FromSqlRaw(sql).ToLinqToDB();
}

version 2, use FromSql linq2db

public IQueryable<GraphPath> SearchGraph2(Node root, EdgeType[] types, int depth = 99999, int limit = 2000000000)
{
    var nameTranslator = NpgsqlConnection.GlobalTypeMapper.DefaultNameTranslator;
    var typesStr = string.Join("','", types.Select(t => nameTranslator.TranslateMemberName(t.ToString())));

    var sql = $"select * from search_graph('{root.OwnerId}', array['{typesStr}']::edge_type[], {depth}, {limit})";
    return this.CreateLinqToDbContext().FromSql<GraphPath>(sql);
}

version 3, use Sql.Extension

[Sql.Extension("select * from search_graph('{root}', array['{types}']::edge_type[], {depth}, {limit})", ServerSideOnly = true)]
public static IQueryable<GraphPath> SearchGraph3(Guid root, string types, int depth, int limit)
{
    throw new LinqException($"'{nameof(SearchGraph)}' is server-side method.");
}

version 4, use recursive CTE

public IQueryable<GraphPath> SearchGraph4(Node node, EdgeType[] types, int depth = 99999, int limit = 2000000000)
{
    var graphSte = this.CreateLinqToDbContext().GetCte<GraphPath>(search =>
        (
            (
                from edge in Edges
                where edge.FromId == node.OwnerId
                        && types.Contains(edge.Type)
                select new GraphPath
                {
                    FromId = edge.FromId,
                    Path = Sql.Ext.PostgreSQL().NewArray(edge.FromId),
                    ToId = edge.ToId,
                    Type = edge.Type,
                    Depth = 1
                }
            )
            .OrderByDescending(e => e.Type)
            .Take(limit)
        )
        .Concat
        (
            (
                from edge in Edges
                from sg in search
                where edge.FromId == sg.ToId
                    && !Sql.Ext.PostgreSQL().ArrayContains(sg.Path, edge.FromId)
                    && types.Contains(edge.Type)
                    && sg.Depth <= depth
                select new GraphPath
                {
                    FromId = edge.FromId,
                    Path = Sql.Ext.PostgreSQL().ArrayAppend(sg.Path, edge.FromId),
                    ToId = edge.ToId,
                    Type = edge.Type,
                    Depth = sg.Depth + 1
                }
            )
            .OrderByDescending(e => e.Type)
            .Take(limit)
        )
    )
    .Select(x => new GraphPath
    {
        Depth = x.Depth,
        FromId = x.FromId,
        Path = Sql.Ext.PostgreSQL().ArrayAppend(x.Path, x.ToId),
        Type = x.Type,
        ToId = x.ToId
    });

    return graphSte;
}

Version 3 did not work even once, I found out that this is related to the return type IQueryable, if you use a primitive return type, then SQL is generated, but, of course, it does not work correctly. Is there any way to use Sql.Extension with an IQueryable return type?

Versions 1,2,4 work as separate queries of the form

var graph = db.SearchGraph(node, types, 10, 10).ToList();

But they do not work inside other requests, as in the example above.

Tell me which way should I go to implement this? And one more thing, is it possible to combine EF Core DbContext and LinqToDbContext in one request as it is done in versions 2 and 4?

[sdanyliv]

Will prepare sample tomorrow, sorry for delay. It is strange that CTE won't work.

[garkushin]

Here is an error while trying to execute "cte", thought it might help.

System.InvalidOperationException
  HResult=0x80131509
  Сообщение = variable 'node' of type 'Domain.Entities.Node' referenced from scope '', but it is not defined
  Источник = System.Linq.Expressions
  Трассировка стека:
   в System.Linq.Expressions.Compiler.VariableBinder.Reference(ParameterExpression node, VariableStorageKind storage)
   в System.Linq.Expressions.Compiler.VariableBinder.VisitParameter(ParameterExpression node)
   в System.Linq.Expressions.ParameterExpression.Accept(ExpressionVisitor visitor)
   в System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   в System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)
   в System.Linq.Expressions.Compiler.VariableBinder.VisitLambda[T](Expression`1 node)
   в System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)
   в System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   в System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda)
   в System.Linq.Expressions.LambdaExpression.Compile()
   в LinqToDB.EntityFrameworkCore.LinqToDBForEFToolsImplDefault.EvaluateExpression(Expression expr)
   в System.Linq.Enumerable.SelectIListIterator`2.ToArray()
   в LinqToDB.EntityFrameworkCore.LinqToDBForEFToolsImplDefault.<>c__DisplayClass39_0.<TransformExpression>g__LocalTransform|0(Expression e)
   в LinqToDB.EntityFrameworkCore.LinqToDBForEFToolsImplDefault.<>c__DisplayClass39_0.<TransformExpression>b__1(Expression e)
   в LinqToDB.Expressions.Extensions.<>c.<Transform>b__16_0(Func`2 f, Expression e)
   в LinqToDB.Expressions.TransformInfoVisitor`1.Transform(Expression expr)
   в LinqToDB.Expressions.TransformInfoVisitor`1.Transform[T](IList`1 source)
   в LinqToDB.Expressions.TransformInfoVisitor`1.Transform(Expression expr)
   в LinqToDB.Expressions.TransformInfoVisitor`1.Transform(Expression expr)
   в LinqToDB.Expressions.TransformInfoVisitor`1.Transform(Expression expr)
   в LinqToDB.Expressions.TransformInfoVisitor`1.Transform[T](IList`1 source)
   в LinqToDB.Expressions.TransformInfoVisitor`1.Transform(Expression expr)
   в LinqToDB.Expressions.TransformInfoVisitor`1.Transform[T](IList`1 source)
   в LinqToDB.Expressions.TransformInfoVisitor`1.Transform(Expression expr)
   в LinqToDB.Expressions.TransformInfoVisitor`1.Transform[T](IList`1 source)
   в LinqToDB.Expressions.TransformInfoVisitor`1.Transform(Expression expr)
   в LinqToDB.Expressions.Extensions.Transform(Expression expr, Func`2 func)
   в LinqToDB.EntityFrameworkCore.LinqToDBForEFToolsImplDefault.TransformExpression(Expression expression, IDataContext dc, DbContext ctx, IModel model)
   в LinqToDB.EntityFrameworkCore.LinqToDBForEFTools.TransformExpression(Expression expression, IDataContext dc, DbContext ctx, IModel model)
   в LinqToDB.EntityFrameworkCore.LinqToDBForEFToolsDataConnection.ProcessExpression(Expression expression)
   в LinqToDB.Linq.Query`1.GetQuery(IDataContext dataContext, Expression& expr, Boolean& dependsOnParameters)
   в LinqToDB.Linq.ExpressionQuery`1.GetQuery(Expression& expression, Boolean cache, Boolean& dependsOnParameters)
   в LinqToDB.Linq.ExpressionQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   в LinqToDB.EntityFrameworkCore.Internal.LinqToDBForEFQueryProvider`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   в System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   в System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   в Application.Common.Interfaces.TemplateMigrationMethod.<Up>d__3.MoveNext() в src\Application\Common\MigrationMethods\TemplateMigrationMethod.cs:строка 40

[sdanyliv]

Can you create small reproducible sample? I cannot reproduce. Looks like problem with EF Core extension itself, but I cannot figure out what happened.

It can be sample without database. I just need the same exception. Also NewArray and other custom extensions are needed.

Also with defining function (not tested):

[ExpressionMethod(nameof(SearchGraph3Impl))]
public static IQueryable<GraphPath> SearchGraph3(IDataContext dc, Guid root, string types, int depth, int limit)
{
    return SearchGraph3Impl().Compile().Invoke(dc, root, types, depth, limit);
}

private static Expression<Func<IDataContext, Guid, string, int, int, IQueryable<GraphPath>>> SearchGraph3Impl()
{
    return (dc, root, types, depth, limit) =>
        dc.FromSql<GraphPath>($"search_graph({root}, array[{types}]::edge_type[], {depth}, {limit})");
}

[garkushin]

https://github.com/garkushin/repro-191 I did not clean up the code "Minimally reproduce" state, there remains a lot of redundant code, but the error is reproduced. I hope you understand.

The option that you describe will try will write about the result later.

[sdanyliv]

Well, finally I see what happened. Will figure out how to solve that elegantly. But actually problem in your code.

[garkushin]

I look forward to hearing from you. I'm wondering where the error is :)

[sdanyliv]

Sorry for delay. Problem is that CTE in the SQL cannot have parameters. If you call SearchGraph4 with node parameter which can be evaluated, linq2db can build SQL. But in query:

from node in db.Nodes
            let grath = db.SearchGraph(node, types, 10, 10)
...      

node is dynamic parameter which depends on record from other recordset. In many other cases we can solve this limitation by ExpressionMethod but not for this task. Since CTE cannot be parametrized we will fail.

[garkushin]

It's a pity. I tried the version you described earlier and got the same error. https://github.com/linq2db/linq2db.EntityFrameworkCore/issues/191#issuecomment-964934430

"variable 'node' of type 'Domain.Entities.Node' referenced from scope '', but it is not defined"

Any more ideas how I can do this?

[garkushin]

If nothing can be done, the issue can be closed, but I would like to receive a definitive answer.

[garkushin]

After some time, I came back to this issue, here is the solution I was looking for:

[Sql.TableFunction(Name="search_graph")]
public IQueryable<GraphPath> SearchGraph([ExprParameter] Guid root, int depth, int limit)
{
    using var ctx = this.CreateLinqToDBContext();
    var method = MethodHelper.GetMethodInfo(SearchGraph, root, depth, limit);
    return ctx.GetTable<GraphPath>(this, method, root, depth, limit);
}