DataTable.Load() Turns Enum Into Integer

Question

I'm using SqlBulkCopy to insert some data into a table. My data object has an enum property. I want to save it as the value (G), not an int (1).

At the point where I call dataTable.Load(), the property goes from a 'G' to a 1. Apparently that's just what happens?
https://github.com/npgsql/npgsql/issues/1220

Is there a way around that? I originally thought maybe it was FastMember's (Marc Gravell) issue with ObjectReader, but reader still has the value as 'G', so it's DataTable or .Load() that's the issue.

How can I save the enum as the string and not the int?

    private static DataTable ToDataTable<T>(IEnumerable<T> data, List<string> columnNames)
    {
        string assemblyQualifiedName = typeof(AccrualHistory).AssemblyQualifiedName;
        Type accrualHistoryType = Type.GetType(assemblyQualifiedName);
        List<string> memberInfos = accrualHistoryType.GetMembers().Select(x => x.Name).ToList();

        // Only include properties that are *also* columns in the table.
        IEnumerable<string> propertiesThatAlsoExistInTable =
            memberInfos.Intersect(columnNames, StringComparer.OrdinalIgnoreCase);

        var dataTable = new DataTable();
        using (var reader = new ObjectReader(typeof(T), data, propertiesThatAlsoExistInTable.ToArray()))
        {
            // This is where the enum goes from 'G' to 1.
            dataTable.Load(reader); 
        }

        return dataTable;
    }

Some fall-back options I can try:

Not use SqlBulkCopy
Create a new class and map the original class to it, where the new class has the enum property as a string. Then use the new class to save?

Here's the enum:

public enum FrequencyType
{
    A,     
    G
}

And here's the property on the object I'm trying to save:

public FrequencyType FrequencyType { get; set; }

No. This is a process that will run nightly, and multiple SQL bulk copy actions will occur during that process. — Bob Horn, Nov 27 '19 at 01:11
My suggestion would be to run an UPDATE TABLE statement afterwards that converts your numeric field into the desired string and populates another column with that string. — Robert Harvey, Nov 27 '19 at 01:12
Interesting. I can add that as an option. That would mean that the column couldn't be a FK to a lookup table then. — Bob Horn, Nov 27 '19 at 01:16
Could you do the update in-memory, before returning the DataTable? Like this: https://stackoverflow.com/questions/19629644/how-to-edit-a-row-in-the-datatable — xander, Nov 27 '19 at 02:04
Yeah, that's not going to work. Apparently data rows are strongly typed? So if there is a 1 in that column, I can't change it to a "G". I get `Input string was not in a correct format. Expected type is FrequencyType."` — Bob Horn, Nov 27 '19 at 14:01
I could do something like this, but this is starting to get super unfortunate and hogging memory: https://stackoverflow.com/a/9028087/279516 — Bob Horn, Nov 27 '19 at 14:05

DataTable.Load() Turns Enum Into Integer

0 Answers0