.NET 5.0 - Convert matrix type of structure (datatable) into individual records

user_2638281 0
Viewed 36

I have a service that currently returns System.DataTable with this type of structure: Attached Data Table

I need to convert (transpose) this type of structure into model that looks like this:

    [Required]
    public string Field { get; set; }
    
    [Required]
    [StringLength(500)]
    public string Identifier { get; set; }

    public string? FieldValue { get; set; }

The final model is the database model. I am currently iterating thru datatable records. Works fine with 1000-10000 records, but when parsing 100000 and more it gets slow. If I can bulk stage the datatable structure in the database that would be great, but Field columns change, which makes it a bit more complicated.

Also, I can return DataFrame if the need be instead of DataTable. Transposing DataFrame I assume is easier, but the caveat here is that Identifier column needs to stay, rather than being transposed as Field columns.

.net-core dataframe performance c#
1 Answers

You can create an extension method and use like below but not sure about performance

//dt object of Datatable
var entities = dt.Transpose("SECURITIES");


public static class DataTableTranspose
{
    public static IList<MyEntity> Transpose(this DataTable dataTable, string indentifierColumn)
    {
        var entities = new List<MyEntity>();
        if (dataTable is null || dataTable.Rows.Count == 0 || string.IsNullOrWhiteSpace(indentifierColumn))
            return entities;
        
        for (int i = 0; i < dataTable.Rows.Count; i++)
        {
            var entity = new MyEntity()
            {
                Identifier = dataTable.Rows[i][indentifierColumn].ToString()
            };
            for (int j = 0; j < dataTable.Columns.Count; j++)
            {
                if (dataTable.Columns[j].ColumnName != indentifierColumn)
                {
                    entity.Field = dataTable.Columns[j].ColumnName;
                    entity.FieldValue = dataTable.Rows[i][j].ToString();
                }
            }
            entities.Add(entity);
        }

        return entities;
    }
    public class MyEntity
  {
    public string Field { get; set; }
    public string Identifier { get; set; }

    public string? FieldValue { get; set; }
  }
user_119975380
Related