Hi guys,

Imagine this query (simplified):

from c in meta.Customers
select new
{
    c.Name,
    c.OrderCount = (from o in c.Orders).Count()
}

Now, I'd like to extract only those customer who have positive orders, i.e.

from c in meta.Customers
where  (from o in c.Orders).Count() > 0
select new
{
    c.Name,
    c.OrderCount = (from o in c.Orders).Count()
}

I get the error below. Is there a way, perhaps even pretier one?

An item with the same key has already been added.

at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
   at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
   at SD.LLBLGen.Pro.LinqSupportClasses.MappingTracker.AddTypeInTreeForAlias(SetAlias alias, Type typeInTree)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.ProcessSelectManySide(Expression handledSide)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallSelectMany(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleAggregateFunctionMethodCall(MethodCallExpression expressionToHandle, AggregateFunction function)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleMemberAssignment(MemberAssignment assignmentToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleMemberBinding(MemberBinding bindingToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleMemberBindingList(ReadOnlyCollection`1 listToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleMemberInitExpression(MemberInitExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleLambdaExpression(LambdaExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallSelect(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpressionList(ReadOnlyCollection`1 listToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleMemberAssignment(MemberAssignment assignmentToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleMemberBinding(MemberBinding bindingToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleMemberBindingList(ReadOnlyCollection`1 listToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleMemberInitExpression(MemberInitExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleLambdaExpression(LambdaExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallSelect(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Righthand.Betek2.Web.Business.Playground.Program.LoadEvents() in C:\Users\Miha\Documents\Projects\Righthand\Betek2\Righthand.Betek2.Web.Business.Playground\Program.cs:line 64

What about the following?

from c in meta.Customers
where c.Orders.Count() > 0
select new
{
    c.Name,
    c.OrderCount = (from o in c.Orders).Count()
}

Hi Walaa,

It might work, however, my sample was a simplified one. the () was intentional as the real subquery is more complex that c.Order.Count(). I also think my subquery won't produce the correct results, it should be:

from c in meta.Customers
select new
{
    c.Name,
    c.OrderCount = (from o in meta.Orders where o.CustomerId = c.Id).Count()
}

Imagine a more complex query to get to the Count()...

I think this was a bug we fixed very recently.

I can't reproduce it with this query:

var q = from c in metaData.Customer
        where (from o in c.Orders select o).Count() > 0
        select new
        {
            c.CompanyName,
            OrderCount = (from o in c.Orders select o).Count()
        };

For convenience I've attached the latest build

Hi Frans,

Unfortunately it doesn't help. I'll try to create a sample.

mihies wrote:

Hi Frans,

Unfortunately it doesn't help. I'll try to create a sample.

Please do that with northwind or adventureworks. that saves me the trouble creating db/code etc.

I have tried but somehow it works in Northwind. But here is my simplified query:

var query = (from ld in meta.Location
                            select new
                            {
                                Id = ld.Id,
                                Title = ld.Name,
                                Events = from e in ld.EventName
                                        [b]where (from g in e.GameResult select g).Count()>0[/b]
                                          select new
                                          {
                                              Id = e.Id,
                                              Title = e.Name,
                                          }
                            }).ToList();

Database (simplified) Location->EventName (through Location.Id->EventName.LocationId) EventName->Game (through EventName.Id -> Game.EventNameId)

If I run the query above without bolded where part it produces these correct SQL statements:

DECLARE @LO11 Int
SET @LO11 = 1

SELECT  [LPLA_1].[Id],
        [LPLA_1].[Name] AS [Title],
        @LO11 AS [LPFA_2]
FROM    [db].[dbo].[Location] [LPLA_1] 


SELECT  [LPLA_2].[Id],
        [LPLA_2].[Name] AS [Title],
        [LPLA_2].[LocationId]
FROM    [db].[dbo].[EventName] [LPLA_2]
WHERE   ( ( (  EXISTS ( SELECT  [LPLA_1].[Id]
                        FROM    [db].[dbo].[Location] [LPLA_1]
                        WHERE   ( [LPLA_1].[Id] = [LPLA_2].[LocationId] ) ))) )

As soon as I introduce the mentioned where it is stuck here:

DECLARE @LO11 Int
SET @LO11=1

SELECT
 [LPLA_1].[Id], [LPLA_1].[Name] AS [Title], @LO11 AS [LPFA_3], [b][LPLA_3].[EventNameId][/b]
FROM
 [db].[dbo].[Location] [LPLA_1] 

The bolded column can't be bound (SQL server error). Odd thing is that similar Northwind query works. Can it be the fact that my project was created using LLBLGenPro 2.0 (and then generated using 2.6)?

Could you check nullability of fields?

I'll look into your query as well, to see if I can reproduce it.

This repro case makes it visible

[Test]
public void NestedQueryWthSameFieldsAsOuter()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = from e in metaData.Employee
                select new
                {
                    FooId = e.EmployeeId,
                    OtherId = e.ReportsTo,
                    NestedElements = from o in e.Orders
                                     where (from od in o.OrderDetails select od).Count() > 0
                                     select new
                                     {
                                         BarId = o.OrderId,
                                         OtherId = o.EmployeeId
                                     }
                };

        foreach(var v in q)
        {
        }
    }
}

It's not the same error, though I think it's related. The first query (parents) contains a wrong field: the FK of Order. This is unnecessary, as the nested set is filtered by the parent. It also makes the query go wrong at runtime. Removing the where solves it, strangely enough. I'll look into fixing this.

(edit) what happens is that the nested-query post processor gathers all predicates from the nested query and checks whether they're containing a correlation relation. If so, it's always assumed this is a correlation relation with the parent. In your query, this isn't the case, as the where contains a query which has a correlation relation (o.Orderdetails) but it's not pointing to the parent, which should be taken into account. Seems complicated, but I hope to have a fix for this.

Fixed See attached dll.

Otis wrote:

Fixed See attached dll.

Looks like you've nailed it this time. Excellent. Thanks.