Odtnhj

I need to add an import/export functionality to my ASP.NET Core application.

What I would like is to take entities in one database, export these entities into one file, and then import that file into a new database.

My problem is that I have some entities that hold same foreign key. Here is a simple model illustrating what I want to do:

public class Bill 

 public List<Product> Products get; set; 
 public int Id get; set; 
 ...


public class Product 

 public int Id get; set; 
 public int ProductCategoryId get; set; 
 public ProductCategory ProductCategory get; set; 
 ...


public class Category 

 public int Id get; set; 
 public string Name get; set; 

I want to export a bill to be imported on an other environment of my application. So if I export the bill, I will get a Json like this:

 "id": 1,
 "products" : [
 
 "id" : 1,
 "productCategoryId": 1,
 "productCategory": 
 "id" : 1,
 "name" : "Category #1"
 
 ,
 
 "id" : 2,
 "productCategoryId": 1,
 "productCategory": 
 "id" : 1,
 "name" : "Category #1"
 
 ,
 
 "id" : 3,
 "productCategoryId": 1,
 "productCategory": 
 "id" : 2,
 "name" : "Category #2"
 
 
 ]

If I deserialize this json into entities in my new environment (ignoring Ids mapping of course), I will get three new categories (category will be duplicated for product 1 and 2) because the serializer will instanciate two categories...

So when I push it into my database, it will add 3 lines instead 2 into the Category table...

Thanks in advance for your answers.

Suppose you have a list of Category to be imported, you could firstly get the id list of these categories and then query the database to make sure which has been already stored in database. And for those already existing ones, just skip them (or update them as you like).

Since we have multiple entity types (categories,products,bills and potential BillProducts), rather than writing a new importer for each TEntity , I prefer to writing a generic Importer method to deal with any Entity type list with Generic and Reflection :

public async Task ImportBatch<TEntity,TKey>(IList<TEntity> entites)
 where TEntity : class 
 where TKey: IEquatable<TKey>

 var ids = entites.Select( e=> GetId<TEntity,TKey>(e));
 var existingsInDatabase=this._context.Set<TEntity>()
 .Where(e=> ids.Any(i => i.Equals(GetId<TEntity,TKey>(e)) ))
 .ToList();

 using (var transaction = this._context.Database.BeginTransaction())
 
 try
 this._context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT " + typeof(TEntity).Name + " ON;");
 this._context.SaveChanges();

 foreach (var entity in entites)
 
 var e= existingsInDatabase.Find(existing => 
 var k1 =GetId<TEntity,TKey>(existing); 
 var k2=GetId<TEntity,TKey>(entity);
 return k1.Equals(k2); 
 );
 // if not yet exists
 if(e == null)
 this._context.Add(entity);
 else
 // if you would like to update the old one when there're some differences
 // uncomment the following line :
 // this._context.Entry(e).CurrentValues.SetValues(entity);
 
 
 await this._context.SaveChangesAsync();
 transaction.Commit();
 
 catch
 transaction.Rollback();
 
 finally
 this._context.Database.ExecuteSqlCommand($"SET IDENTITY_INSERT " + typeof(TEntity).Name + " OFF;");
 await this._context.SaveChangesAsync();
 
 
 return;

Here the GetId<TEntity,TKey>(TEntity e) is a simple helper method which is used to get the key filed of e:

 // use reflection to get the Id of any TEntity type
 private static TKey GetId<TEntity,TKey>(TEntity e)
 where TEntity : class
 where TKey : IEquatable<TKey>
 
 PropertyInfo pi=typeof(TEntity).GetProperty("Id");
 if(pi == null) throw new Exception($"the type typeof(TEntity) must has a property of `Id`"); 
 TKey k = (TKey) pi.GetValue(e);
 return k ;
 

To make the code more reusable, we can create an EntityImporter service to hold the method above :

public class EntityImporter
 private DbContext _context;

 public EntityImporter(DbContext dbContext)
 this._context = dbContext;
 

 public async Task ImportBatch<TEntity,TKey>(IList<TEntity> entites)
 where TEntity : class 
 where TKey: IEquatable<TKey>
 
 // ...
 

 public static TKey GetId<TEntity,TKey>(TEntity e)
 where TEntity : class
 where TKey : IEquatable<TKey>
 
 // ... 
 

and then register the services at startup time:

services.AddScoped<DbContext, AppDbContext>();
services.AddScoped<EntityImporter>();

Test Case :

Firstly, I'll take several categories as an example :

var categories = new ProductCategory
 new ProductCategory
 Id = 1,
 Name="Category #1"
 ,
 new ProductCategory
 Id = 2,
 Name="Category #2"
 ,
 new ProductCategory
 Id = 3,
 Name="Category #3"
 ,
 new ProductCategory
 Id = 2,
 Name="Category #2"
 ,
 new ProductCategory
 Id = 1,
 Name="Category #1"
 ,
;

await this._importer.ImportBatch<ProductCategory,int>(categories);

The expected results should be only 3 rows imported :

1 category #1
2 category #2 
3 category #3

And here's a screenshot it works:

Finally, for your bills json, you can do as below to import the entites :

var categories = bill.Products.Select(p=>p.ProductCategory).ToList();
var products = bill.Products.ToList();
// other List<TEntity>...

// import the categories firstly , since they might be referenced by other entity
await this._importer.ImportBatch<ProductCategory,int>(categories);
// import the product secondly , since they might be referenced by BillProduct table
await this._import.ImportBatch<Product,int>(products);
// ... import others