CsvHelper and Primary Key with Entity Framework

Question

I'm using Entity Framework 6.0.0.0 with Josh Close's great CsvHelper tool.

All was going great BEFORE I added EF to the mix, as I was dealing primarily just with classes, no database, therefore NO PRIMARY KEYS!

So now I have confirmed that my primary keys are setup properly in the DB as auto incrementing identities.

And my class looks something like this:

[Table("tbl_P")]
public class P // PData File
{
    // Column P1
    public string StrTrId { get; set; }

    // NOTE COLUMNS P2-P99 GO HERE

    // Column P99
    public string StrCo { get; set; }

    // Column P100
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int pk_PropertyId { get; set; }

}

Note, I put the Primary Key at the end, hoping that this would help the mapping work without having to somehow shift the columns all around.

I'm reading into the above class like this:

var csv = new CsvReader(reader);
csv.Configuration.HasHeaderRecord = false;
csv.Configuration.WillThrowOnMissingField = false;
csv.Configuration.TrimFields = true;

while (csv.Read())
{
    var pr = csv.GetRecord<P>();
}

So I'm not sure if the issue has to do with me putting the Primary Key at the end of the class, or if it's b/c Entity Framework requires the Primary Key to have a default value. Either way, perhaps there's a way to ignore a column in CsvHelper when I process the data?

The errors I'm getting are:

the conversion cannot be performed

"Row: '1' (1 based) Type: 'P' Field Index: '99' (0 based)"

There is no value in the CSV file for this field, which I imagine is the problem since it's an int not an int? but if it's a Primary Key, I can't have it as nullable, right?

Any help would be great here...

Answer 1

So I was able to get it to work.

The error had to do with the empty Primary Key in the class with the non-nullable integer.

So I made the following change:

// Column P100
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int? pk_PropertyId { get; set; } = null

And now it's working fine.

So nothing to do with CsvHelper, that part's working great. It was an Entity Framework thing just having to do with my inexperience using the tool.

Maybe this will help someone else.

I guess it turns out that you can set the Primary Key to any value and the DB will basically ignore it and assign its own value anyways.