public void GivenPatientNameFilterForExtendedQueryTag_WithFuzzyMatchMultiWord_ValidateContainsFilterGenerated()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(new List <DicomTag>());
var queryTag = new QueryTag(DicomTag.ConsultingPhysicianName.BuildExtendedQueryTagStoreEntry(level: QueryTagLevel.Series));
var filter = new PersonNameFuzzyMatchCondition(queryTag, "Fall 6");
filter.QueryTag = queryTag;
var filters = new List <QueryFilterCondition>()
{
filter,
};
var query = new QueryExpression(QueryResource.AllInstances, includeField, true, 10, 0, filters, Array.Empty <string>());
SqlParameterCollection sqlParameterCollection = CreateSqlParameterCollection();
var parm = new SqlQueryParameterManager(sqlParameterCollection);
new SqlQueryGenerator(stringBuilder, query, parm, SqlServer.Features.Schema.SchemaVersion.V4, DefaultPartition.Key);
string expectedParam = $"\"Fall 6*\"";
string expectedFilters = @"AND ctpn1.TagKey=@p0
AND CONTAINS(ctpn1.TagValueWords, @p1)";
Assert.Equal(queryTag.ExtendedQueryTagStoreEntry.Key.ToString(), sqlParameterCollection[0].Value.ToString());
Assert.Equal(expectedParam, sqlParameterCollection[1].Value.ToString());
Assert.Contains(expectedFilters, stringBuilder.ToString());
}
public void GivenStringExtendedQueryTagFilter_WhenIELevelStudy_ValidateExtendedQueryTagFilter()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(false, new List <DicomTag>());
var queryTag = new QueryTag(DicomTag.ModelGroupUID.BuildExtendedQueryTagStoreEntry(level: QueryTagLevel.Study));
var filter = new StringSingleValueMatchCondition(queryTag, "123");
filter.QueryTag = queryTag;
var filters = new List <QueryFilterCondition>()
{
filter,
};
var query = new QueryExpression(QueryResource.AllStudies, includeField, false, 0, 0, filters);
SqlParameterCollection sqlParameterCollection = CreateSqlParameterCollection();
var parm = new SqlQueryParameterManager(sqlParameterCollection);
new SqlQueryGenerator(stringBuilder, query, parm);
string expectedExtendedQueryTagTableFilter = @"INNER JOIN dbo.ExtendedQueryTagString cts1
ON cts1.StudyKey = st.StudyKey
WHERE";
string expectedFilters = @"AND cts1.TagKey=@p0
AND cts1.TagValue=@p1";
string builtString = stringBuilder.ToString();
Assert.Equal(queryTag.ExtendedQueryTagStoreEntry.Key.ToString(), sqlParameterCollection[0].Value.ToString());
Assert.Equal(filter.Value.ToString(), sqlParameterCollection[1].Value.ToString());
Assert.Contains(expectedExtendedQueryTagTableFilter, builtString);
Assert.Contains(expectedFilters, builtString);
}
public void VersionGTE6_GivenStudyDate_WhenIELevelStudy_ValidatePartitionKeySpecified()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(new List <DicomTag>());
var minDate = new DateTime(2020, 2, 1);
var maxDate = new DateTime(2020, 3, 1);
var filters = new List <QueryFilterCondition>()
{
new DateRangeValueMatchCondition(new QueryTag(DicomTag.StudyDate), minDate, maxDate),
};
var query = new QueryExpression(QueryResource.AllStudies, includeField, false, 0, 0, filters, Array.Empty <string>());
var parm = new SqlQueryParameterManager(CreateSqlParameterCollection());
new SqlQueryGenerator(stringBuilder, query, parm, SqlServer.Features.Schema.SchemaVersion.V6, DefaultPartition.Key);
string expectedDistinctSelect = @"SELECT
st.StudyKey
FROM dbo.Study st
WHERE 1 = 1
AND st.PartitionKey = 1";
Assert.Contains(expectedDistinctSelect, stringBuilder.ToString());
}
public void GivenDateTimeExtendedQueryTagFilter_WhenIELevelStudy_ValidateExtendedQueryTagFilter()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(new List <DicomTag>());
var queryTag = new QueryTag(DicomTag.DateTime.BuildExtendedQueryTagStoreEntry(level: QueryTagLevel.Study));
var filter = new DateRangeValueMatchCondition(queryTag, DateTime.ParseExact("19510910111213.123", QueryParser.DateTimeTagValueFormats, null), DateTime.ParseExact("19571110111213.123", QueryParser.DateTimeTagValueFormats, null));
filter.QueryTag = queryTag;
var filters = new List <QueryFilterCondition>()
{
filter,
};
var query = new QueryExpression(QueryResource.AllStudies, includeField, false, 0, 0, filters, Array.Empty <string>());
SqlParameterCollection sqlParameterCollection = CreateSqlParameterCollection();
var parm = new SqlQueryParameterManager(sqlParameterCollection);
new SqlQueryGenerator(stringBuilder, query, parm, SqlServer.Features.Schema.SchemaVersion.V4, DefaultPartition.Key);
string expectedExtendedQueryTagTableFilter = @"INNER JOIN dbo.ExtendedQueryTagDateTime ctdt1
ON ctdt1.PartitionKey = st.PartitionKey
AND ctdt1.StudyKey = st.StudyKey
WHERE";
string expectedFilters = @"AND ctdt1.TagKey=@p0
AND ctdt1.TagValue BETWEEN @p1 AND @p2";
string builtString = stringBuilder.ToString();
Assert.Equal(queryTag.ExtendedQueryTagStoreEntry.Key.ToString(), sqlParameterCollection[0].Value.ToString());
Assert.Equal(filter.Minimum.ToString(SqlDateFormat), sqlParameterCollection[1].Value.ToString());
Assert.Equal(filter.Maximum.ToString(SqlDateFormat), sqlParameterCollection[2].Value.ToString());
Assert.Contains(expectedExtendedQueryTagTableFilter, builtString);
Assert.Contains(expectedFilters, builtString);
}
public void GivenNonUidFilter_WhenIELevelInstance_ValidateDistinctInstances()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(false, new List <DicomTag>());
var filters = new List <QueryFilterCondition>()
{
new StringSingleValueMatchCondition(DicomTag.Modality, "123"),
};
var query = new QueryExpression(QueryResource.AllInstances, includeField, false, 0, 0, filters);
var parm = new SqlQueryParameterManager(CreateSqlParameterCollection());
new SqlQueryGenerator(stringBuilder, query, parm);
string expectedDistinctSelect = @"SELECT
i.StudyInstanceUid
,i.SeriesInstanceUid
,i.SopInstanceUid
,i.Watermark
FROM dbo.Study st
INNER JOIN dbo.Series se
ON se.StudyKey = st.StudyKey
INNER JOIN dbo.Instance i
ON i.SeriesKey = se.SeriesKey";
string expectedFilters = @"AND se.Modality=@p0";
Assert.Contains(expectedDistinctSelect, stringBuilder.ToString());
Assert.Contains(expectedFilters, stringBuilder.ToString());
Assert.DoesNotContain("CROSS APPLY", stringBuilder.ToString());
}
public void GivenDoubleExtendedQueryTagFilter_WhenIELevelStudy_ValidateExtendedQueryTagFilter()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(new List <DicomTag>());
var queryTag = new QueryTag(DicomTag.FloatingPointValue.BuildExtendedQueryTagStoreEntry(level: QueryTagLevel.Study));
var filter = new DoubleSingleValueMatchCondition(queryTag, 123D);
filter.QueryTag = queryTag;
var filters = new List <QueryFilterCondition>()
{
filter,
};
var query = new QueryExpression(QueryResource.AllStudies, includeField, false, 0, 0, filters, Array.Empty <string>());
SqlParameterCollection sqlParameterCollection = CreateSqlParameterCollection();
var parm = new SqlQueryParameterManager(sqlParameterCollection);
new SqlQueryGenerator(stringBuilder, query, parm, SqlServer.Features.Schema.SchemaVersion.V4, DefaultPartition.Key);
string expectedExtendedQueryTagTableFilter = @"INNER JOIN dbo.ExtendedQueryTagDouble ctd1
ON ctd1.PartitionKey = st.PartitionKey
AND ctd1.StudyKey = st.StudyKey
WHERE";
string expectedFilters = @"AND ctd1.TagKey=@p0
AND ctd1.TagValue=@p1";
string builtString = stringBuilder.ToString();
Assert.Equal(queryTag.ExtendedQueryTagStoreEntry.Key.ToString(), sqlParameterCollection[0].Value.ToString());
Assert.Equal(filter.Value.ToString(), sqlParameterCollection[1].Value.ToString());
Assert.Contains(expectedExtendedQueryTagTableFilter, builtString);
Assert.Contains(expectedFilters, builtString);
}
public void GivenStudyDate_WhenIELevelStudy_ValidateDistinctStudyStudies()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(false, new List <DicomTag>());
var minDate = new DateTime(2020, 2, 1);
var maxDate = new DateTime(2020, 3, 1);
var filters = new List <QueryFilterCondition>()
{
new DateRangeValueMatchCondition(DicomTag.StudyDate, minDate, maxDate),
};
var query = new QueryExpression(QueryResource.AllStudies, includeField, false, 0, 0, filters);
var parm = new SqlQueryParameterManager(CreateSqlParameterCollection());
new SqlQueryGenerator(stringBuilder, query, parm);
string expectedDistinctSelect = @"SELECT
st.StudyKey
FROM dbo.Study st";
string expectedCrossApply = @"
FROM dbo.Instance a
WHERE 1 = 1
AND a.StudyKey = f.StudyKey";
Assert.Contains(expectedDistinctSelect, stringBuilder.ToString());
Assert.Contains(expectedCrossApply, stringBuilder.ToString());
Assert.Contains("StudyDate BETWEEN @p0 AND @p1", stringBuilder.ToString());
}
public SqlQueryGenerator(
IndentedStringBuilder stringBuilder,
QueryExpression queryExpression,
SqlQueryParameterManager sqlQueryParameterManager)
{
_stringBuilder = stringBuilder;
_queryExpression = queryExpression;
_parameters = sqlQueryParameterManager;
Build();
}
public SqlQueryGenerator(IndentedStringBuilder sb, SqlQueryParameterManager parameters, SqlServerFhirModel model, bool isHistorySearch)
{
EnsureArg.IsNotNull(sb, nameof(sb));
EnsureArg.IsNotNull(parameters, nameof(parameters));
EnsureArg.IsNotNull(model, nameof(model));
StringBuilder = sb;
Parameters = parameters;
Model = model;
_isHistorySearch = isHistorySearch;
}
internal SearchParameterQueryGeneratorContext(IndentedStringBuilder stringBuilder, SqlQueryParameterManager parameters, SqlServerFhirModel model, string tableAlias = null)
{
EnsureArg.IsNotNull(stringBuilder, nameof(stringBuilder));
EnsureArg.IsNotNull(parameters, nameof(parameters));
EnsureArg.IsNotNull(model, nameof(model));
StringBuilder = stringBuilder;
Parameters = parameters;
Model = model;
TableAlias = tableAlias;
}
public void GivenMultipleExtendedQueryTagFiltersOnSameLevel_WhenIELevelInstance_ValidateExtendedQueryTagFilter()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(new List <DicomTag>());
var queryTag1 = new QueryTag(DicomTag.ModelGroupUID.BuildExtendedQueryTagStoreEntry(level: QueryTagLevel.Series));
var filter1 = new StringSingleValueMatchCondition(queryTag1, "abc");
filter1.QueryTag = queryTag1;
var queryTag2 = new QueryTag(DicomTag.ContainerDescription.BuildExtendedQueryTagStoreEntry(key: 2, level: QueryTagLevel.Series));
var filter2 = new StringSingleValueMatchCondition(queryTag2, "description");
filter2.QueryTag = queryTag2;
var filters = new List <QueryFilterCondition>()
{
filter1,
filter2,
};
var query = new QueryExpression(QueryResource.AllInstances, includeField, false, 0, 0, filters, Array.Empty <string>());
SqlParameterCollection sqlParameterCollection = CreateSqlParameterCollection();
var parm = new SqlQueryParameterManager(sqlParameterCollection);
new SqlQueryGenerator(stringBuilder, query, parm, SqlServer.Features.Schema.SchemaVersion.V4, DefaultPartition.Key);
// cts1 is associated with filter1 which is at the instance level. This means the join should be on all three keys.
// cts2 is associated with filter2 which is at the series level. This means the join should be on only study and series keys.
// ctl4 is associated with filter3 which is at the study level. This means the join should be on only the study key.
string expectedExtendedQueryTagTableFilter = @"INNER JOIN dbo.ExtendedQueryTagString cts1
ON cts1.PartitionKey = st.PartitionKey
AND cts1.StudyKey = st.StudyKey
AND cts1.SeriesKey = se.SeriesKey
INNER JOIN dbo.ExtendedQueryTagString cts2
ON cts2.PartitionKey = st.PartitionKey
AND cts2.StudyKey = st.StudyKey
AND cts2.SeriesKey = se.SeriesKey
WHERE";
string expectedFilters = @"AND cts1.TagKey=@p0
AND cts1.TagValue=@p1
AND cts2.TagKey=@p2
AND cts2.TagValue=@p3";
string builtString = stringBuilder.ToString();
Assert.Equal(queryTag1.ExtendedQueryTagStoreEntry.Key.ToString(), sqlParameterCollection[0].Value.ToString());
Assert.Equal(filter1.Value.ToString(), sqlParameterCollection[1].Value.ToString());
Assert.Equal(queryTag2.ExtendedQueryTagStoreEntry.Key.ToString(), sqlParameterCollection[2].Value.ToString());
Assert.Equal(filter2.Value.ToString(), sqlParameterCollection[3].Value.ToString());
Assert.Contains(expectedExtendedQueryTagTableFilter, builtString);
Assert.Contains(expectedFilters, builtString);
}
public SqlQueryGenerator(
IndentedStringBuilder stringBuilder,
QueryExpression queryExpression,
SqlQueryParameterManager sqlQueryParameterManager,
SchemaVersion schemaVersion,
int partitionKey)
{
_stringBuilder = stringBuilder;
_queryExpression = queryExpression;
_parameters = sqlQueryParameterManager;
_schemaVersion = schemaVersion;
_partitionKey = partitionKey;
Build();
}
public void GivenStudyDate_WhenIELevelStudy_ValidateDistinctStudyStudies()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(new List <DicomTag>());
var minDate = new DateTime(2020, 2, 1);
var maxDate = new DateTime(2020, 3, 1);
var filters = new List <QueryFilterCondition>()
{
new DateRangeValueMatchCondition(new QueryTag(DicomTag.StudyDate), minDate, maxDate),
};
var query = new QueryExpression(QueryResource.AllStudies, includeField, false, 0, 0, filters, Array.Empty <string>());
var parm = new SqlQueryParameterManager(CreateSqlParameterCollection());
new SqlQueryGenerator(stringBuilder, query, parm, SqlServer.Features.Schema.SchemaVersion.V4, DefaultPartition.Key);
string expectedDistinctSelect = @"SELECT
st.StudyKey
FROM dbo.Study st
WHERE 1 = 1";
string expectedCrossApply = @"
FROM dbo.Instance a
WHERE 1 = 1
AND a.StudyKey = f.StudyKey
AND a.Status = 1
ORDER BY a.Watermark DESC";
string expectedFilterAndPage = @"
AND st.StudyDate BETWEEN @p0 AND @p1
ORDER BY st.StudyKey DESC
OFFSET 0 ROWS
FETCH NEXT 100 ROWS ONLY";
string partitionFilter = "AND st.PartitionKey = 1";
Assert.Contains(expectedDistinctSelect, stringBuilder.ToString());
Assert.Contains(expectedCrossApply, stringBuilder.ToString());
Assert.Contains(expectedFilterAndPage, stringBuilder.ToString());
Assert.DoesNotContain(partitionFilter, stringBuilder.ToString());
}
public void GivenModality_WhenIELevelSeries_ValidateDistinctSeries()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(new List <DicomTag>());
var filters = new List <QueryFilterCondition>()
{
new StringSingleValueMatchCondition(new QueryTag(DicomTag.Modality), "123"),
};
var query = new QueryExpression(QueryResource.AllSeries, includeField, false, 0, 0, filters, Array.Empty <string>());
var parm = new SqlQueryParameterManager(CreateSqlParameterCollection());
new SqlQueryGenerator(stringBuilder, query, parm, SqlServer.Features.Schema.SchemaVersion.V4, DefaultPartition.Key);
string expectedDistinctSelect = @"SELECT
st.StudyKey
,se.SeriesKey
FROM dbo.Study st
INNER JOIN dbo.Series se
ON se.StudyKey = st.StudyKey";
string expectedFilterAndPage = @"
AND se.Modality=@p0
ORDER BY se.SeriesKey DESC
OFFSET 0 ROWS
FETCH NEXT 100 ROWS ONLY";
string expectedCrossApply = @"
FROM dbo.Instance a
WHERE 1 = 1
AND a.StudyKey = f.StudyKey
AND a.SeriesKey = f.SeriesKey
AND a.Status = 1
ORDER BY a.Watermark DESC";
Assert.Contains(expectedDistinctSelect, stringBuilder.ToString());
Assert.Contains(expectedCrossApply, stringBuilder.ToString());
Assert.Contains(expectedFilterAndPage, stringBuilder.ToString());
}
public void GivenPatientNameFilter_WithFuzzyMatchMultiWord_ValidateContainsFilterGenerated()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(new List <DicomTag>());
var filters = new List <QueryFilterCondition>()
{
new PersonNameFuzzyMatchCondition(new QueryTag(DicomTag.PatientName), "Fall 6"),
};
var query = new QueryExpression(QueryResource.AllStudies, includeField, true, 10, 0, filters, Array.Empty <string>());
SqlParameterCollection sqlParameterCollection = CreateSqlParameterCollection();
var parm = new SqlQueryParameterManager(sqlParameterCollection);
new SqlQueryGenerator(stringBuilder, query, parm, SqlServer.Features.Schema.SchemaVersion.V4, DefaultPartition.Key);
string expectedParam = $"\"Fall 6*\"";
string expectedFilters = @"AND CONTAINS(st.PatientNameWords, @p0)";
Assert.Equal(expectedParam, sqlParameterCollection[0].Value.ToString());
Assert.Contains(expectedFilters, stringBuilder.ToString());
}
public void GivenExtendedQueryTagFilterWithNonUidFilter_WhenIELevelSeries_ValidateExtendedQueryTagFilter()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(new List <DicomTag>());
var queryTag = new QueryTag(DicomTag.ModelGroupUID.BuildExtendedQueryTagStoreEntry(level: QueryTagLevel.Series));
var extendedQueryTagFilter = new StringSingleValueMatchCondition(queryTag, "123");
extendedQueryTagFilter.QueryTag = queryTag;
var filter = new StringSingleValueMatchCondition(new QueryTag(DicomTag.Modality), "abc");
var filters = new List <QueryFilterCondition>()
{
filter,
extendedQueryTagFilter,
};
var query = new QueryExpression(QueryResource.StudySeries, includeField, false, 0, 0, filters, Array.Empty <string>());
SqlParameterCollection sqlParameterCollection = CreateSqlParameterCollection();
var parm = new SqlQueryParameterManager(sqlParameterCollection);
new SqlQueryGenerator(stringBuilder, query, parm, SqlServer.Features.Schema.SchemaVersion.V4, DefaultPartition.Key);
string expectedExtendedQueryTagTableFilter = @"INNER JOIN dbo.ExtendedQueryTagString cts1
ON cts1.PartitionKey = st.PartitionKey
AND cts1.StudyKey = st.StudyKey
AND cts1.SeriesKey = se.SeriesKey
WHERE";
string expectedFilter = @"AND se.Modality=@p0";
string expectedExtendedQueryTagFilter = @"AND cts1.TagKey=@p1
AND cts1.TagValue=@p2";
string builtString = stringBuilder.ToString();
Assert.Equal(queryTag.ExtendedQueryTagStoreEntry.Key.ToString(), sqlParameterCollection[1].Value.ToString());
Assert.Equal(extendedQueryTagFilter.Value.ToString(), sqlParameterCollection[2].Value.ToString());
Assert.Contains(expectedExtendedQueryTagTableFilter, builtString);
Assert.Contains(expectedFilter, builtString);
Assert.Contains(expectedExtendedQueryTagFilter, builtString);
}
public void GivenNonUidFilter_WhenIELevelInstance_ValidateDistinctInstances()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(new List <DicomTag>());
var filters = new List <QueryFilterCondition>()
{
new StringSingleValueMatchCondition(new QueryTag(DicomTag.Modality), "123"),
};
var query = new QueryExpression(QueryResource.AllInstances, includeField, false, 0, 0, filters, Array.Empty <string>());
var parm = new SqlQueryParameterManager(CreateSqlParameterCollection());
new SqlQueryGenerator(stringBuilder, query, parm, SqlServer.Features.Schema.SchemaVersion.V4, DefaultPartition.Key);
string expectedDistinctSelect = @"SELECT
i.StudyInstanceUid
,i.SeriesInstanceUid
,i.SopInstanceUid
,i.Watermark
FROM dbo.Study st
INNER JOIN dbo.Series se
ON se.StudyKey = st.StudyKey
INNER JOIN dbo.Instance i
ON i.SeriesKey = se.SeriesKey";
string expectedFilters = @"AND se.Modality=@p0";
string expectedPage = @"ORDER BY i.Watermark DESC
OFFSET 0 ROWS
FETCH NEXT 100 ROWS ONLY";
Assert.Contains(expectedDistinctSelect, stringBuilder.ToString());
Assert.Contains(expectedFilters, stringBuilder.ToString());
Assert.Contains(expectedPage, stringBuilder.ToString());
Assert.DoesNotContain("CROSS APPLY", stringBuilder.ToString());
}
public void GivenMultipleExtendedQueryTagFiltersOnDifferentLevels_WhenIELevelInstance_ValidateExtendedQueryTagFilter()
{
var stringBuilder = new IndentedStringBuilder(new StringBuilder());
var includeField = new QueryIncludeField(false, new List <DicomTag>());
var queryTag1 = new QueryTag(DicomTag.ModelGroupUID.BuildExtendedQueryTagStoreEntry(key: 1, level: QueryTagLevel.Instance));
var filter1 = new StringSingleValueMatchCondition(queryTag1, "abc");
filter1.QueryTag = queryTag1;
var queryTag2 = new QueryTag(DicomTag.ContainerDescription.BuildExtendedQueryTagStoreEntry(key: 2, level: QueryTagLevel.Series));
var filter2 = new StringSingleValueMatchCondition(queryTag2, "description");
filter2.QueryTag = queryTag2;
var queryTag3 = new QueryTag(DicomTag.NumberOfAssessmentObservations.BuildExtendedQueryTagStoreEntry(key: 4, level: QueryTagLevel.Study));
var filter3 = new LongSingleValueMatchCondition(queryTag3, 123);
filter3.QueryTag = queryTag3;
var filters = new List <QueryFilterCondition>()
{
filter1,
filter2,
filter3,
};
var query = new QueryExpression(QueryResource.AllInstances, includeField, false, 0, 0, filters);
SqlParameterCollection sqlParameterCollection = CreateSqlParameterCollection();
var parm = new SqlQueryParameterManager(sqlParameterCollection);
new SqlQueryGenerator(stringBuilder, query, parm);
// cts1 is associated with filter1 which is at the instance level. This means the join should be on all three keys.
// cts2 is associated with filter2 which is at the series level. This means the join should be on only study and series keys.
// ctl4 is associated with filter3 which is at the study level. This means the join should be on only the study key.
string expectedExtendedQueryTagTableFilter = @"INNER JOIN dbo.ExtendedQueryTagString cts1
ON cts1.StudyKey = st.StudyKey
AND cts1.SeriesKey = se.SeriesKey
AND cts1.InstanceKey = i.InstanceKey
INNER JOIN dbo.ExtendedQueryTagString cts2
ON cts2.StudyKey = st.StudyKey
AND cts2.SeriesKey = se.SeriesKey
INNER JOIN dbo.ExtendedQueryTagLong ctl4
ON ctl4.StudyKey = st.StudyKey
WHERE";
string expectedFilters = @"AND cts1.TagKey=@p0
AND cts1.TagValue=@p1
AND cts2.TagKey=@p2
AND cts2.TagValue=@p3
AND ctl4.TagKey=@p4
AND ctl4.TagValue=@p5";
string builtString = stringBuilder.ToString();
Assert.Equal(queryTag1.ExtendedQueryTagStoreEntry.Key.ToString(), sqlParameterCollection[0].Value.ToString());
Assert.Equal(filter1.Value.ToString(), sqlParameterCollection[1].Value.ToString());
Assert.Equal(queryTag2.ExtendedQueryTagStoreEntry.Key.ToString(), sqlParameterCollection[2].Value.ToString());
Assert.Equal(filter2.Value.ToString(), sqlParameterCollection[3].Value.ToString());
Assert.Equal(queryTag3.ExtendedQueryTagStoreEntry.Key.ToString(), sqlParameterCollection[4].Value.ToString());
Assert.Equal(filter3.Value.ToString(), sqlParameterCollection[5].Value.ToString());
Assert.Contains(expectedExtendedQueryTagTableFilter, builtString);
Assert.Contains(expectedFilters, builtString);
}
