1

So what I'm trying to do is create a basic sync system between a CSV and a Database (not using SSIS), it also does some other magic stuff for computations and such like.

The first step of a sync is to remove any users in the DB who are no longer in the CSV file.

I have a List<StaffInfo> where StaffInfo is 

public class StaffInfo
{
    public int ID {get;set;}
    public string Firstname {get;set;
    // etc etc etc ... ... ...
}

My initial thoughts was to get a list of UserIDs from the list then select users not in the DB and then loop through and delete, but this seems very wasteful 

// oUsers contains List<StaffInfo>
// Context is the reference to the EF Context 
var myList = (from c in oUsers select c.ID).ToList();
var usersNotInDb = context.Users.Where(x => !myList.Contains(x.UserId));

foreach (var user in usersNotInDb)
{
    context.DeleteObject(user);
}
context.SaveChanges();

Is there a better way to accomplish this without so much either data transfer or manipulation of data directly (i.e converting the complex object to a list of user ids)

John Mitchell
  • 9,653
  • 9
  • 57
  • 91
  • If performance is your concern, you could call a stored procedure and pass list of id's (and so don't use EF).(http://www.dotnetspider.com/resources/29792-Pass-array-stored-procedure.aspx) Stored procedure then contains the data logic to delete correct rows. – L-Four Mar 25 '13 at 14:20
  • I thought about that at first L-Three but if I have a large number of users I'm worried that I may overrun the varchar(MAX) – John Mitchell Mar 25 '13 at 14:21
  • Well then you could split it into batches. – L-Four Mar 25 '13 at 14:22
  • I might end up doing that (assuming I can still manage to get full transaction support working across multiple SPs) but I'm still curious to see if there’s a better solution for EF. – John Mitchell Mar 25 '13 at 14:29
  • 1
    Check this article http://stackoverflow.com/questions/1069311/passing-an-array-of-parameters-to-a-stored-procedure – Max Mar 25 '13 at 14:29
  • Also check http://stackoverflow.com/questions/2588885/entities-framework-4-doing-a-bulk-delete – L-Four Mar 25 '13 at 14:30

0 Answers0