相交多个记录 [英] intersect multple records

问题描述

情况
我试图找到一种对表A进行选择的方法，其中在表b的每种情况下都存在该记录.这是我见过的最糟糕的描述，但是我不知道如何一齐澄清，所以剩下的就是这里.

在网站上，有人选择链接到交付方式A1，A2和A3的产品B1.然后，他们选择另一个产品B2，该产品链接到A1，A2和A4.传递方法类型的最终列表将仅包含方法A1和A2，因为这两个表中都不存在其他方法.

我知道我可以做到这一点.但是，当有数十条A记录和数十条B记录，并且全部相互链接并且用户可以选择多个(而不是2个)B记录时，这将变得更加困难.
我拥有的SQL取决于对返回每个记录的次数进行计数，并确保该计数等于所选产品的数量:

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:

    --@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

问题
在linq中有一种简单的方法可以执行此操作吗?


PS :(这项工作的性质是机密的，实际上不是产品和交付方式，但这种分析似乎可以正常工作).

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)

推荐答案

我自己已经解决了这个问题.这一切都是为了理解GroupBy扩展:

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

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

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

这篇关于相交多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！