Does Effort support subqueries?

Feb 3, 2014 at 9:10 PM
Hi,

I am playing with Effort framework and I think it is awesome! But I have faced a problem when I tried to quiery DbContext using subqueries. I am trying to quiery another context inside a nested call.

Here is an example (I removed some project related data):
TableAContext.TableA.Select(data => new TableAModel
            {
//trying to query another related table
                Description = TableAContext.TableB.FirstOrDefault(u => false) != null ?
                       TableAContext.TableB.FirstOrDefault(u => false).Description :
                        null

            }).ToList();
Exception happened In \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.cs

At line 199.

arguments = [0] {Table<TableB>.Where(Extent15 => (((Convert(Convert(Extent15.TableBId)) == Convert(Project5.TableBId)) AndAlso Not(((Convert(Convert(Extent15.TableBId)) == null) OrElse (Convert(Project5.TableBId) == null)))) OrElse ((Convert(Convert(Extent15.TableBId)) == null) AndAlso (Convert(Project5.TableBId) == null)))).Select(Filter2 => new DataRow22(Convert(Filter2.TableBId))).FirstOrDefault().TableBId}

resultType = {Name = "DataRow22[]" FullName = "DataRow22[]"}
System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.InvalidOperationException: An expression of type 'System.Nullable`1[System.Int32]' cannot be used to initialize an array of type 'DataRow22'

at System.Linq.Expressions.Expression.NewArrayInit(Type type, IEnumerable`1 initializers)
   at System.Linq.Expressions.Expression.NewArrayInit(Type type, Expression[] initializers)
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.CreateSelector(Expression[] arguments, Type resultType) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.cs:line 196
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbNewInstanceExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.NewInstance.cs:line 42
   at System.Data.Entity.Core.Common.CommandTrees.DbNewInstanceExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.cs:line 102
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbApplyExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.Apply.cs:line 64
   at System.Data.Entity.Core.Common.CommandTrees.DbApplyExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.cs:line 102
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbProjectExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.Project.cs:line 40
   at System.Data.Entity.Core.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.cs:line 102
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbApplyExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.Apply.cs:line 54
   at System.Data.Entity.Core.Common.CommandTrees.DbApplyExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.cs:line 102
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbApplyExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.Apply.cs:line 64
   at System.Data.Entity.Core.Common.CommandTrees.DbApplyExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.cs:line 102
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbProjectExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.Project.cs:line 40
   at System.Data.Entity.Core.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.cs:line 102
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbUnionAllExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.UnionAll.cs:line 46
   at System.Data.Entity.Core.Common.CommandTrees.DbUnionAllExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.cs:line 102
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbSortExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.Sort.cs:line 41
   at System.Data.Entity.Core.Common.CommandTrees.DbSortExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.cs:line 102
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbProjectExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.Project.cs:line 40
   at System.Data.Entity.Core.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at Effort.Internal.DbCommandTreeTransformation.TransformVisitor.Visit(DbExpression expression) in \Source\Effort\Internal\DbCommandTreeTransformation\TransformVisitor.cs:line 102
   at Effort.Internal.CommandActions.QueryCommandAction.ExecuteDataReader(ActionContext context) in \Source\Effort\Internal\CommandActions\QueryCommandAction.cs:line 61
   at Effort.Provider.EffortEntityCommand.ExecuteDbDataReader(CommandBehavior behavior) in \Source\Effort\Provider\EffortEntityCommand.cs:line 133
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<>c__DisplayClassb.<Reader>b__8()
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TInterceptionContext,TResult](Func`1 operation, TInterceptionContext interceptionContext, Action`1 executing, Action`1 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
 --- End of inner exception stack trace ---
    at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClassb.<GetResults>b__a()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClassb.<GetResults>b__9()
   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Lazy`1.CreateValue()
   at System.Lazy`1.LazyInitValue()
   at System.Lazy`1.get_Value()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
Coordinator
Feb 20, 2014 at 7:42 AM
Hello,

Thank you for reporting this issue!

This might be a bug in the query builder in Effort. May I ask you to provide the required context and entity class definitions too?
Coordinator
Mar 2, 2014 at 7:29 PM
Unfortunately I am not able to reproduce this. May I ask you to provide a working proof of concept solution?