Issue with subquery referencing outer query field in where clause

Hi.

I have an issue with generating a subquery in a where clause, that itself contains a where clause referencing a field in the outer query. A minimal reproduction project is attached.

The SQL I'm shooting for is something like this:

SELECT
  Movie.Id AS "Movie.Id",
FROM
  Movie
WHERE
  NOT (Movie.Date IN
  (
    SELECT
      Comment.Date
    FROM
      Comment
    WHERE
      Comment.Category = Movie.Category
  )

I have tried achieving this like this:

from m in uow.Movies
where !uow.Comments.Where(c => c.Category == m.Category).Select(c => c.Date).Contains(m.Date)
select m.Id;

However, this produces a strange SQL cross join:

SELECT
  Movie.Id AS "Movie.Id",
FROM
  Movie
WHERE
  NOT (Movie.Date IN (SELECT
    Comment.Date AS "Comment.Date"
  FROM
    Comment
    CROSS JOIN
    Movie
  WHERE
    Comment.Category = Movie.Category
  )

Any ideas why this is happening? I have also tried to perform this query in another way:

from m in uow.Movies
where !uow.Comments.Any(c => c.Category == m.Category && c.Date == m.Date)
select m.Id;

But this produces the following exception:

Unhandled Exception: System.InvalidCastException: Cannot cast from source type to destination type.
  at Mindscape.LightSpeed.Linq.Sqo.AnyAllBase.ApplyCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, Mindscape.LightSpeed.Querying.QueryExpression applyTo, System.Linq.Expressions.Expression[] arguments) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.ScalarCriteriaSqo.GetCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, IEnumerable`1 arguments) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractMethodCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.MethodCallExpression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.Where.ExtractWhereCriteria (System.Linq.Expressions.MethodCallExpression expression, Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, Mindscape.LightSpeed.Linq.Plan.GroupResultsPlan groupPlan) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.Where.Evaluate (Mindscape.LightSpeed.Linq.Plan.ExpressionVisitor visitor, System.Linq.Expressions.MethodCallExpression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall (System.Linq.Expressions.MethodCallExpression exp) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit (System.Linq.Expressions.Expression exp) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Build (System.Linq.Expressions.Expression translation, Mindscape.LightSpeed.Linq.LinqQueryProvider provider) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.LinqQueryProvider.GetExecutionPlan (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.LinqQuery`1[GettingStarted.Movie].GetEnumerator () [0x00000] in <filename unknown>:0 
  at System.Collections.Generic.List`1[GettingStarted.Movie].AddEnumerable (IEnumerable`1 enumerable) [0x00000] in /home/EAGLERD/roji/work/mono-2.10.8.1-egl/mcs/class/corlib/System.Collections.Generic/List.cs:126 
  at System.Collections.Generic.List`1[GettingStarted.Movie]..ctor (IEnumerable`1 collection) [0x0002f] in /home/EAGLERD/roji/work/mono-2.10.8.1-egl/mcs/class/corlib/System.Collections.Generic/List.cs:63 
  at System.Linq.Enumerable.ToList[Movie] (IEnumerable`1 source) [0x00006] in /home/EAGLERD/roji/work/mono-2.10.8.1-egl/mcs/class/System.Core/System.Linq/Enumerable.cs:2847 
  at GettingStarted.Program.Problem2 (GettingStarted.ModelUnitOfWork uow) [0x00022] in /home/EAGLERD/roji/work/lightspeed_problem/Subquery/Program.cs:83 
  at GettingStarted.Program.Main (System.String[] args) [0x000fc] in /home/EAGLERD/roji/work/lightspeed_problem/Subquery/Program.cs:66 
[ERROR] FATAL UNHANDLED EXCEPTION: System.InvalidCastException: Cannot cast from source type to destination type.
  at Mindscape.LightSpeed.Linq.Sqo.AnyAllBase.ApplyCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, Mindscape.LightSpeed.Querying.QueryExpression applyTo, System.Linq.Expressions.Expression[] arguments) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.ScalarCriteriaSqo.GetCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, IEnumerable`1 arguments) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractMethodCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.MethodCallExpression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria (Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.Where.ExtractWhereCriteria (System.Linq.Expressions.MethodCallExpression expression, Mindscape.LightSpeed.Linq.Plan.LinqQueryPlanExpression plan, Mindscape.LightSpeed.Linq.Plan.GroupResultsPlan groupPlan) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Sqo.Where.Evaluate (Mindscape.LightSpeed.Linq.Plan.ExpressionVisitor visitor, System.Linq.Expressions.MethodCallExpression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall (System.Linq.Expressions.MethodCallExpression exp) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit (System.Linq.Expressions.Expression exp) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Build (System.Linq.Expressions.Expression translation, Mindscape.LightSpeed.Linq.LinqQueryProvider provider) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.LinqQueryProvider.GetExecutionPlan (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Mindscape.LightSpeed.Linq.LinqQuery`1[GettingStarted.Movie].GetEnumerator () [0x00000] in <filename unknown>:0 
  at System.Collections.Generic.List`1[GettingStarted.Movie].AddEnumerable (IEnumerable`1 enumerable) [0x00000] in /home/EAGLERD/roji/work/mono-2.10.8.1-egl/mcs/class/corlib/System.Collections.Generic/List.cs:126 
  at System.Collections.Generic.List`1[GettingStarted.Movie]..ctor (IEnumerable`1 collection) [0x0002f] in /home/EAGLERD/roji/work/mono-2.10.8.1-egl/mcs/class/corlib/System.Collections.Generic/List.cs:63 
  at System.Linq.Enumerable.ToList[Movie] (IEnumerable`1 source) [0x00006] in /home/EAGLERD/roji/work/mono-2.10.8.1-egl/mcs/class/System.Core/System.Linq/Enumerable.cs:2847 
  at GettingStarted.Program.Problem2 (GettingStarted.ModelUnitOfWork uow) [0x00022] in /home/EAGLERD/roji/work/lightspeed_problem/Subquery/Program.cs:83 
  at GettingStarted.Program.Main (System.String[] args) [0x000fc] in /home/EAGLERD/roji/work/lightspeed_problem/Subquery/Program.cs:66 

Thanks for any help on this!

Shay

You will not be able to emit a statement exactly as you have specified as the sub-query needs to reference the outer table and we dont have a concept of this when specifying criteria in the LightSpeed Querying API. This is also why you are seeing a cross join, as you have a reference to a Member entity in the criteria of your sub-query, but there hasn't been an explicit join for that Query instance - so LightSpeed will automatically join in the table as required.

The second example looks like more how you would want to write this, Ill have a look into the exception as that must be coming from uow.Comments being an unexpected argument to our Any() operator during translation.

A better way of doing this would be to specify this as an exists criteria rather than using IN, in general the use of .In() or .Contains() in LINQ requires that you pass in a known array of objects to compare to - you want to compare to a query, which is supported but not if you are referencing the outer table in criteria within the sub-query as mentioned above.

Try this instead to generate the EXISTS criteria:

var query = new Query(typeof(Movie))
{
  QueryExpression = !(Entity.Attribute("Comments.Category") == Entity.Attribute(typeof(Movie), "Category") && Entity.Attribute("Comments.Date") == Entity.Attribute(typeof(Movie), "Date"))
};

var results = unitOfWork.Find(query);

Jeremy

Thanks for the answer Jeremy.

The inner query references a pretty small table, so my current workaround is simply to load it into memory via a separate earlier query, and then performs the "NOT EXISTS" in memory. This also allows me to keep LINQ rather than switching to the query object API.

So no big blocker for me here, but it definitely seems like something people would need to do with LINQ... would be glad to hear of any progress.

Shay