Servicestack - Ormlite - high volume data loading

Question

I am getting some issues with Servicestack and OrmLite in high data loading scenarios.

Specifically,
1. I have a list of 1000 000 + entities
2. I would like to insert them into Db (using Sql Server) if record does not exist yet

Thus,

public class Entity  
{  
  [Autoincrement]  
  public int Id {get;set;}  
  public string Name {get;set;}  
  public string Address {get;set;}  
}

Now for the import logic,

List<Entity> entities = oneMillionEntities.ToList();  
foreach (var entity in entities)  
{   
    if (!db.Exists<Entity>(ar => ar.Address == entity.Address))  
    {  
        db.Save(entity);  
    }  
}  

Issue is that quite often db is still busy with save action thus db.Exists does not always produce correct result. What is the best way of handling these scenarios?

Answer 1

Try

// Prepare SqlExpression
var ev = Db.From<Entity>().Select(p => p.Address).GroupBy(p => p.Address);

// Execute SqlExpression and transform result to HashSet
var dbAddresses = Db.SqlList(ev).ToHashSet();

// Filter local entities and get only local entities with different addresses
var filteredEntities = oneMillionEntities.Where(p => 
!dbAddresses.Contains(p.Address));

// Bulk insert
db.InsertAll(filteredEntities.ToList());