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();
精彩评论