如何在sql或Linq to sql中通过多属性搜索来获取ProductId

本文关键字：sql 属性 搜索 获取 ProductId Linq to | 更新日期: 2023-09-27 18:00:22

有两个表：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

我想得到productId，其中产品的ProductProperty k1=v1和k2=v1类似于以下内容：

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

结果应该是：

ProductId
1
2

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

如何在sql或Linq to sql中通过多属性搜索来获取ProductId

如果您正在检查的属性条件的数量是静态的（在本例中为两个），那么您可以实现如下查询。如果没有，请回复并让我们知道你是否需要它更动态（支持三、四等条件）

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

EDIT：用于动态条件计数

;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);

查询如下所示：

var q = 
    from product in db.Product
    where
        db.ProductProperty.Any(arg => arg.ProductId = product.Id && arg.InfoKey = "k1" && arg.InfoValue = "v1") &&
        db.ProductProperty.Any(arg => arg.ProductId = product.Id && arg.InfoKey = "k2" && arg.InfoValue = "v1")
    select product.Id;

有了这个特殊的条件，它可以简化为这样：

var q =
    from product in db.Product
    let properties = db.ProductProperty.Where(arg => arg.ProductId = product.Id && arg.InfoValue = "v1")
    where
        properties.Any(arg => arg.InfoKey = "k1") &&
        properties.Any(arg => arg.InfoKey = "k2")
    select product.Id;

如果条件是动态的：

var q =
    from product in db.Product
    select
        new
        {
            Product = product,
            Properties = db.ProductProperty.Where(arg => arg.ProductId = product.Id)
        };
if (something1)
    q = q.Where(arg => arg.InfoKey = "k1" && arg.InfoValue = "v1");
if (something2)
    q = q.Where(arg => arg.InfoKey = "k2" && arg.InfoValue = "v1");
var result = q.Select(arg => new { arg.Product.Id, arg.Product.Name }).ToList();
相关文章：