CODEWITHSUNDEEP
c#sqllinq
Jonathan Wood
Jonathan Wood

Reputation: 67283

The nested query is not supported

I have a query that is similar to the following (my actual query has three sections like this and then Concats them together and applies some additional filters and sorting).

var articles = from p in Repository.Query<Product>()
               let article = p.Article
               let office = p.TariffCategory.Office
               where p.IsDeleted == false
               select new
               {
                   OfficeId = office.Id,
                   Office = office.Name,
                   Category = p.TariffCategory.Description,
                   ArticleId = article.Id,
                   Article = article.Title,
                   Destinations = p.ProductDestinations.Select(d => new { Id = d.DestinationId, Name = d.Destination.Description }),
                   GlobalDestinations = p.AllDestinationsInOffice,
                   article.LastReviewedDate,
                   article.CreatedDate,
                   article.CreatedByEmployee
               };

Everythings seems right except my assignment to Destinations. That line produces the following error.

The nested query is not supported. Operation1='UnionAll' Operation2='MultiStreamNest'

If I remove that line, everything works as expected. Is there any way to perform a query like this?

Upvotes: 1

Views: 2623

Answers (1)

Rob
Rob

Reputation: 27367

I had a bit of a think, and rather than doing a join as I suggested, it may make sense to start the query at ProductDestination. What we're interested in is a row for each product+destination combination, much like you'd see via regular SQL queries. Once we've got that, we can apply grouping to the result so that we're closer to the representation you had

var data = Repository.Query<ProductDestination>()
    .Where(pd => !pd.Product.IsDeleted)
    .Select(pd => 
    new {   
       Product = pd.Product,
       Destination = pd.Destination,
    })
    .GroupBy(pd => pd.Product)
    //I'm not in a position to test if EF will successfully run the below, so .ToList()
    //May not be required. However, the bulk of the work is done in the database, anyway.
    //.ToList()
    .Select(g => new {
        OfficeId = g.Key.TariffCategory.Office.Id,
        Office = g.Key.TariffCategory.Office.Name,
        Category = g.Key.TariffCategory.Description,
        ArticleId = g.Key.Article.Id,
        Article = g.Key.Article.Title,
        Destinations = g.Select(gg => new { Id = gg.Destination.DestinationId, Name = gg.Destination.Description }),
        GlobalDestinations = g.Key.AllDestinationsInOffice,
        g.Key.Article.LastReviewedDate,
        g.Key.Article.CreatedDate,
        g.Key.Article.CreatedByEmployee
    });

I'm pretty sure the above should work without the ToList(), but I'm not confident to say it 100% will work. However, as noted, the bulk of the work is done in the database, the final projection shouldn't be too intensive, even if it's done in memory. However, should the ToList() be required, we would need to modify the GroupBy to return all fields we select via Key, otherwise we're going to have issues with lazy loading and N+1 queries.

Upvotes: 1

Related Questions