intersect multple records

Question

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 16-May-12 0:15am

Andy Lanng

Add a Solution

Comments

sunandandutt 16-May-12 6:18am

Its very simle and less time consuming in linq.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Andy Lanng · Accepted Answer · 2012-05-16T02:45:00

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 ^_^