How to get the right foreign-key when importing from a CSV

advapi 0
Viewed 32

I'm migrating an application from Access to a cloud-based solution. This access file has no relation defined and I've trouble importing data. For simplicity, I've exported the tables as CSV and tried to import with CSVHelper.

Consider this simple scenario

Book_ID;escription;BookType

Now in my csv I've got

1;A plague tale;Drama 2;Flowers and how to kill them;floriculture

I've created a BookType class, mapped to Table with EF Core with the form

[PK,Identity] GUID,Description

And the Book class as

[PK,Identity] GUID,BookDescription,[FK to BookType] FK_BookType

I don't know (and even don't know if it's possible) how to fill the relation. I mean

I can import the distinct values from CSV of BookType and insert them into the BookType table I don't know when I import the Books how to tell instead of the value put the Guid taken from the BookType.

I'm using ABP.io as Framework, so I've AutoMapper and the dependency injection, but I think I can't resolve the IRespository inside the Book's mapper.

Any suggestion?

csvhelper automapper .net
1 Answers

One solution could be to use EF Core Alternate Keys. Your DbContext could look like this (change Id's from int to GUID for your scenario):

internal class MyContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<BookType> BookTypes { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>()
            .HasOne(book => book.BookType)
            .WithMany(bookType => bookType.Books)
            .HasForeignKey(book => book.BookTypeDescription)
            .HasPrincipalKey(bookType => bookType.Description);
    }
}

public class BookType
{
    public int BookTypeId { get; set; }
    public string Description { get; set; }

    public List<Book> Books { get; set; }
}

public class Book
{
    public int BookId { get; set; }
    public string Description { get; set; }

    public string BookTypeDescription { get; set; }
    public BookType BookType { get; set; }
}

Now if you map Description from CSV to Book.BookTypeDescription EF Core should handle everything correctly.

More documentation about Principal key.

Edit:

Another option is to load the CSV, find the distinct book type descriptions using LINQ and insert them to the BookType table so that you get Id's. Then manually map each book type description to book type id.

Example code:

void ImportFromCsv()
{
    IEnumerable<BookCsv> booksFromCsv = LoadDataFromCsv();
    
    // get distinct book types
    List<BookType> bookTypes = booksFromCsv
       .GroupBy(b => b.Description)
       .Select(g => g.First())
       .ToList();

    _dbContext.BookTypes.AddRange(bookTypes);

    // ef core will populate the BookType.Id's here
    _dbContext.SaveChangesAsync();    
      
    // manually map each book type description to book type id
    foreach(var bookCsv in booksFromCsv)
    {
        var bookType = bookTypes.First(b => b.Description = bookCsv.Description);
        bookCsv.BookTypeId = bookType.Id;
    }
    
    var books = _mapper.Map<IEnumerable<Book>>(booksFromCsv);
}

Where the entities would look like this:

public class BookType
{
    public int BookTypeId { get; set; }
    public string Description { get; set; }

    public List<Book> Books { get; set; }
}

public class Book
{
    public int BookId { get; set; }
    public string Description { get; set; }

    public int BookTypeId { get; set; }
    public BookType BookType { get; set; }
}

And the BookCsv class:

public class BookCsv
{
    public int BookId { get; set; }
    public string Description { get; set; }

    public int BookTypeId { get; set; } // we will populate manually after inserting book types to databases
}
Dimitris Maragkos0
Related