Key-value querying

Query.sql

-- C# Predicate: item.SortOrder == 2 || (item.SortOrder == 1 && item.StringValue == "B").Take(2).SortBy(item => item.StringValue)


-- Existing rows
-- {"StructureId":"208fad84b608e111b21c544249037e42","SortOrder":2,"StringValue":"D"}
-- {"StructureId":"218fad84b608e111b21c544249037e42","SortOrder":2,"StringValue":"C"}
-- {"StructureId":"228fad84b608e111b21c544249037e42","SortOrder":1,"StringValue":"B"}
-- {"StructureId":"238fad84b608e111b21c544249037e42","SortOrder":1,"StringValue":"A"}


-- Desired outcome
-- {"StructureId":"228fad84b608e111b21c544249037e42","SortOrder":1,"StringValue":"B"}
-- {"StructureId":"218fad84b608e111b21c544249037e42","SortOrder":2,"StringValue":"C"}


-- Failing SQL for Sql2008, SqlCe has limited support giving other syntax, but first things first and that is SQL2008
select top(2) s.Json
from [QueryGuidItemStructure] as s 
inner join [QueryGuidItemIndexes] as si on si.[StructureId] = s.[StructureId] 
where 
(
	(si.[MemberPath]='SortOrder' and si.[IntegerValue] = 2)
	or 
	(
		(si.[MemberPath]='SortOrder' and si.[IntegerValue] = 1)
		and
		(si.[MemberPath]='StringValue' and si.[StringValue] = 'B')
	)
)
group by s.[StructureId], s.[Json]
order by min(si.[StringValue]) Asc;

Setup database.sql

