linq2db

linq2db copied to clipboard

I'm getting the following error when trying to query a CTE selecting in the depth

Stack trace:
LinqToDB.Linq.LinqException : Expression 'parent.OrgGroup.Id' (parent.OrgGroup) is not a table.
   at LinqToDB.Linq.Builder.TableBuilder.TableContext.FindTable(Expression expression, Int32 level, Boolean throwException, Boolean throwExceptionForNull) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\TableBuilder.TableContext.cs:line 1314
   at LinqToDB.Linq.Builder.TableBuilder.TableContext.ConvertToSql(Expression expression, Int32 level, ConvertFlags flags) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\TableBuilder.TableContext.cs:line 736
   at LinqToDB.Linq.Builder.TableBuilder.TableContext.ConvertToIndex(Expression expression, Int32 level, ConvertFlags flags) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\TableBuilder.TableContext.cs:line 792
   at LinqToDB.Linq.Builder.SubQueryContext.ConvertToSql(Expression expression, Int32 level, ConvertFlags flags) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SubQueryContext.cs:line 39
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertToSql(IBuildContext context, Expression expression, Boolean unwrap) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 867
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertToSql(IBuildContext context, Expression expression, Boolean unwrap) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 787
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertCompare(IBuildContext context, ExpressionType nodeType, Expression left, Expression right) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 1689
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertPredicate(IBuildContext context, Expression expression) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 1433
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSearchCondition(IBuildContext context, Expression expression, List`1 conditions, Boolean isNotExpression) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 2676
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildWhere(IBuildContext parent, IBuildContext sequence, LambdaExpression condition, Boolean checkForSubQuery, Boolean enforceHaving) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 48
   at LinqToDB.Linq.Builder.AllJoinsBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\AllJoinsBuilder.cs:line 50
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 176
   at LinqToDB.Linq.Builder.SelectManyBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectManyBuilder.cs:line 38
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 176
   at LinqToDB.Linq.Builder.OrderByBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\OrderByBuilder.cs:line 38
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 176
   at LinqToDB.Linq.Builder.OrderByBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\OrderByBuilder.cs:line 38
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 176
   at LinqToDB.Linq.Builder.SelectBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectBuilder.cs:line 36
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 176
   at LinqToDB.Linq.Builder.ConcatUnionBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ConcatUnionBuilder.cs:line 26
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 176
   at LinqToDB.Linq.Builder.TableBuilder.<>c__DisplayClass11_0.<BuildCteContext>b__1(CteClause cteClause) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\TableBuilder.CteTableContext.cs:line 49
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildCte(Expression cteExpression, Func`2 buildFunc) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 3100
   at LinqToDB.Linq.Builder.TableBuilder.BuildCteContext(ExpressionBuilder builder, BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\TableBuilder.CteTableContext.cs:line 45
   at LinqToDB.Linq.Builder.TableBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\TableBuilder.cs:line 140
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 176
   at LinqToDB.Linq.Builder.SelectBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectBuilder.cs:line 36
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 176
   at LinqToDB.Linq.Builder.ExpressionBuilder.Build[T]() in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 147
   at LinqToDB.Linq.Query`1.CreateQuery(IDataContext dataContext, Expression expr) in C:\projects\linq2db\Source\LinqToDB\Linq\Query.cs:line 268

Steps to reproduce

Just create a simple table with my type OrgGroup and try to setup a wrapper class around it to track the depth.

var queryable = OrgGroups;
return _connection.GetCte<OrgGroupDepthWrapper>(previous =>
    {
        var parentQuery = from parent in queryable
            select new OrgGroupDepthWrapper
            {
                OrgGroup = parent,
                Depth = 0
            };

        var childQuery = from child in queryable
            from parent in previous.InnerJoin(parent => parent.OrgGroup.Id == child.ParentId)
            orderby parent.Depth + 1, child.GroupName
            select new OrgGroupDepthWrapper
            {
                OrgGroup = child,
                Depth = parent.Depth + 1
            };

        return parentQuery.Union(childQuery);
    })
    .Select(wrapper => wrapper.OrgGroup);

Environment details

linq2db version: 2.6.4 Database Server: SqlLite

@hahn-kev, i confirm problem but fixing this might be not a trivial task. There is problem when you assing objects in CTE projection. As a quick workaround i suggest to rewrite your query.

class OrgGroupDepthWrapper
{
	public int Id { get; set; }
	public int Depth { get; set; }
}

var queryable = db.GetTable<OrgGroup>();
var cte = db.GetCte<OrgGroupDepthWrapper>(previous =>
    {
        var parentQuery = from parent in queryable
            select new OrgGroupDepthWrapper
            {
                Id = parent.Id,
                Depth = 0
            };

        var childQuery = from child in queryable
	        from parent in previous.InnerJoin(parent => parent.Id == child.ParentId)
	        select new OrgGroupDepthWrapper
	        {
		        Id = child.Id,
		        Depth = parent.Depth + 1
	        };

        return parentQuery.Union(childQuery);
    });

var query = from q in queryable
	from w in cte.InnerJoin(w => w.Id == q.Id)
	orderby w.Depth, q.GroupName
	select q;

it produces very optimal query:

WITH [previous] ([Id], [Depth])
AS
(
	SELECT
		[parent_1].[Id],
		0
	FROM
		[OrgGroup] [parent_1]
	UNION
	SELECT
		[child].[Id],
		[parent_2].[Depth] + 1
	FROM
		[OrgGroup] [child]
			INNER JOIN [previous] [parent_2] ON [parent_2].[Id] = [child].[ParentId]
)
SELECT
	[q].[Id],
	[q].[ParentId],
	[q].[GroupName]
FROM
	[OrgGroup] [q]
		INNER JOIN [previous] [w] ON [w].[Id] = [q].[Id]
ORDER BY
	[w].[Depth],
	[q].[GroupName]

Thanks. Sorry I'm always breaking CTE's lol

I ended up working around it by determining the depth server side and then sorting it after the fact, the result set was pretty small so it doesn't make much of a difference.

@hahn-kev, we have very difficult for support linq parser, and CTE is trying to work with this and sometimes without success. Keep it simpler as you can and it will work.

We are working on improving parsing, so possible in near future your queries will work without any limitations.