// 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); }