public static async Task <Dictionary <int, Dictionary <string, object> > > GetColumns(BitcornContext dbContext, string[] columns, int[] primaryKeys, UserReflectionContext context = null)
{
if (context == null)
{
if (_StandardReflectionModel == null)
{
_StandardReflectionModel = new UserReflectionContext(UserModel);
}
_StandardReflectionModel.CacheReflection();
context = _StandardReflectionModel;
}
else
{
context.CacheReflection();
}
//CacheReflection();
if (columns == null)
{
var output = new Dictionary <int, Dictionary <string, object> >();
for (int i = 0; i < primaryKeys.Length; i++)
{
output.Add(primaryKeys[i], new Dictionary <string, object>());
}
return(output);
}
string[] validColumns;
if (columns.Length == 1 && columns[0] == "*")
{
validColumns = context.TableColumns.SelectMany(u => u.Value).Distinct().ToArray();
}
else
{
var list = columns.Where(c => context.ColumnToTable.ContainsKey(c.ToLower())).Distinct().ToList();
string userIdKey = nameof(User.UserId).ToLower();
if (!list.Select(c => c.ToLower()).Contains(userIdKey))
{
list.Add(userIdKey);
}
validColumns = list.ToArray();
}
if (validColumns.Length > 0)
{
string sql = GenerateSql(context, validColumns, primaryKeys);
return(await RawSqlQuery(dbContext, sql));
}
return(null);
}
static string GenerateSql(UserReflectionContext context, string[] columns, int[] primaryKeyValues)
{
StringBuilder sql = new StringBuilder();
List <string> uniqueTables = new List <string>();
sql.Append("SELECT ");
for (int i = 0; i < columns.Length; i++)
{
if (i > 0)
{
sql.Append(',');
}
string column = columns[i];
string table = context.ColumnToTable[column.ToLower()].Name;
if (!uniqueTables.Contains(table))
{
uniqueTables.Add(table);
}
sql.Append('[');
sql.Append(table);
sql.Append("].[");
sql.Append(column);
sql.Append("] ");
}
var firstTable = uniqueTables[0];
var selectFirstTable = "[" + firstTable + "]";
sql.Append(" FROM ");
sql.Append(selectFirstTable);
sql.Append(' ');
string primaryKeyName = nameof(User.UserId);
if (uniqueTables.Count > 1)
{
for (int i = 1; i < uniqueTables.Count; i++)
{
sql.Append(" JOIN ");
string selfSelect = "[" + uniqueTables[i] + "]";
sql.Append(selfSelect);
sql.Append(" on ");
sql.Append(selectFirstTable);
sql.Append('.');
sql.Append(primaryKeyName);
sql.Append('=');
sql.Append(selfSelect);
sql.Append('.');
sql.Append(primaryKeyName);
sql.Append(' ');
}
}
sql.Append(" Where ");
sql.Append(selectFirstTable);
sql.Append('.');
sql.Append(primaryKeyName);
if (primaryKeyValues.Length == 1)
{
sql.Append("=");
sql.Append(primaryKeyValues[0]);
}
else
{
sql.Append(" in (");
sql.Append(string.Join(',', primaryKeyValues));
sql.Append(")");
}
return(sql.ToString());
}
