How to get ProductIds by multi-property search in sql or Linq To Sql?

There are two tables: Product, ProductProperty

ProductProperty is a table that stores the InfoKey and the InfoValue of the products.

The SQL sample script is:

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

Sample data:

[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

I want to get the productIds where the product's ProductProperty k1=v1 and k2=v1 something like this:

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

The result should be:

ProductId
1
2

How can I do this in Sql or LinqToSql?

If the number of property conditions you are checking is static (in this case, two) then you can achieve this query like below. If not, please post back and let us know if you need it to be more dynamic (to support three, four, etc. conditions)

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

EDIT: for dynamic condition count

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

The query would look like this:

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;

With this particular condition it can be simplified like this:

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;

If conditions are dynamic:

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