BulkInsert Failed to attempt to insert with custom column name

[gustavopinho]

Model

[Key] public int Id { get; set; } [Required] [Column("JobFunctionID")] public JobFunction JobFunction { get; set; } [Required] [Column("StatusID")] public Status Status { get; set; } [Required] [Column("EmployeeID")] public Employee Employee { get; set; } [Column(TypeName = "datetime")] public DateTime CreatedAt { get; set; } [Column(TypeName = "datetime")] public DateTime UpdatedAt { get; set; }

Insert

using (var transaction = _context.Database.BeginTransaction()) { if (entities.Count() > 0) { _context.BulkInsert(entities); } transaction.Commit(); }

Error
System.InvalidOperationException: The given value of type String from the data source cannot be converted to type int of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a Int32. ---> System.FormatException: Input string was not in a correct format. at System.Number.ThrowOverflowOrFormatException(ParsingStatus status, TypeCode type) at System.String.System.IConvertible.ToInt32(IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) --- End of inner exception stack trace --- at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) at Microsoft.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed) --- End of inner exception stack trace --- at EFCore.BulkExtensions.SqlBulkOperation.Insert[T](DbContext context, Type type, IList1 entities, TableInfo tableInfo, Action1 progress) at EFCore.BulkExtensions.SqlBulkOperation.Insert[T](DbContext context, IList1 entities, TableInfo tableInfo, Action1 progress) at EFCore.BulkExtensions.DbContextBulkTransaction.Execute[T](DbContext context, IList1 entities, OperationType operationType, BulkConfig bulkConfig, Action1 progress) at EFCore.BulkExtensions.DbContextBulkExtensions.BulkInsert[T](DbContext context, IList1 entities, BulkConfig bulkConfig, Action1 progress) at TrainingManagement.Controllers.ManagementController.DataUpdateAsync(IFormFile file) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)`

[gandhis1]

When you replace the BulkInsert() call with an AddRange() and SaveChanges(), does it work?

[ErikLeClerq]

I have the same issue. it works with savechanges.

[gandhis1]

What version of the library are you using? If you revert to version 3.1.1, does it work?

I think it's important to confirm that this isn't a regression that occurred between version 3.1.2 to the current 3.1.4, as these all were released in the past week.

[ErikLeClerq]

same issue on both versions

[gandhis1]

@gustavopinho - Is your EF core model valid? If you have a foreign key against a JobFunctions table, you need a navigation element of type JobFunction with a [ForeignKey(nameof(JobFunctionId))] attribute, and an int JobFunction property with a [ForeignKey(nameof(JobFunction))] attribute.

Correct me if I'm wrong, but what you have now is basically attempting to map a JobFunction navigation element to JobFunctionId using the Column attribute (which is the attribute for a one-to-one column rename).

Specifically, this is what you have:

[Column("JobFunctionID")]
public JobFunction JobFunction { get; set; }
[Column("StatusID")]
public Status Status { get; set; }
[Column("EmployeeID")]
public Employee Employee { get; set; }

For a proper foreign key relationship, this is what you are supposed to have. Technically I believe you don't need the attribute on both the navigation element and the foreign key itself, but for demonstration's sake:

[ForeignKey("JobFunction")]
public int JobFunctionID { get; set; }
[ForeignKey("Status")]
public int StatusID { get; set; }
[ForeignKey("Employee")]
public int EmployeeID { get; set; }
[ForeignKey("JobFunctionID")]
public JobFunction JobFunction { get; set; }
[ForeignKey("StatusID")]
public Status Status { get; set; }
[ForeignKey("EmployeeID")]
public Employee Employee { get; set; }

[gustavopinho]

Updated
Hi @gandhis1, my code is based on this post No foreign key property, I don't know if this is the best practice, I'm a newbie with EF core.

But my model is valid!

Base example:

class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<AuditEntry>();
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public Blog Blog { get; set; }
}

In my example my models generates the following migration:

migrationBuilder.CreateTable(
                name: "EmployeeJobFunction",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    JobFunctionId = table.Column<int>(nullable: false),
                    StatusId = table.Column<int>(nullable: false),
                    EmployeeId = table.Column<int>(nullable: false),
                    CreatedAt = table.Column<DateTime>(type: "datetime", nullable: false),
                    UpdatedAt = table.Column<DateTime>(type: "datetime", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_EmployeeJobFunction", x => x.Id);
                    table.ForeignKey(
                        name: "FK_EmployeeJobFunction_Employee_EmployeeId",
                        column: x => x.EmployeeId,
                        principalTable: "Employee",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_EmployeeJobFunction_JobFunction_JobFunctionId",
                        column: x => x.JobFunctionId,
                        principalTable: "JobFunction",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_EmployeeJobFunction_Status_StatusId",
                        column: x => x.StatusId,
                        principalTable: "Status",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

[borisdj]

Shadow FKs are not supported, so you should add FK property as well.
Also I find it better practice to have them defined explicitly then to use shadow.

It is stated in the ReadMe:

For mapping FKs explicit Id properties have to be in entity. Having only Navigation property is not supported.

Related issue: BulkInsert does not populate foreign keys for child entities

[borisdj]

Support for Shadow FKs is now added, version 3.2.1+