USE [SisoDbSpecs]
GO
/****** Object:  Table [dbo].[QueryGuidItemStructure]    Script Date: 11/06/2011 21:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QueryGuidItemStructure](
	[StructureId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[Json] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_QueryGuidItemStructure] PRIMARY KEY CLUSTERED 
(
	[StructureId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[QueryGuidItemStructure] ([StructureId], [Json]) VALUES (N'2b0a73d9-6908-e111-b21c-544249037e42', N'{"StructureId":"2b0a73d96908e111b21c544249037e42","SortOrder":2,"StringValue":"D"}')
INSERT [dbo].[QueryGuidItemStructure] ([StructureId], [Json]) VALUES (N'2c0a73d9-6908-e111-b21c-544249037e42', N'{"StructureId":"2c0a73d96908e111b21c544249037e42","SortOrder":2,"StringValue":"C"}')
INSERT [dbo].[QueryGuidItemStructure] ([StructureId], [Json]) VALUES (N'2d0a73d9-6908-e111-b21c-544249037e42', N'{"StructureId":"2d0a73d96908e111b21c544249037e42","SortOrder":1,"StringValue":"B"}')
INSERT [dbo].[QueryGuidItemStructure] ([StructureId], [Json]) VALUES (N'2e0a73d9-6908-e111-b21c-544249037e42', N'{"StructureId":"2e0a73d96908e111b21c544249037e42","SortOrder":1,"StringValue":"A"}')
/****** Object:  Table [dbo].[SisoDbIdentities]    Script Date: 11/06/2011 21:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SisoDbIdentities](
	[EntityHash] [varchar](8) NOT NULL,
	[EntityName] [varchar](100) NOT NULL,
	[CurrentId] [bigint] NOT NULL,
 CONSTRAINT [PK_SisoDbIdentities] PRIMARY KEY CLUSTERED 
(
	[EntityHash] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[QueryGuidItemUniques]    Script Date: 11/06/2011 21:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QueryGuidItemUniques](
	[StructureId] [uniqueidentifier] NOT NULL,
	[UqStructureId] [uniqueidentifier] NULL,
	[UqMemberPath] [varchar](250) NOT NULL,
	[UqValue] [nvarchar](10) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[QueryGuidItemIndexes]    Script Date: 11/06/2011 21:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QueryGuidItemIndexes](
	[RowId] [bigint] IDENTITY(1,1) NOT NULL,
	[StructureId] [uniqueidentifier] NOT NULL,
	[MemberPath] [varchar](250) NOT NULL,
	[StringValue] [nvarchar](max) NULL,
	[IntegerValue] [bigint] NULL,
	[FractalValue] [real] NULL,
	[DateTimeValue] [datetime] NULL,
	[BoolValue] [bit] NULL,
	[GuidValue] [uniqueidentifier] NULL,
 CONSTRAINT [PK_QueryGuidItemIndexes] PRIMARY KEY CLUSTERED 
(
	[RowId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[QueryGuidItemIndexes] ON
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (1, N'2b0a73d9-6908-e111-b21c-544249037e42', N'SortOrder', NULL, 2, NULL, NULL, NULL, NULL)
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (2, N'2b0a73d9-6908-e111-b21c-544249037e42', N'StringValue', N'D', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (3, N'2c0a73d9-6908-e111-b21c-544249037e42', N'SortOrder', NULL, 2, NULL, NULL, NULL, NULL)
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (4, N'2c0a73d9-6908-e111-b21c-544249037e42', N'StringValue', N'C', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (5, N'2d0a73d9-6908-e111-b21c-544249037e42', N'SortOrder', NULL, 1, NULL, NULL, NULL, NULL)
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (6, N'2d0a73d9-6908-e111-b21c-544249037e42', N'StringValue', N'B', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (7, N'2e0a73d9-6908-e111-b21c-544249037e42', N'SortOrder', NULL, 1, NULL, NULL, NULL, NULL)
INSERT [dbo].[QueryGuidItemIndexes] ([RowId], [StructureId], [MemberPath], [StringValue], [IntegerValue], [FractalValue], [DateTimeValue], [BoolValue], [GuidValue]) VALUES (8, N'2e0a73d9-6908-e111-b21c-544249037e42', N'StringValue', N'A', NULL, NULL, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[QueryGuidItemIndexes] OFF
/****** Object:  Default [DF__SisoDbIde__Curre__7F60ED59]    Script Date: 11/06/2011 21:30:59 ******/
ALTER TABLE [dbo].[SisoDbIdentities] ADD  DEFAULT ((0)) FOR [CurrentId]
GO
/****** Object:  ForeignKey [FK_QueryGuidItemIndexes_QueryGuidItemStructure]    Script Date: 11/06/2011 21:30:59 ******/
ALTER TABLE [dbo].[QueryGuidItemIndexes]  WITH NOCHECK ADD  CONSTRAINT [FK_QueryGuidItemIndexes_QueryGuidItemStructure] FOREIGN KEY([StructureId])
REFERENCES [dbo].[QueryGuidItemStructure] ([StructureId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[QueryGuidItemIndexes] CHECK CONSTRAINT [FK_QueryGuidItemIndexes_QueryGuidItemStructure]
GO
/****** Object:  ForeignKey [FK_QueryGuidItemUniques_QueryGuidItemStructure]    Script Date: 11/06/2011 21:30:59 ******/
ALTER TABLE [dbo].[QueryGuidItemUniques]  WITH NOCHECK ADD  CONSTRAINT [FK_QueryGuidItemUniques_QueryGuidItemStructure] FOREIGN KEY([StructureId])
REFERENCES [dbo].[QueryGuidItemStructure] ([StructureId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[QueryGuidItemUniques] CHECK CONSTRAINT [FK_QueryGuidItemUniques_QueryGuidItemStructure]
GO

VICTORY!!!! This is done to take into count that MemberPath 'asdfasdfasdf' with a StringValue of 'A' could affect sorting.

select top(2) s.json, min(si.stringvalue) sort0, min(si.integervalue) sort1 from QueryGuidItemIndexes si
inner join 
(
    select s.StructureId
    from [QueryGuidItemStructure] as s 
    left join [QueryGuidItemIndexes] as crit0 on crit0.[StructureId] = s.[StructureId] and (crit0.[MemberPath]='SortOrder' and crit0.[IntegerValue] = 2)
    left join [QueryGuidItemIndexes] as crit1 on crit1.[StructureId] = s.[StructureId] and (crit1.[MemberPath]='SortOrder' and crit1.[IntegerValue] = 1)
    left join [QueryGuidItemIndexes] as crit2 on crit2.[StructureId] = s.[StructureId] and (crit2.[MemberPath]='StringValue' and crit2.StringValue = 'B')
    where
    crit0.StructureId is not null
    or
    (
        crit1.StructureId is not null
        and
        crit2.StructureId is not null
    )
)sids on si.StructureId = sids.StructureId and si.MemberPath in('SortOrder', 'StringValue')
inner join QueryGuidItemStructure s on s.StructureId = sids.StructureId
group by si.StructureId, s.Json
order by sort0, sort1

Sorry, but it should be enough with this

select s.json from [QueryGuidItemStructure] s
left join [QueryGuidItemIndexes] as crit0 on crit0.[StructureId] = s.[StructureId] and (crit0.[MemberPath]='SortOrder' and crit0.[IntegerValue] = 2)
left join [QueryGuidItemIndexes] as crit1 on crit1.[StructureId] = s.[StructureId] and (crit1.[MemberPath]='SortOrder' and crit1.[IntegerValue] = 1)
left join [QueryGuidItemIndexes] as crit2 on crit2.[StructureId] = s.[StructureId] and (crit2.[MemberPath]='StringValue' and crit2.StringValue = 'B')
inner join [QueryGuidItemIndexes] as si on si.StructureId = s.StructureId and si.MemberPath in ('SortOrder', 'StringValue')
where
crit0.StructureId is not null
or
(
    crit1.StructureId is not null
    and
    crit2.StructureId is not null
)
group by s.StructureId, s.Json
order by min(si.stringvalue), min(si.integervalue)

select top 2 s.json from [QueryGuidItemStructure] s
left join [QueryGuidItemIndexes] as mem0 on mem0.[StructureId] = s.[StructureId] and mem0.[MemberPath]='SortOrder' 
left join [QueryGuidItemIndexes] as mem1 on mem1.[StructureId] = s.[StructureId] and mem1.[MemberPath]='SortOrder'
left join [QueryGuidItemIndexes] as mem2 on mem2.[StructureId] = s.[StructureId] and mem2.[MemberPath]='StringValue'
inner join [QueryGuidItemIndexes] as si on si.StructureId = s.StructureId and si.MemberPath in ('SortOrder', 'StringValue')
where
(mem0.[IntegerValue] = 2)
or
((mem1.[IntegerValue] = 1) and (mem2.StringValue = 'B'))
group by s.StructureId, s.Json
order by min(si.stringvalue), min(si.integervalue)

select top 2 min(s.json) from [QueryGuidItemStructure] s
inner join [QueryGuidItemIndexes] as si on si.StructureId = s.StructureId
inner join [QueryGuidItemIndexes] as mem0 on mem0.[StructureId] = s.[StructureId] and mem0.[MemberPath]='SortOrder'
inner join [QueryGuidItemIndexes] as mem2 on mem2.[StructureId] = s.[StructureId] and mem2.[MemberPath]='StringValue'
where
(mem0.[IntegerValue] = 2)
or
((mem0.[IntegerValue] = 1) and (mem2.StringValue = 'B'))
group by s.StructureId
order by min(mem2.stringvalue), min(mem0.integervalue)