Click here to Skip to main content
15,917,971 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hi,

Situation
I am trying to find a way to perform a select on table A where the record exists in every case of table b. That is the worst description I have ever seen but I don't know how to clarify it in one line, so here's the rest.

On the website, someone select product B1 which is linked to delivery method A1, A2 and A3. They then select another product, B2, which is linked to A1, A2 and A4. The final list of delivery method types would consist of only methos A1 and A2 because the others do not exist in both tables.

i know I can do this will a .Intersect but it becomes more difficult when there are dozens of A records and dozens on B records, all interlinked and the user can select multiple (not just 2) B records.

The SQL I have depends on counting the number of times each record is returned and ensuring that count is equal to the number of products selected:
SQL
    --@IdsTable = the productIds (Table B)
select @DesiredNumber = COUNT(distinct id) from @IdsTable

select distinct a.name, a.id
from
    productdeliverymethod ab --link table
    inner join deliverymethod a on (a.id = ab.deliverymethod_id)
    inner join @IdsTable b on b.id = ab.product_id
group by a.name, a.id
having count(distinct ab.id) = @DesiredNumber --ensure match all
order by
    a.name asc

Question
Is there a simple way of performing this in linq?


PS: (The nature of the work is confidential and is not actually products and delivery methods but the analagy seems to work ok)
Posted
Comments
sunandandutt 16-May-12 6:18am    
Its very simle and less time consuming in linq.

1 solution

I have solved this one myself. It's all about understanding the GroupBy Extension:

C#
public static IQueryable<deliverymethod> QueryByProductIds(
  int[] productIds,
  DataContext1 db)
{
  return Query(db)
    .Join(
      productdeliverymethods.QueryByProductIds(productIds, db),
      a => a.deliverymethod_id,
      ab => ab.deliverymethod_id,
      (a, ab) => a)
    .GroupBy(
      a => a,
      (key, g) => new
        {
          Key = key,
          Count = g.Count()
        })
    .Where(g => g.Count == productIds.Length)
    .Select(g => g.Key);
</deliverymethod>



In fact, it worked so well that I have made an extention method

C#
public static IQueryable IntersectAll<t>(this IQueryable<t> query, int count)
{
  return query
           .GroupBy(
              q => q,
              (key, g) => new
              {
                Key = key,
                Count = g.Count()
              })
            .Where(g => g.Count == count)
            .Select(g => g.Key);
}
/////////////////////////////////

public static IQueryable<deliverymethod> QueryByProductIds(
  int[] productIds,
  DataContext1 db)
{
  return Query(db)
    .Join(
      productdeliverymethods.QueryByProductIds(productIds, db),
      a => a.deliverymethod_id,
      ab => ab.deliverymethod_id,
      (a, ab) => a)
    .IntersectAll(productIds.Length);
</deliverymethod></t></t>


Hope someone finds this useful. I am quite proud ^_^
 
Share this answer
 
Comments
Maciej Los 16-May-12 9:38am    
Good work, Andy! My 5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900