LINQ: Convert any empty string to Null

c# linq sqlbulkcopy


I have a list populated with massive amount of data and over hundred columns. I will have to convert the entire list to DataTable and use SQLBulkCopy to batch insert all data into the database.

Everything is okay except that some of the fields could be empty. And for some reason, some of the empty fields causes some formatting exception after converted to DataTable and during SQLBulkCopy insertion. After some R&D, I found that converting the empty fields to NULL solve the issue for batch insertion using SQLBulkCopy.

Is there any way I can use LINQ to find empty fields in the list and convert them to NULL or DBNULL before I convert the list to DataTable?

Even though I'm going to convert the list to DataTable but I don't want to loop each row and each cell to check if the cell is empty and convert to null because it can affect the performance a lot when the data is too much.

3/27/2017 7:18:10 AM

Popular Answer

I can answer only to the very first part where you can replace all the empty string fields with the Null value using a conditional statment

public class Person
    public string Sex { get; set; }
    public string Name { get; set; }
private static void LinQ3()
    var namesList = new List<Person>();
    namesList.Add(new Person() { Sex = "M", Name = "Jonh" });
    namesList.Add(new Person() { Sex = "F", Name = string.Empty });
    namesList.Add(new Person() { Sex = "M", Name = string.Empty });

    var nullV = namesList.Select(x => new {
        nullableName= string.IsNullOrWhiteSpace(x.Name)?null: x.Name

This will return a list(nullV) where the field "Name" will contain null values for every empty value on the field name. Also, you have to declare every field that you want to convert the empty strings using the same conditional statement

3/27/2017 7:34:25 AM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow