CODEWITHSUNDEEP
c#.netlinqentity-frameworkdatatable
Jader Dias
Jader Dias

Reputation: 90475

What's wrong with this DataTable while SqlBulkCopy-ing from an Entity?

I know the method below isn't meant to work with any Entity, and its use shouldn't be enforced.

I found a System.Data.Linq.Table extension method which uses SqlBulkCopy to insert data. I am trying to adapt it to the Entity Framework but it throws a strange exception, while the original works for Linq-To-Sql data classes. I couldn't find the flaw so far, it happens with any SQL table, in a 1-1 mapping. Could you help me?

public static class ObjectQueryExtensions
{
    public static string GetName<TEntity>(
        this ObjectQuery<TEntity> objectQuery)
        where TEntity : class
    {
        var tableNameGroup = new Regex(@"FROM\s([^\s]*)\s"
            , RegexOptions.IgnoreCase);
        var sql = objectQuery.ToTraceString();
        var tableNameGroupMatch = tableNameGroup.Match(sql);
        return tableNameGroupMatch.Groups[1].Value;
    }

    public static void BulkInsert<TEntity>(
          this ObjectQuery<TEntity> objectQuery
        , IEnumerable<TEntity> items)
        where TEntity : class
    {
        using (var dt = new DataTable())
        {
            var properties = typeof(TEntity)
                .GetProperties()
                .Where(property => property.Name  != "EntityKey")
                .Where(property => property.Name  != "EntityState")
                ;
            foreach (var property in properties)
            {
                dt.Columns.Add(property.Name
                   , Nullable.GetUnderlyingType(property.PropertyType)
                   ?? property.PropertyType);
            }

            foreach (var t in items)
            {
                DataRow row = dt.NewRow();
                foreach (var info in properties)
                {
                    row[info.Name] = info.GetValue(t, null) ?? DBNull.Value;
                }
                dt.Rows.Add(row);
            }

            var entityConnection = (EntityConnection)objectQuery
                .Context.Connection;
            using (var sqlBulkCopy = new SqlBulkCopy(
                 entityConnection.StoreConnection.ConnectionString))
            {
                sqlBulkCopy.DestinationTableName = objectQuery.GetName();
                sqlBulkCopy.WriteToServer(dt);
            }
        }
    }
}

The exception

Test method LinqExtensionsTest.ObjectQueryExtensionsTest.BulkInsertTest threw exception: System.InvalidOperationException: The given value of type Int64 from the data source cannot be converted to type datetime of the specified target column. ---> System.InvalidCastException: Failed to convert parameter value from a Int64 to a DateTime. ---> System.InvalidCastException: Invalid cast from 'Int64' to 'DateTime'..

The stack trace

System.Int64.System.IConvertible.ToDateTime(IFormatProvider provider) System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) LinqExtensions.ObjectQueryExtensions.BulkInsert[TEntity](ObjectQuery1 objectQuery, IEnumerable1 items) in LinqExtensions\LinqExtensions\ObjectQueryExtensions.cs: line 60 LinqExtensionsTest.ObjectQueryExtensionsTest.BulkInsertTest() in LinqExtensions\LinqExtensionsTest\ObjectQueryExtensionsTest.cs: line 88

Upvotes: 1

Views: 3970

Answers (2)

Massimiliano Sasso
Massimiliano Sasso

Reputation: 63

I know that you created your DataTable from your entities, but my suggestion is to check if the order of the columns in the DataTable is the same of the columns in the table (maybe you changed your database and didn't update your model). If it is not the same you might have mismatching data type and bump into the invalid cast error. It happened to me ;)

Upvotes: 3

Konamiman
Konamiman

Reputation: 50273

The exception you got says pretty much it all: in your entity object you have a property of type Int64 which is defined as DateTime in the database, and there is no way to implicitly convertfrom one to another. Do you really intend to represent that database column as an integer? Maybe it is just a mistake in the definition of the entity class.

Upvotes: 0

Related Questions