public virtual List <TEntity> MapData(IEnumerable <Row> rows, Row headerRow)
{
//initialize list of container to add data
var entities = new List <TEntity>();
//loop on rows and skip first row that contain only column header
for (int i = 1; i < rows.Count(); i++)
{
// create new instance of T type
TEntity entity = (TEntity)Activator.CreateInstance(typeof(TEntity));
//get data row
Row row = rows.ElementAt(i);
//initialize list of container of setting reference properties
List <string> settingReferenceProperties = new List <string>();
//get row cells include empty values
var rowCellValues = GetRowValues(row, headerRow.Elements <Cell>().Count());
//loop on cells of row
for (int y = 0; y < rowCellValues.Count(); y++)
{
//get reference of cell value
var cellValue = rowCellValues[y];
//to set the value to entity property
//first : check about HeaderEntityReferences if exist and header index is saved in HeaderEntityReferences
if (HeaderEntityReferences != null && HeaderEntityReferences.Any(h => h.Headers.Any(info => info.HeaderIndex == y)))
{
SetReferenceValue(ref entity, rowCellValues, y, settingReferenceProperties);
}
else
{
//get property name from columns header that will be set with value
string propertyName = (string)GetCellValue(headerRow.Elements <Cell>().ElementAt(y));
//set value to property of object
Repository <TEntity> .SetPropertyValue(ref entity, propertyName, cellValue);
}
}
//insert object to list of container
entities.Add(entity);
}
return(entities);
}
public virtual bool ValidateColumnsHeader(IEnumerable <Cell> cells)
{
try
{
//get properties of entity
var properties = typeof(TEntity).GetProperties();
//loop through each cell in header row
for (int i = 0; i < cells.Count(); i++)
{
//get cell reference
Cell cell = cells.ElementAt(i);
//get column header text
string header = (string)GetCellValue(cell);
//check if header name is consider a property in entity
if (!properties.Any(x => x.Name.Equals(header, StringComparison.OrdinalIgnoreCase)))
{
//check in reference entity properties
var dependences = EntityDependences.Where(x => x.Value.GetProperties().Any(y => y.Name.Equals(header, StringComparison.OrdinalIgnoreCase)));
//if not exist then validation process is fail
if (!dependences.Any())
{
return(false);
}
else
{
//header name is consider a property of child reference of the entity
//built HeaderEntityReferences object
if (HeaderEntityReferences == null)
{
HeaderEntityReferences = new List <HeaderEntityReference>();
}
//get dependence reference object that contain (Reference Type & Foreign Key Property name)
var dep = dependences.ElementAt(0);
//check if headerEntityReference which has same referece type is inserted before
var her = HeaderEntityReferences.SingleOrDefault(x => x.ReferenceType == dep.Value);
if (her == null)
{
//create list of headersInfo that hold information of header index and header name and corresponding property name in child reference entity
List <HeaderInfo> headersInfo = new List <HeaderInfo>();
headersInfo.Add(new HeaderInfo()
{
HeaderIndex = i, HeaderName = header, EntityPropertyName = header
});
// create new HeaderEntityReferences
her = new HeaderEntityReference()
{
Headers = headersInfo,
ReferenceType = dep.Value,
ForeignKeyProperty = dep.Key
};
HeaderEntityReferences.Add(her);
}
else
{
//add only header infomartion
her.Headers.Add(new HeaderInfo()
{
HeaderIndex = i, HeaderName = header, EntityPropertyName = header
});
}
}
}
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
public virtual void SetReferenceValue(ref TEntity entity, object[] rowCellValues, int headerIndex, List <string> settingReferenceProperties)
{
// this cell is related to child reference entity
var her = HeaderEntityReferences.SingleOrDefault(h => h.Headers.Any(info => info.HeaderIndex == headerIndex));
//check if foreign key is set value before
if (!settingReferenceProperties.Any(s => s.Equals(her.ForeignKeyProperty, StringComparison.OrdinalIgnoreCase)))
{
// store the setting of foreign key to ignore duplication
settingReferenceProperties.Add(her.ForeignKeyProperty);
List <FilterItems> filters = new List <FilterItems>();
//loop throught headers and its corresponding property name to built the filteration
foreach (var column in her.Headers)
{
FilterItems filter = new FilterItems()
{
Property = column.EntityPropertyName, Operation = FilterOperations.Equal, Value = rowCellValues[column.HeaderIndex], LogicalOperation = LogicalOperations.And
};
filters.Add(filter);
}
//create Repository of reference type
var repositoryType = typeof(Repository <>).MakeGenericType(her.ReferenceType);
//create instance of Repository of reference type
dynamic rep = Activator.CreateInstance(repositoryType, Context);
//invoke GetFilter method in Repository class with paramters to get the filteration expression
var method = repositoryType.GetMethod("GetFilter", System.Reflection.BindingFlags.Static | System.Reflection.BindingFlags.Public);
//set the parameters of method
object[] parameters = new object[] { filters };
//invoke GetFilter method with parameters
var filterExpr = method.Invoke(null, parameters);
//invoke GetPKColumns method in Repository class with paramters to get primary columns name of linked entity
method = repositoryType.GetMethod("GetPKColumns", System.Reflection.BindingFlags.Static | System.Reflection.BindingFlags.Public);
parameters = new object[] { Context };
IEnumerable <string> PkColumnsName = (IEnumerable <string>)method.Invoke(null, parameters);
//call GetReferenceForImport method in Repository class to select Linked Entity using filteration to get pk value
var referencesItems = rep.GetReferenceForImport(includeProperties: PkColumnsName, filter: filterExpr);
//check referenceItems
if (referencesItems == null || referencesItems.Count == 0)
{
//see this if you thorw exception or insert new item of type linked entity
throw new Exception("there are one or more record don't not have linked items which has reference information. please review your data");
}
else if (referencesItems.Count > 1)
{
// throw exception that there are more than one references founded in db
throw new Exception("there are one or more record have more than one linked items which have same reference information. please review your data");
}
else
{
//get reference of linked item founded
var referenceItem = referencesItems[0];
//loop throught primary columns if contain more than one column to get value of pks
foreach (var column in PkColumnsName)
{
//get reference value of each primary column
var referenceValue = Utility.GetPropertyValue(referenceItem, column);
if (referenceValue != null)
{
//set foreign key of entity with pk of linked entity
Repository <TEntity> .SetPropertyValue(ref entity, her.ForeignKeyProperty, referenceValue);
}
}
}
}
}
