c# - 如何通过sql或Linq To Sql中的多属性搜索获取ProductIds？

Question

有两个表:Product，ProductProperty

ProductProperty 是存储产品的 InfoKey 和 InfoValue 的表。

SQL 示例脚本是:

CREATE TABLE [dbo].[ProductProperty](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [InfoKey] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [InfoValue] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
 CONSTRAINT [PK_ProductProperty] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)

GO

SET IDENTITY_INSERT [dbo].[ProductProperty] ON
INSERT [dbo].[ProductProperty] ([Id], [ProductId], [InfoKey], [InfoValue]) VALUES (1, 1, N'k1', N'v1')
INSERT [dbo].[ProductProperty] ([Id], [ProductId], [InfoKey], [InfoValue]) VALUES (3, 2, N'k1', N'v1')
INSERT [dbo].[ProductProperty] ([Id], [ProductId], [InfoKey], [InfoValue]) VALUES (4, 3, N'k1', N'v2')
INSERT [dbo].[ProductProperty] ([Id], [ProductId], [InfoKey], [InfoValue]) VALUES (6, 5, N'k1', N'v1')
INSERT [dbo].[ProductProperty] ([Id], [ProductId], [InfoKey], [InfoValue]) VALUES (7, 2, N'k2', N'v1')
INSERT [dbo].[ProductProperty] ([Id], [ProductId], [InfoKey], [InfoValue]) VALUES (8, 1, N'k2', N'v1')
INSERT [dbo].[ProductProperty] ([Id], [ProductId], [InfoKey], [InfoValue]) VALUES (9, 5, N'k2', N'v2')
INSERT [dbo].[ProductProperty] ([Id], [ProductId], [InfoKey], [InfoValue]) VALUES (10, 2, N'k3', N'v2')
INSERT [dbo].[ProductProperty] ([Id], [ProductId], [InfoKey], [InfoValue]) VALUES (11, 3, N'k3', N'v2')
INSERT [dbo].[ProductProperty] ([Id], [ProductId], [InfoKey], [InfoValue]) VALUES (12, 5, N'k3', N'v1')
SET IDENTITY_INSERT [dbo].[ProductProperty] OFF

GO

CREATE TABLE [dbo].[Product](
    [ProductId] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
    [ProductId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)

GO

SET IDENTITY_INSERT [dbo].[Product] ON
INSERT [dbo].[Product] ([ProductId], [ProductName]) VALUES (1, N'Product_A1')
INSERT [dbo].[Product] ([ProductId], [ProductName]) VALUES (2, N'Product_A2')
INSERT [dbo].[Product] ([ProductId], [ProductName]) VALUES (3, N'Product_B1')
INSERT [dbo].[Product] ([ProductId], [ProductName]) VALUES (4, N'Product_B2')
INSERT [dbo].[Product] ([ProductId], [ProductName]) VALUES (5, N'Product_B3')
SET IDENTITY_INSERT [dbo].[Product] OFF

GO

示例数据:

[Product Table]

ProductId ProductName

1   Product_A1
2   Product_A2
3   Product_B1
4   Product_B2
5   Product_B3


[ProductProperty  Table]

Id ProductId InfoKey InfoValue

1   1   k1  v1
3   2   k1  v1
4   3   k1  v2
6   5   k1  v1
7   2   k2  v1
8   1   k2  v1
9   5   k2  v2
10  2   k3  v2
11  3   k3  v2
12  5   k3  v1

我想获取产品的 ProductProperty k1=v1 和 k2=v1 的 productIds，如下所示:

(InfoKey='k1' AND InfoValue='v1') and (InfoKey='k2' AND InfoValue='v1')

结果应该是:

ProductId
1
2

我如何在 Sql 或 LinqToSql 中执行此操作？

Answer 1

如果您要检查的属性条件的数量是静态的(在本例中为两个)，那么您可以像下面这样实现此查询。如果没有，请回贴并让我们知道您是否需要它更加动态(以支持三、四等条件)

select  ProductId
from    dbo.productproperty
where   (InfoKey='k1' AND InfoValue='v1') or 
        (InfoKey='k2' AND InfoValue='v1')
group 
by      ProductId 
having  count(*) = 2;

编辑:动态条件计数

;with c_stage (k, v)
as  (   select 'k1', 'v1' union all
        select 'k2', 'v1'
    )
select  ProductId
from    dbo.productproperty pp
join    c_stage t on 
        pp.InfoKey = t.k and 
        pp.InfoValue = t.v
group 
by      ProductId 
having  count(*) = (select count(*) from c_stage);