Let's assume we have a simple SQL table with the following columns and data: ID ISOLanguageCode ISOCountryCode 1 en US 1 en CA 1 en MX 1 es US

We want to write a query that returns an object grouped by the ID and ISOLanguageCode with a List of ISOCountryCodes for each combination. Below is our (failed) attempt at the query, which gives the error:

{"Couldn't create any correlation filter lambda because the nested query didn't have any correlation filters. 
Please specify a filter in the nested query to tie the nested query to the parent query"}

Here is the query:

  var results = queryable
                .GroupBy(x => new
                {
                    x.ID,
                    x.ISOLanguageCode
                }).Select(y => new
                {
                    y.Key.ID,
                    y.Key.ISOLanguageCode,
                    Countries = y.Select(country => country.ISOCountryCode)
                }).ToList();

We also tried adding the below to see if that would suffice as a correlation filter (although I'm not sure why we would have to).

  var results = queryable
                .GroupBy(x => new
                {
                    x.ID,
                    x.ISOLanguageCode
                }).Select(y => new
                {
                    y.Key.ID,
                    y.Key.ISOLanguageCode,
                    Countries = y.Where(c => c.ID == y.Key.ID && c.ISOLanguageCode == y.Key.ISOLanguageCode).Select(country => country.ISOCountryCode)
                }).ToList();

This additional .Where(...) has no impact on the error.

Any idea what is going on? Thanks so much (as always) for the help.

Groupby in linq isn't equal to groupby in sql. The main difference is that groupby in linq groups the entire rows under a key in sets in the result, and groupby in sql merges the rows if they have equal values into a single set.

So because of this, a groupby query in linq requires both grouping based on key and the entire row that was grouped. As sql groupby doesn't allow that, we fetch the groupby query in 2 queries: one with the groupby key and one which fetches the grouped rows as a nested query. The behavior isn't convertable to a single sql query so appending a select with another nested query to the groupby therefore runs into a problem: to which query does the nested query belong to?

Your query assumes the groupby in the linq is equal to the groupby in sql, but that's not the case.

Fortunately what you want is doable: the groupby in linq already results in what you want: the list of rows grouped and stored under the key.

var q = from l in queryable
            group l by new {l.ID, l.ISOLanguageCode} into g
            select g;

that's it Enumerate q, you'll see it has IGrouping<> elements, where the Key property is equal to the tuple formed by the 2 fields you grouped on, and the IGrouping value itself is enumerable and contains teh rows grouped, so contains the language entities grouped.

Example:

using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    var q = from o in metaData.Order
            group o by new { o.CustomerId, o.EmployeeId } into g
            select g;

    foreach(var v in q)
    {
        Console.WriteLine("{0}, {1}", v.Key.CustomerId, v.Key.EmployeeId);
        foreach(var o in v)
        {
            Console.WriteLine(o.OrderId);
        }
    }
}

Thanks for the reply! I've been working at this all morning otherwise I would have replied sooner.

That does indeed appear to work for the simple query I provided. Now for the plot to thicken... I have 2 additional complications to the query that both seem to present their own issues. I've boiled it down to the bare minimum to illustrate the issue below:

var results = queryable.GroupBy(x => new
            {
                x.ID,
                ChildIdsIncludingObsolete = x.SelfJoinedChildTable
                                             .Where(child => child.ISOLanguageCode == x.ISOLanguageCode)
                                             .Select(child => new
                                             {
                                                 child.ID,
                                                 child.IsObsolete
                                             })
                                             .Distinct(),
                DefaultTitle = x.ProductCategory.TitleContent.BaseContent
            }).ToList();

If with the ChildIdsIncludingObsolete field left in, I get the following error:

{"Unable to cast object of type 'System.Linq.Expressions.UnaryExpression' to type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SetExpression'."}

I'm guessing it has something to do with not being able to group when there is an IEnumerable in the key, but I'm really not sure.

The second problem comes from the DefaultTitle which I'm pulling back via some LLBLGen Pro relations we have set up in our model. Our queryable type has a single ProductCategory which has a single TitleContent which has an nvarchar field called BaseContent. With this in place, the error I get is:

{"A nested query relies on a correlation filter which refers to the field 'DefaultTitle', however this field wasn't found in the projection of the entity."}

If I do the same query as a .Select (instead of .GroupBy) then the results are perfect -- only 80% redundant. For now I might just do this the old fashioned way and pull back all 32,000 rows and then just write some code to boil it down to the results I want. However, I'd love to convert this to a single LINQ query if possible.

As always, we really appreciate the help!

morphman wrote:

Thanks for the reply! I've been working at this all morning otherwise I would have replied sooner.

That does indeed appear to work for the simple query I provided. Now for the plot to thicken... I have 2 additional complications to the query that both seem to present their own issues. I've boiled it down to the bare minimum to illustrate the issue below:

var results = queryable.GroupBy(x => new
            {
                x.ID,
                ChildIdsIncludingObsolete = x.SelfJoinedChildTable
                                             .Where(child => child.ISOLanguageCode == x.ISOLanguageCode)
                                             .Select(child => new
                                             {
                                                 child.ID,
                                                 child.IsObsolete
                                             })
                                             .Distinct(),
                DefaultTitle = x.ProductCategory.TitleContent.BaseContent
            }).ToList();

If with the ChildIdsIncludingObsolete field left in, I get the following error:

{"Unable to cast object of type 'System.Linq.Expressions.UnaryExpression' to type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SetExpression'."}

I'm guessing it has something to do with not being able to group when there is an IEnumerable in the key, but I'm really not sure.

The second problem comes from the DefaultTitle which I'm pulling back via some LLBLGen Pro relations we have set up in our model. Our queryable type has a single ProductCategory which has a single TitleContent which has an nvarchar field called BaseContent. With this in place, the error I get is:

{"A nested query relies on a correlation filter which refers to the field 'DefaultTitle', however this field wasn't found in the projection of the entity."}

If I do the same query as a .Select (instead of .GroupBy) then the results are perfect -- only 80% redundant. For now I might just do this the old fashioned way and pull back all 32,000 rows and then just write some code to boil it down to the results I want. However, I'd love to convert this to a single LINQ query if possible.

As always, we really appreciate the help!

Yes, in both cases you do additional things in the key specification, you can't do that: specify the set before the group by (e.g. through a join) and then specify a simply group by key. Groupby in linq is really complicated as I said: it doesn't map 1:1 to sql. The engine then has to try to convert it to something that makes sql produce the right sets (as there are always 2 sets in linq's groupby, not 1) and merge them. You can't do additional things there, like pull related sets, that's not going to work.

If you know the sql you want, you can also write a queryspec query instead and pull the entities or projection with that. Which is very likely much better than doing it in-memory, as you never know when someone will run that code a lot of times in the future and runs into a problem.

If you have a problem converting the sql to queryspec, we can help you with that.

Admittedly, I couldn't even think of a clean way to do do what I want in SQL due to the dynamic number of relations we pull back with each row. What you said makes sense -- we probably need to join to get the other data before doing the group by. If I get some more time I'll give that a shot.

In the meantime, below is the code I wrote to pull back all 30k+ results and then collapsed it down to what I needed in memory.

It isn't great -- but it is something that runs once a night in a batch job so I'm not terribly worried about it so long as it works.

Thanks again for schooling me on LINQ

Code?

Here is the code I ended up with:

var flatResults = queryable.Select(category => new
            {
                category.IsObsolete,
                category.ParentProductCategoryID,
                category.ID,
                ChildIdsIncludingObsolete = category.ChildTranslatedProductCategoryVWs
                                             .Where(child => child.ISOLanguageCode == category.ISOLanguageCode)
                                             .Select(child => new
                                             {
                                                 child.ID,
                                                 child.IsObsolete
                                             })
                                             .Distinct(),
                category.IsTTI,
                category.IsLicensed,
                category.ISOLanguageCode,
                category.ISOCountryCode,
                category.ShortTitle,
                category.SummaryDescription,
                category.DisplayOrder,
                DefaultTitle = category.ProductCategory.TitleContent.BaseContent
            }).ToList();

            var groupedResults = flatResults.GroupBy(groupedCategory => new
            {
                groupedCategory.IsObsolete,
                groupedCategory.ParentProductCategoryID,
                groupedCategory.ID,
                groupedCategory.IsTTI,
                groupedCategory.IsLicensed,
                groupedCategory.ISOLanguageCode,
                groupedCategory.ShortTitle,
                groupedCategory.SummaryDescription,
                groupedCategory.DisplayOrder,
                groupedCategory.DefaultTitle
            }).ToList();

            return groupedResults.Select(groupedResult => new CacheCategoryNodeWithAndWithoutObsoleteChildren
            {
                NodeWithObsoleteChildren = new CacheCategoryNode(groupedResult.Key.ParentProductCategoryID,
                                                                 groupedResult.Key.ID,
                    //--all keys in this group will have the same child ids, so we can do .First
                                                                 groupedResult.First().ChildIdsIncludingObsolete.Select(j => j.ID).Distinct().ToList(),
                                                                 groupedResult.Key.IsTTI,
                                                                 groupedResult.Key.IsLicensed,
                                                                 groupedResult.Key.ISOLanguageCode,
                                                                 groupedResult.Select(country => country.ISOCountryCode).ToList(),
                                                                 groupedResult.Key.ShortTitle,
                                                                 groupedResult.Key.SummaryDescription,
                                                                 groupedResult.Key.DisplayOrder,
                                                                 groupedResult.Key.DefaultTitle),
                //--don't bother with pulling back obsolete nodes when we don't want to include obsolete children as we have 0 cases that need this today
                NodeWithoutObsoleteChildren = groupedResult.Key.IsObsolete
                                                  ? null
                                                  : new CacheCategoryNode(groupedResult.Key.ParentProductCategoryID,
                                                                          groupedResult.Key.ID,
                                                                          groupedResult.First().ChildIdsIncludingObsolete.Where(j => !j.IsObsolete).Distinct().Select(j => j.ID).ToList(),
                                                                          groupedResult.Key.IsTTI,
                                                                          groupedResult.Key.IsLicensed,
                                                                          groupedResult.Key.ISOLanguageCode,
                                                                          groupedResult.Select(country => country.ISOCountryCode).ToList(),
                                                                          groupedResult.Key.ShortTitle,
                                                                          groupedResult.Key.SummaryDescription,
                                                                          groupedResult.Key.DisplayOrder,
                                                                          groupedResult.Key.DefaultTitle)
            }).ToList(); 

The thing is that you want to group set A on data not in set A but read in the key definition. That's the core issue I think. If the data you pull into the key definition is already in set A you can group on it.

So you combine two things which are not really combineable. In memory it works because it can execute the code as it is formulated, but with a database, as said earlier, it needs to be converted to SQL, which isn't possible for the combined query.

Hopefully this will stay usable in the future.

Yeah, I'm sure this will be a real pleasure 3 years from now when another developer comes in to check this out

I think we are good to go for now. Thanks for your help!