CODEWITHSUNDEEP
c#linqentity-framework
Patrick
Patrick

Reputation: 2781

Group by a many-to-many relashionship

I have two entities:

public class Category
{
    // Primary properties
    public int Id { get; set; }
    public string Name { get; set; }

    // Navigation properties
    public virtual ICollection<Address> Addresses { get; set; }
}

public class Address
{
    // Primary properties
    public int Id { get; set; }
    public string Name { get; set; }

    // Navigation properties
    public virtual ICollection<Category> Categories { get; set; }
}

Based on a set of Address Id's, I need to Group By all the addresses by Category, including the Address Count for each Address.

Example:

Category: {1,"WORKSHOP",{1,2}},{2,"DEALER",{1,3}}

Address:  {1,"Paul Workshop and Dealer",{1,2}}
          {2,"Joe Workshop",{1}} 
          {3,"Peter Dealer",{2}}

If I have the Address Id's 1 and 3, I want to get: 

Categories - "WORKSHOP -  Count: 1"
             "DEALER   -  Count: 2"

If I have the Address Id's 1 and 2, I want to get: Category - 

Categories - "WORKSHOP -  Count: 2"
             "DEALER   -  Count: 1"

So far I get this, but the group by is not working:

var groupedAddresses = from add in addressQuery
                       where addressIds.Contains(add.Id)
                       group add by new { Category_Id = add.Categories, Address_Id = add.Id };

var result = from add in groupedAddresses 
             group add by add.Id into final
             join c in categoryQuery on final.Key equals c.Id
             select new CategoryGetAllBySearchDto
             {
                   Id = final.Key,
                   Name = c.Name,
                   SearchCount = final.Count()
              };

Any idea?

Thanks.

Upvotes: 2

Views: 90

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

int[] addressIds = { 1, 3 };

var query = from c in categoryQuery
            let searchCount = c.Addresses.Count(a => addressIds.Contains(a.Id))
            where searchCount > 0
            select new CategoryGetAllBySearchDto{
               Id = c.Id,
               Name = c.Name,
               SearchCount = searchCount
            };

Upvotes: 2

Related Questions