Error while executing a query with subquery

Hi,

I have a query that when executing in LinqPad with LightSpeed provider, no problem, the query is execute perfectly.

Here is my query:

               var filteredCategories = (from c in uow.Categories

                                        where c.VersionId == versionId

                                        select c.Id);

               var questions = from p in uow.Questions

                               where filteredCategories.Contains(p.CategoryId)

                               select p;

When I execute exactly the same query in my application, I receive this error:

Object reference not set to an instance of an object.

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractMemberCriteria(LinqQueryPlanExpression plan, MemberExpression member)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(LinqQueryPlanExpression plan, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractBinaryCriteria(LinqQueryPlanExpression plan, BinaryExpression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.Where.ExtractWhereCriteria(MethodCallExpression expression, LinqQueryPlanExpression plan, GroupResultsPlan groupPlan)

   at Mindscape.LightSpeed.Linq.Sqo.Where.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)

   at Mindscape.LightSpeed.Linq.Sqo.Select.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitConstant(ConstantExpression c)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)

   at Mindscape.LightSpeed.Linq.Sqo.Contains.GetCriteria(LinqQueryPlanExpression existingPlan, IEnumerable`1 arguments)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractMethodCriteria(LinqQueryPlanExpression plan, MethodCallExpression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression)

   at Mindscape.LightSpeed.Linq.Sqo.Where.ExtractWhereCriteria(MethodCallExpression expression, LinqQueryPlanExpression plan, GroupResultsPlan groupPlan)

   at Mindscape.LightSpeed.Linq.Sqo.Where.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)

   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Build(Expression translation, LinqQueryProvider provider)

   at Mindscape.LightSpeed.Linq.LinqQueryProvider.GetExecutionPlan(Expression expression)

   at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression)

   at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression)

   at Mindscape.LightSpeed.Linq.LinqQuery`1.GetEnumerator()

Have you any idea what I do wrong?

Ok, I worked a couple of hours on the subject and I created a sample project to find what in my query is wrong and I found the problem.

This query works:

            var filteredCategories = (from c in uow.Categories

                                      where c.VersionId == 1016771

                                      select c.Id);

            var questions = from p in uow.Questions

                            join s in uow.ProgramStructures on p.Id equals s.ItemId

                            where filteredCategories.Contains(p.CategoryId)

                            orderby p.CategoryId, s.Position

                            select p;

            foreach (var question in questions)

            {

               System.Diagnostics.Debug.WriteLine(question.Id.ToString());

            }

But if I place the version id parameter in a local variable, the same query throw an exception:

            int versionId = 1016771;

            var filteredCategories = (from c in uow.Categories

                                      where c.VersionId == versionId

                                      select c.Id);

            var questions = from p in uow.Questions

                            join s in uow.ProgramStructures on p.Id equals s.ItemId

                            where filteredCategories.Contains(p.CategoryId)

                            orderby p.CategoryId, s.Position

                            select p;

            foreach (var question in questions)

            {

               System.Diagnostics.Debug.WriteLine(question.Id.ToString());

            }

This explains why in my previous post, the query throw an exception.

Thank you.

Thanks for alerting us to this.  We've reproduced the problem and we're investigating a fix.

A workaround that seems to work for us is to put AsEnumerable() before the Contains i.e.

where filteredCategories.AsEnumerable().Contains(p.CategoryId)

This does result in two queries to the database instead of one, but the second query is still an IN query so it won't cause n+1 problems.

Hi Ivan,

This is exactly what I did to resolve my problem; call the AsEnumerable or ToList on the filteredCategories just before executing the second query.

The problem is that my Category table now have approximatly 2000 of rows returns by the first query; the sub-query.

LightSpeed generate a sql string with 2000 of parameters and I'm not sure about the performance. I know that performance should not drive the development but in this case, it's a lot of parameters.

I have not found other ways to execute a subquery that can generate a query with the pattern "IN(SELECT [TABLE])" that in my view is a drastic improvment compare to a IN clause with a lot of parameters.

Have you any idea how I can write my Linq query?

Thank you very much for you help.

Sorry, I just found that I can use a join with the filteredCategories instead of using a subquery and it does just one query to the database.

I'm not sure but for the moment, this can be the best optimize sql query that can be by the LS Generator. Am I wrong?

You may be able to optimise it further using a nested query:

from x in uow.Xs
where x.Id in (from y in uow.Ys select y.XId)
select x

or more explicitly with query objects passing a Query to the In method:

Query yquery = new Query(typeof(Y));
yquery.Projection.Add("XId");
uow.Find<X>(Entity.Attribute("Id").In(yquery));

That should generate a nested select as described in your previous message, but there may be some limits on complexity.

Any development about this bug. I know that I could be very hard to resolve but for me, the ability to create sub queries that return IQueryable and then use it later to create a more complex query is a must have.

Thank you.

Have you tried this with a recent nightly build? I notice that the original discussion was over a year ago and we have had a number of updates since then which include better support for handling sub-queries.

While I dont have the model you are working with I have quickly tried writing an equivalent query against our test model here and it produced the expected query. If you are not seeing this against a current nightly would you be able to send us a small repro project which we can look at further?

Thanks!
Jeremy