// find entities in relations where given entity's primary key is being used as foreign key
public List <string> GetChildEntities(MainTableRowSelectModel model)
{
List <string> allChildEntities = _relations.AsEnumerable()
.Where(r => r.Field <string>(RelationDataColumns.PARENT_TABLE) == model.table)
.Select(r => r.Field <string>(RelationDataColumns.CHILD_TABLE)).Distinct().OrderBy(o => o).ToList();
List <string> childEntities = new List <string>();
if (model.hideChilEntitiesWhenNoData)
{
// if need to hide child entities when no related data found, query each child entities to check if data exists
foreach (string childEntity in allChildEntities)
{
RelatedDataSelectModel fakeModel = new RelatedDataSelectModel {
topN = 1, fromEntity = model.table, toEntity = childEntity,
toEntityType = RelationType.CHILD, keyVals = model.colNameVals
};
List <JQDTFriendlyColumnInfo> columnsForFrontEnd = new List <JQDTFriendlyColumnInfo>();
DataTable dt = GetParentOrChildData(fakeModel, ref columnsForFrontEnd);
if (dt.Rows.Count > 0)
{
childEntities.Add(childEntity);
}
}
}
else
{
childEntities = allChildEntities;
}
return(childEntities);
}
public IActionResult MainEntityRowSelect([FromBody] MainTableRowSelectModel model)
{
try
{
return(Json(new { parentEntities = _businessLayer.GetParentEntities(model), childEntities = _businessLayer.GetChildEntities(model) }));
}
catch (Exception ex)
{
return(Json(new { error = ex.Message }));
}
}
// find entities in relations where given entity contains foreign keys
public List <string> GetParentEntities(MainTableRowSelectModel model)
{
// get current maintable row
List <JQDTFriendlyColumnInfo> entityCols = _dataAccess.GetColumns(model.table);
string criteria = _dataAccess.ColNameValToCriteria(model.colNameVals, entityCols);
string sql = _dataAccess.BuildBasicSql("*", model.table, criteria, entityCols[0].name, "", 1);
DataTable dt = _dataAccess.GetData(sql);
// for each foreignkeys make sure value is not null and then add to list
var parentAndFk = new List <Tuple <string, string> >();
foreach (var relationRow in _relations.AsEnumerable()
.Where(r => r.Field <string>(RelationDataColumns.CHILD_TABLE) == model.table))
{
string fkColName = relationRow.Field <string>(RelationDataColumns.CHILD_KEY);
if (dt.Rows[0][fkColName] != DBNull.Value)
{
parentAndFk.Add(Tuple.Create(relationRow.Field <string>(RelationDataColumns.PARENT_TABLE), fkColName));
}
}
// as child table can have multiple fk for same parent, for each fk add parent item separatly including fk name
List <string> parents = new List <string>();
foreach (var pGrp in parentAndFk.GroupBy(o => o.Item1).Select(g => new { table = g.Key, Count = g.Count() }))
{
if (pGrp.Count <= 1)
{
parents.Add(pGrp.table);
}
else
{
foreach (var match in parentAndFk.Where(o => o.Item1 == pGrp.table))
{
parents.Add(match.Item2 + PARENT_TABLE_FK_SEPARATOR + match.Item1);
}
}
}
return(parents);
}
