CODEWITHSUNDEEP
c#sqllinq
chris c
chris c

Reputation: 331

Linq grouping by item count from joined table

I had a look through a bunch of answers, and tried to get the code working but couldn't see a way to group by the _productBundlesItemsRepository table

Here is the query I'm looking at

return _productBundlesRepository.Table.Join(_productBundleItemsRepository.Table,
                    pbr => pbr.Id,
                    pbir => pbir.BundleId,
                    (pbr, pbir) => new { Bundle = pbr, BundleItem = pbir})
                .Where(x => x.BundleItem.ProductId == productId && x.BundleItem.Visible == true && x.Bundle.Active == true).Select(x => x.Bundle).ToList();

I thought maybe I could do something like this .OrderByDescending(x => x.BundleItem.Count()) but that option is not available

I also tried something like grabbing the count of items linked to the bundle in the new statement like this

new { Bundle = pbr, BundleItem = pbir, Count = GetProductBundleItems(pbr.Id).Count})

But then I get an error like this

System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Collections.Generic.List

Not really sure how I go about ordering the bundles returned by the number of bundle items contained within the bundle.

UPDATE I used some modified code based on the answer from @StriplingWarrior but it appears this will not work, as multiple bundles can contain the same item. ie one bundle is a 2 pack, and another bundle is a 4 pack both use the same item. Here is my updated code

var bundleItems = _productBundleItemsRepository.Table
                .Where(bundleItem => bundleItem.ProductId == productId
                                     && bundleItem.Visible == true);
            return _productBundlesRepository.Table
                .Select(bundle => new BundleWithCount { pb = bundle, count = bundleItems.Count(bundleItem => bundleItem.BundleId == bundle.Id) })
                .Where(x => x.pb.Active == true && x.count > 0).OrderByDescending(x => x.count).Select(x => x.pb)
                .ToList();

with

internal class BundleWithCount
    {
        public int count { get; set; }

        public ProductBundle pb { get; set; }
    }

Anyone see where I might be going wrong?

Thanks

Upvotes: 0

Views: 41

Answers (2)

chris c
chris c

Reputation: 331

I managed to work this out, with the number of items ordered first then the item quantity. Just how I planned to do it. Thanks @StriplingWarrior for pointing me in the right direction :)

public List<ProductBundle> GetBundlesActive()
        {
            var bundleItems = _productBundleItemsRepository.Table
                .Where(bundleItem => bundleItem.Visible == true);
            var bundleItemCount = _productBundleItemsRepository.Table
                .Where(bundleItem => bundleItem.Visible == true);
            return _productBundlesRepository.Table
                .Select(bundle => new BundleWithCount { Pb = bundle, Count = bundleItems.Count(bundleItem => bundleItem.BundleId == bundle.Id), ItemCount = bundleItemCount.FirstOrDefault(bundleItem => bundleItem.BundleId == bundle.Id).Quantity })
                .Where(x => x.Pb.Active == true && x.Count > 0).OrderByDescending(x => x.Count).ThenByDescending(x => x.ItemCount).Select(x => x.Pb)
                .ToList();
        }

and internal class of this

internal class BundleWithCount
    {
        public int Count { get; set; }

        public int ItemCount { get; set; }

        public ProductBundle Pb { get; set; }
    }

Upvotes: 0

StriplingWarrior
StriplingWarrior

Reputation: 156524

A Join isn't really great when you're not wanting get the scalar data from both tables. Something like this would be more effective for just getting the Bundles.

return _productBundlesRepository.Table
    .Where(bundle => bundle.Active == true
        && _productBundleItemsRepository.Table
            .Any(bundleItem => bundleItem.BundleId == bundle.Id
                 && bundleItem.ProductId == productId 
                 && bundleItem.Visible == true))
    .ToList();

Or, put another way:

var bundleItems = _productBundleItemsRepository.Table
    .Where(bundleItem => bundleItem.ProductId == productId 
        && bundleItem.Visible == true);
return _productBundlesRepository.Table
    .Where(bundle => bundle.Active == true
        && bundleItems.Any(bundleItem => bundleItem.BundleId == bundle.Id))
    .ToList();

Then, if you want the count of bundle items per bundle:

var bundleItems = _productBundleItemsRepository.Table
    .Where(bundleItem => bundleItem.ProductId == productId 
        && bundleItem.Visible == true);
return _productBundlesRepository.Table
    .Select(bundle => new BundleWithCount {bundle, count=bundleItems.Count(bundleItem => bundleItem.BundleId == bundle.Id)})
    .Where(x => x.bundle.Active == true && x.count > 0)
    .ToList();

(You'll need to define the BundleWithCount class so you can return a generic based on objects of that type)

Upvotes: 1

Related Questions