//// Used for testing. codeSystemCodes parameter not used by required for mapper.
internal IReadOnlyCollection <IValueSetCode> GetCustomValueSetCodes(string valueSetUniqueId, IEnumerable <string> codeSystemCodes)
{
var all = this.CustomDbSet.ToList();
var dtos = this.CustomDbSet.Where(dto => dto.ValueSetUniqueID == valueSetUniqueId).OrderBy(this.SortExpression).ToList();
var mapper = new ValueSetCodeMapper();
return(dtos.Select(dto => mapper.Map(dto)).ToList().AsReadOnly());
}
public IReadOnlyCollection <IValueSetCode> GetValueSetCodes(
string valueSetUniqueId,
IEnumerable <string> codeSystemCodes)
{
var dtos = this.DbSet.Where(dto => dto.ValueSetUniqueID == valueSetUniqueId);
var systemCodes = codeSystemCodes as string[] ?? codeSystemCodes.ToArray();
if (systemCodes.Any())
{
dtos = dtos.Where(dto => systemCodes.Contains(dto.CodeSystemCD));
}
dtos = dtos.OrderBy(this.SortExpression);
var mapper = new ValueSetCodeMapper();
return(dtos.Select(dto => mapper.Map(dto)).ToList().AsReadOnly());
}
/// <remarks>
/// Entity Framework does not support PARTITION BY and "will most likely generate the query using CROSS APPLY"
/// Attempt to use straight also resulted in several warnings indicating that certain portions of the query could
/// not be translated and the expression would be evaluated in the CLR after the execution (so no performance gain).
/// </remarks>
/// <seealso cref="https://stackoverflow.com/questions/43906840/row-number-over-partition-by-order-by-in-entity-framework"/>
public Task <ILookup <string, IValueSetCode> > LookupValueSetCodes(
IEnumerable <string> valueSetUniqueIds,
IEnumerable <string> codeSystemCodes,
int count = 5)
{
var setIds = valueSetUniqueIds as string[] ?? valueSetUniqueIds.ToArray();
if (!setIds.Any())
{
return(Task.FromResult(Enumerable.Empty <IValueSetCode>().ToLookup(vs => vs.ValueSetUniqueId, vs => vs)));
}
var mapper = new ValueSetCodeMapper();
var escapedSetIds = string.Join(",", setIds.Select(EscapeForSqlString).Select(v => "'" + v + "'"));
var innerSql =
$@"SELECT vsc.BindingID, vsc.BindingNM, vsc.CodeCD, vsc.CodeDSC, vsc.CodeSystemCD, vsc.CodeSystemNM, vsc.CodeSystemVersionTXT,
vsc.LastLoadDTS, vsc.RevisionDTS, vsc.SourceDSC, vsc.ValueSetUniqueID, vsc.ValueSetID, vsc.ValueSetNM, vsc.ValueSetOID, vsc.VersionDSC,
ROW_NUMBER() OVER (PARTITION BY vsc.ValueSetUniqueID ORDER BY vsc.ValueSetUniqueID) AS rownum
FROM [Terminology].[ValueSetCode] vsc WHERE vsc.ValueSetUniqueID IN ({escapedSetIds})";
var systemCodes = codeSystemCodes as string[] ?? codeSystemCodes.ToArray();
if (systemCodes.Any())
{
var escapedCodes = string.Join(
",",
systemCodes.Select(EscapeForSqlString).Select(v => "'" + v + "'"));
innerSql += $" AND vsc.CodeSystemCD IN ({escapedCodes})";
}
var sql =
$@"SELECT vscr.BindingID, vscr.BindingNM, vscr.CodeCD, vscr.CodeDSC, vscr.CodeSystemCD, vscr.CodeSystemNM, vscr.CodeSystemVersionTXT,
vscr.LastLoadDTS, vscr.RevisionDTS, vscr.SourceDSC, vscr.ValueSetUniqueID, vscr.ValueSetID, vscr.ValueSetNM, vscr.ValueSetOID, vscr.VersionDSC, vscr.rownum
FROM ({innerSql}) vscr
WHERE vscr.rownum <= {count}
ORDER BY vscr.CodeDSC";
return(Task.Run(() => this.DbSet.FromSql(sql).ToLookup(vsc => vsc.ValueSetUniqueID, vsc => mapper.Map(vsc))));
}