public void AddrangeEFUtilities(List <User> getData)
{
using (var db = new TestDbContext())
{
EFBatchOperation.For(db, db.Users).InsertAll(getData);
}
}
public void UpdateAll_IncrementIntValue_RenamedColumn()
{
RenamedAndReorderedContext.SetupTestDb();
using (var db = new RenamedAndReorderedContext())
{
db.BlogPosts.Add(new RenamedAndReorderedBlogPost {
Title = "T1", Created = new DateTime(2013, 01, 01)
});
db.BlogPosts.Add(new RenamedAndReorderedBlogPost {
Title = "T2", Created = new DateTime(2013, 02, 01), Reads = 10
});
db.BlogPosts.Add(new RenamedAndReorderedBlogPost {
Title = "T3", Created = new DateTime(2013, 03, 01)
});
db.SaveChanges();
}
using (var db = new RenamedAndReorderedContext())
{
var count = EFBatchOperation.For(db, db.BlogPosts).Where(b => b.Title == "T2").Update(b => b.Reads, b => b.Reads + 100);
Assert.AreEqual(1, count);
}
using (var db = new RenamedAndReorderedContext())
{
var post = db.BlogPosts.First(p => p.Title == "T2");
Assert.AreEqual(110, post.Reads);
}
}
protected void BulkInsert(IEnumerable <ExampleObject> entities, string warnMessagePrefix = "")
{
ExampleDbContext db = new ExampleDbContext();
if (entities == null)
{
return;
}
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
try
{
EFBatchOperation.For(db, db.Objects).InsertAll(entities);
stopWatch.Stop();
Console.WriteLine(warnMessagePrefix + " : Bulk Insert duration in seconds: {0}", stopWatch.Elapsed.TotalSeconds);
}
catch (SqlException e)
{
stopWatch.Stop();
Console.WriteLine(warnMessagePrefix + " : Bulk Insert duration in seconds: {0}", stopWatch.Elapsed.TotalSeconds);
Console.WriteLine(e.Message, e);
throw;
}
}
public void UpdateAll_SetDateTimeValueFromVariable_RenamedColumn()
{
RenamedAndReorderedContext.SetupTestDb();
using (var db = new RenamedAndReorderedContext())
{
db.BlogPosts.Add(new RenamedAndReorderedBlogPost {
Title = "T1", Created = new DateTime(2013, 01, 01)
});
db.BlogPosts.Add(new RenamedAndReorderedBlogPost {
Title = "T2", Created = new DateTime(2013, 02, 01)
});
db.BlogPosts.Add(new RenamedAndReorderedBlogPost {
Title = "T3", Created = new DateTime(2013, 03, 01)
});
db.SaveChanges();
}
using (var db = new RenamedAndReorderedContext())
{
var count = EFBatchOperation.For(db, db.BlogPosts).Where(b => b.Title == "T2").Update(b => b.Created, b => DateTime.Today);
Assert.AreEqual(1, count);
}
using (var db = new RenamedAndReorderedContext())
{
var post = db.BlogPosts.First(p => p.Title == "T2");
Assert.AreEqual(DateTime.Today, post.Created);
}
}
public void DeleteAll_DateIsInRange_DeletesAllMatchesAndNothingElse()
{
using (var db = Context.Sql())
{
if (db.Database.Exists())
{
db.Database.Delete();
}
db.Database.Create();
db.BlogPosts.Add(BlogPost.Create("T1", DateTime.Today.AddDays(-2)));
db.BlogPosts.Add(BlogPost.Create("T2", DateTime.Today.AddDays(0)));
db.BlogPosts.Add(BlogPost.Create("T3", DateTime.Today.AddDays(2)));
db.SaveChanges();
}
using (var db = Context.Sql())
{
var lower = DateTime.Today.AddDays(-1);
var upper = DateTime.Today.AddDays(1);
var count = EFBatchOperation.For(db, db.BlogPosts).Where(b => b.Created <upper && b.Created> lower).Delete();
Assert.AreEqual(1, count);
}
using (var db = Context.Sql())
{
var posts = db.BlogPosts.ToList();
Assert.AreEqual(2, posts.Count);
Assert.AreEqual(0, posts.Count(p => p.Title == "T2"));
}
}
public void InsertAll_WithExplicitConnection_InsertsItems()
{
using (var db = Context.Sql())
{
if (db.Database.Exists())
{
db.Database.Delete();
}
db.Database.Create();
var list = new List <BlogPost>
{
BlogPost.Create("T1"),
BlogPost.Create("T2"),
BlogPost.Create("T3")
};
EFBatchOperation.For(db, db.BlogPosts).InsertAll(list, db.Database.Connection);
}
using (var db = Context.Sql())
{
Assert.AreEqual(3, db.BlogPosts.Count());
}
}
private static void DeleteDuplicateUserIncomes(UserIncomeModel data)
{
using (var tranasactionScope = new TransactionScope())
{
using (var context = new Restaurants())
{
var branch = data.Master.Select(x => x.Branch_No).FirstOrDefault();
//var date = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
var allEntities = context.User_Income
.Where(x => x.Branch_No == branch)
.ToList();
var similarEntities = data.Master.Intersect(allEntities,
new LambdaComparer <User_Income>(
(x, y) => x.Branch_No == y.Branch_No && x.Income_Date == y.Income_Date &&
x.User_ID == y.User_ID)).ToList();
foreach (var entity in similarEntities)
{
data.Master.Remove(entity);
}
EFBatchOperation.For(context, context.User_Income).InsertAll(data.Master);
tranasactionScope.Complete();
}
}
}
public SAX_REGISTRO_CONTROL LoadFileData(SAX_REGISTRO_CONTROL control)
{
SAX_REGISTRO_CONTROL registro = null;
try
{
using (var trx = new TransactionScope())
{
using (var db = new DBModelEntities())
{
db.Database.CommandTimeout = 200000;
db.Configuration.LazyLoadingEnabled = false;
var partidas = control.SAX_PARTIDAS.ToList();
control.SAX_PARTIDAS = null;
registro = base.Insert(control, true);
partidas.ForEach(c => c.RC_REGISTRO_CONTROL = registro.RC_REGISTRO_CONTROL);
EFBatchOperation.For(db, db.SAX_PARTIDAS).InsertAll(partidas, batchSize: 1500);
}
trx.Complete();
}
}
catch (Exception ex)
{
throw ex;
}
return(registro);
}
public void Merge_With_Condition()
{
Setup();
using (var db = Context.Sql())
{
var posts = db.BlogPosts.ToList();
foreach (var post in posts)
{
post.Title = post.Title.Replace("1", "4").Replace("2", "8").Replace("3", "12");
}
var insert = BlogPost.Create("TNew");
posts.Add(insert);
EFBatchOperation.For(db, db.BlogPosts).MergeAll(posts, c => c.ColumnsToIdentity(p => p.ID));
}
using (var db = Context.Sql())
{
var posts = db.BlogPosts.OrderBy(b => b.ID).ToList();
Assert.AreEqual("T4", posts[0].Title);
Assert.AreEqual("T8", posts[1].Title);
Assert.AreEqual("T12", posts[2].Title);
Assert.AreEqual("TNew", posts[3].Title);
}
}
public void DeleteAll_PropertyEquals_DeletesAllMatchesAndNothingElse()
{
using (var db = Context.Sql())
{
if (db.Database.Exists())
{
db.Database.Delete();
}
db.Database.Create();
db.BlogPosts.Add(BlogPost.Create("T1"));
db.BlogPosts.Add(BlogPost.Create("T2"));
db.BlogPosts.Add(BlogPost.Create("T2"));
db.BlogPosts.Add(BlogPost.Create("T3"));
db.SaveChanges();
}
using (var db = Context.Sql())
{
var count = EFBatchOperation.For(db, db.BlogPosts).Where(b => b.Title == "T2").Delete();
Assert.AreEqual(2, count);
}
using (var db = Context.Sql())
{
var posts = db.BlogPosts.ToList();
Assert.AreEqual(2, posts.Count);
Assert.AreEqual(0, posts.Count(p => p.Title == "T2"));
}
}
public void DeleteAll_DateIsSmallerThan_DeletesAllMatchesAndNothingElse()
{
using (var db = Context.Sql())
{
if (db.Database.Exists())
{
db.Database.Delete();
}
db.Database.Create();
db.BlogPosts.Add(BlogPost.Create("T1", DateTime.Today.AddDays(-2)));
db.BlogPosts.Add(BlogPost.Create("T2", DateTime.Today.AddDays(-1)));
db.BlogPosts.Add(BlogPost.Create("T3", DateTime.Today.AddDays(1)));
db.SaveChanges();
}
using (var db = Context.Sql())
{
var limit = DateTime.Today;
var count = EFBatchOperation.For(db, db.BlogPosts).Where(b => b.Created < limit).Delete();
Assert.AreEqual(2, count);
}
using (var db = Context.Sql())
{
var posts = db.BlogPosts.ToList();
Assert.AreEqual(1, posts.Count);
Assert.AreEqual("T3", posts.First().Title);
}
}
public void UpdateAll_IncrementDateTime_RenamedColumn()
{
RenamedAndReorderedContext.SetupTestDb();
using (var db = new RenamedAndReorderedContext())
{
db.BlogPosts.Add(new RenamedAndReorderedBlogPost {
Title = "T1", Created = new DateTime(2013, 01, 01)
});
db.BlogPosts.Add(new RenamedAndReorderedBlogPost {
Title = "T2", Created = new DateTime(2013, 02, 01)
});
db.BlogPosts.Add(new RenamedAndReorderedBlogPost {
Title = "T3", Created = new DateTime(2013, 03, 01)
});
db.SaveChanges();
}
int count;
using (var db = new RenamedAndReorderedContext())
{
count = EFBatchOperation.For(db, db.BlogPosts).Where(b => b.Title == "T2").Update(b => b.Created, b => DbFunctions.AddDays(b.Created, 1));
Assert.AreEqual(1, count);
}
using (var db = new RenamedAndReorderedContext())
{
var post = db.BlogPosts.First(p => p.Title == "T2");
Assert.AreEqual(new DateTime(2013, 02, 02), post.Created);
}
}
public IHttpActionResult MailAllWaiting(int id)
{
var @event = context.Events.SingleOrDefault(e => e.Id == id);
if (@event == null)
{
return(NotFound());
}
var list = context.Guests.Where(x => x.EventId == id && x.IsMailed == false && x.IsAttending == true && x.IsWaiting == true);
//TODO: Event should do this
foreach (var guest in list)
{
guest.IsMailed = true;
guest.MailedDate = DateTime.Now;
}
context.Events.AddOrUpdate(@event);
context.SaveChanges();
EFBatchOperation.For(context, context.Guests).UpdateAll(@event.Guests.ToList(), x => x.ColumnsToUpdate(c => c.IsMailed, c => c.MailedDate));
context.SaveChanges();
context.SaveChanges();
return(Ok(@event));
}
public void InserAll(IEnumerable <Match> entities)
{
EFBatchOperation.For(dbContext, dbContext.Matches).InsertAll(
entities,
dbContext.Database.Connection,
10000);
}
private static void BatchIteration(int count)
{
Console.WriteLine("Batch iteration with " + count + " entities");
CreateAndWarmUp();
using (var db = new Context())
{
var stop = new Stopwatch();
var comments = GetEntities(count).ToList();
stop.Start();
EFBatchOperation.For(db, db.Comments).InsertAll(comments);
stop.Stop();
Console.WriteLine("Insert entities: " + stop.ElapsedMilliseconds + "ms");
stop.Restart();
EFBatchOperation.For(db, db.Comments).Where(x => x.Text == "a").Update(x => x.Reads, x => x.Reads + 1);
stop.Stop();
Console.WriteLine("Update all entities with a: " + stop.ElapsedMilliseconds + "ms");
stop.Restart();
EFBatchOperation.For(db, db.Comments).Where(x => x.Text == "a").Delete();
stop.Stop();
Console.WriteLine("delete all entities with a: " + stop.ElapsedMilliseconds + "ms");
stop.Restart();
EFBatchOperation.For(db, db.Comments).Where(x => true).Delete();
stop.Stop();
Console.WriteLine("delete all entities: " + stop.ElapsedMilliseconds + "ms");
}
}
public void UpdateAll(IEnumerable <Event> entities)
{
EFBatchOperation.For(dbContext, dbContext.Events).UpdateAll(
entities,
x => x.ColumnsToUpdate(t => t.Name, t => t.SportId, t => t.IsCategory, t => t.CategoryId),
dbContext.Database.Connection, 10000);
}
public bool removeRegistro(int registro)
{
try
{
using (var trx = new TransactionScope())
{
using (var db = new DBModelEntities())
{
db.Database.CommandTimeout = 200000;
db.Configuration.LazyLoadingEnabled = false;
var validCreado = Convert.ToInt16(BusinessEnumerations.EstatusCarga.CREADO);
var validPorAprobar = Convert.ToInt16(BusinessEnumerations.EstatusCarga.POR_APROBAR);
EFBatchOperation.For(db, db.SAX_PARTIDAS).Where(p => p.RC_REGISTRO_CONTROL == registro && (p.PA_STATUS_PARTIDA == validCreado ||
p.PA_STATUS_PARTIDA == validPorAprobar)).Delete();
EFBatchOperation.For(db, db.SAX_REGISTRO_CONTROL).Where(p => p.RC_REGISTRO_CONTROL == registro).Delete();
}
trx.Complete();
}
}
catch (Exception ex)
{
throw ex;
}
return(true);
}
public static void InsertFromListByEFUtiInserAll(List <TargetTable1> list)
{
using (var db = new TestEntities())
{
EFBatchOperation.For(db, db.TargetTable1).InsertAll(list);
}
}
public void Merge_With_Specific_Update_Column()
{
Setup();
using (var db = Context.Sql())
{
var posts = db.BlogPosts.ToList();
foreach (var post in posts)
{
post.Title = post.Title.Replace("1", "4").Replace("2", "8").Replace("3", "12");
post.Reads = 99;
}
var insert = BlogPost.Create("TNew");
insert.Reads = 99;
posts.Add(insert);
EFBatchOperation.For(db, db.BlogPosts).MergeAll(posts, null, c => c.ColumnsToUpdate(p => p.Reads));
}
using (var db = Context.Sql())
{
var posts = db.BlogPosts.OrderBy(b => b.ID).ToList();
posts.ForEach(p => Assert.AreEqual(99, p.Reads));
Assert.AreEqual("T1", posts[0].Title);
Assert.AreEqual("T2", posts[1].Title);
Assert.AreEqual("T3", posts[2].Title);
Assert.AreEqual("TNew", posts[3].Title);
}
}
public void InsertAll_InsertsItems()
{
using (var db = Context.Sql())
{
if (db.Database.Exists())
{
db.Database.Delete();
}
db.Database.Create();
var list = new List <BlogPost>()
{
BlogPost.Create("T1"),
BlogPost.Create("T2"),
BlogPost.Create("T3")
};
EFBatchOperation.For(db, db.BlogPosts).InsertAll(list);
}
using (var db = Context.Sql())
{
Assert.AreEqual(3, db.BlogPosts.Count());
Assert.AreEqual("*****@*****.**", db.BlogPosts.First().Author.Email);
}
}
public void InsertAll_NoProvider_UsesDefaultInsert()
{
string fallbackText = null;
Configuration.DisableDefaultFallback = false;
Configuration.Log = str => fallbackText = str;
using (var db = Context.SqlCe())
{
if (db.Database.Exists())
{
db.Database.Delete();
}
db.Database.Create();
}
var list = new List <BlogPost>()
{
BlogPost.Create("T1"),
BlogPost.Create("T2"),
BlogPost.Create("T3")
};
using (var db = Context.SqlCe())
{
EFBatchOperation.For(db, db.BlogPosts).InsertAll(list);
}
using (var db = Context.SqlCe())
{
Assert.AreEqual(3, db.BlogPosts.Count());
}
Assert.IsNotNull(fallbackText);
}
public void InsertAll_InsertItems_WithTypeHierarchyBase()
{
using (var db = Context.Sql())
{
if (db.Database.Exists())
{
db.Database.Delete();
}
db.Database.Create();
List <Person> people = new List <Person>();
people.Add(Person.Build("FN1", "LN1"));
people.Add(Person.Build("FN2", "LN2"));
people.Add(Person.Build("FN3", "LN3"));
EFBatchOperation.For(db, db.People).InsertAll(people);
}
using (var db = Context.Sql())
{
var contacts = db.People.OrderBy(c => c.FirstName).ToList();
Assert.AreEqual(3, contacts.Count);
Assert.AreEqual("FN1", contacts.First().FirstName);
}
}
public void InsertAll_WrongColumnOrderAndRenamedColumn_InsertsItems()
{
using (var db = new RenamedAndReorderedContext())
{
if (db.Database.Exists())
{
db.Database.Delete();
}
db.Database.Create();
db.Database.ExecuteSqlCommand("drop table dbo.RenamedAndReorderedBlogPosts;");
db.Database.ExecuteSqlCommand(RenamedAndReorderedBlogPost.CreateTableSql());
}
using (var db = new RenamedAndReorderedContext())
{
var list = new List <RenamedAndReorderedBlogPost>()
{
RenamedAndReorderedBlogPost.Create("T1"),
RenamedAndReorderedBlogPost.Create("T2"),
RenamedAndReorderedBlogPost.Create("T3")
};
EFBatchOperation.For(db, db.BlogPosts).InsertAll(list);
}
using (var db = new RenamedAndReorderedContext())
{
Assert.AreEqual(3, db.BlogPosts.Count());
}
}
public void InsertAll_InsertItems_WithTypeHierarchy()
{
using (var db = Context.Sql())
{
if (db.Database.Exists())
{
db.Database.Delete();
}
db.Database.Create();
List <Contact> people = new List <Contact>();
people.Add(Contact.Build("FN1", "LN1", "Director"));
people.Add(Contact.Build("FN2", "LN2", "Associate"));
people.Add(Contact.Build("FN3", "LN3", "Vice President"));
EFBatchOperation.For(db, db.People).InsertAll(people);
}
using (var db = Context.Sql())
{
var contacts = db.People.OfType <Contact>().OrderBy(c => c.FirstName).ToList();
Assert.AreEqual(3, contacts.Count);
Assert.AreEqual("FN1", contacts.First().FirstName);
Assert.AreEqual("Director", contacts.First().Title);
}
}
public void InsertAll_WrongColumnOrder_InsertsItems()
{
using (var db = new ReorderedContext())
{
if (db.Database.Exists())
{
db.Database.Delete();
}
db.Database.Create();
}
using (var db = Context.Sql())
{
var list = new List <BlogPost>()
{
BlogPost.Create("T1"),
BlogPost.Create("T2"),
BlogPost.Create("T3")
};
EFBatchOperation.For(db, db.BlogPosts).InsertAll(list);
}
using (var db = Context.Sql())
{
Assert.AreEqual(3, db.BlogPosts.Count());
}
}
public void Reset()
{
IEnumerable <ClockModels> toUpdates = new List <ClockModels>();
// 获取所有数据
using (var context = new ClockInEntity())
{
toUpdates = context.ClockModels.ToList();
}
// 所有的打卡状态值都改为false
Parallel.ForEach(toUpdates,
(entity, state) =>
{
entity.ClockStateAM = false;
entity.ClockStatePM = false;
});
// 所有的过期用户flag改为False
Parallel.ForEach(toUpdates.Where(
r => r.CreatTime.AddDays(r.TotalDays).Subtract(DateTime.Now).Days <= 0
), (entity, state) => entity.flag = false);
using (var context = new ClockInEntity())
{
EFBatchOperation.For(context, context.ClockModels).UpdateAll(toUpdates, x => x.ColumnsToUpdate(
c => c.ClockStateAM,
c2 => c2.ClockStatePM,
c3 => c3.flag
));
}
}
/// <summary>
/// 写入数据库
/// </summary>
public void FastWriteToDate(attContent dc)
{
List <CHECKINOUT> newlist = new List <CHECKINOUT>();
///删除考勤
if (Checks.Count > 0)
{
var df1 = GetEmpChecks.Where(p => p.FirstCheck != null).OrderBy(p => p.CheckDate).FirstOrDefault();
var dl2 = GetEmpChecks.Where(p => p.FirstCheck != null).OrderBy(p => p.CheckDate).LastOrDefault();
if (df1 != null & dl2 != null)
{
var d1 = df1.FirstCheck.Value;
if (df1.PreCheckDay != null && df1.PreCheckDay.ShiftName != df1.ShiftName)
{
d1 = (df1.PreCheckDay.CheckDate + df1.PreCheckDay.EmpShift.EndTime).AddMinutes(10);
}
var d2 = dl2.LastCheck.Value;
var cd1 = Checks.OrderBy(p => p.CHECKTIME).First();
if (d1 != cd1.CHECKTIME)
{
if (cd1.Memoinfo == null)
{
d1 = cd1.CHECKTIME;
}
}
;
string delstr = string.Format("delete checkinout where userid={0} and checktime>='{1}' and checktime<='{2}';", Emp.USERID, d1.AddMinutes(-1), d2.AddMinutes(1));
if (delstr != "")
{
dc.Database.ExecuteSqlCommand(delstr);
}
}
}
//添加考勤
EmpChecks.ForEach(p =>
{
if (p.NewChecks.Count > 0)
{
if (p.NewChecks.Count > 0)
{
newlist.AddRange(p.NewChecks);
}
}
});
//去重
newlist.ForEach(p =>
{
if (newlist.Count(n => n.CHECKTIME == p.CHECKTIME) > 0)
{
p.CHECKTIME.AddSeconds(3);
}
});
var l = new HashSet <CHECKINOUT>(newlist);
EFBatchOperation.For(dc, dc.CHECKINOUT).InsertAll(l);
}
/// <summary>
/// Seeds a dbContext from a CSV file that will be read from the specified stream using BulkInsert operation
/// </summary>
/// <typeparam name="T">The type of entity to load</typeparam>
/// <param name="dbContext">The dbContext to populate</param>
/// <param name="stream">The stream containing the CSV file contents</param>
/// <param name="additionalMapping">Any additonal complex mappings required</param>
/// <returns>Returns the read entities from the CSV resource. <remarks>It does not return the inserted entities</remarks></returns>
public static IList <T> SeedFromStreamWithBulkInsert <T>(this DbContext dbContext, Stream stream, params CsvColumnMapping <T>[] additionalMapping) where T : class
{
IList <T> entities = GetEntitiesFromStream(stream, additionalMapping);
EFBatchOperation.For(dbContext, dbContext.Set <T>()).InsertAll(entities);
return(entities);
}
public void UpdateAll(IEnumerable <Match> entities)
{
EFBatchOperation.For(dbContext, dbContext.Matches).UpdateAll(
entities,
x => x.ColumnsToUpdate(t => t.EventId, t => t.MatchType, t => t.Name, t => t.StartDate),
dbContext.Database.Connection,
10000);
}
public IHttpActionResult UpdateTaxItems(int constituentId, List <TaxItem> vm)
{
var list = vm;
EFBatchOperation.For(db, db.TaxItems).UpdateAll(list, x => x.ColumnsToUpdate(c => c.Amount, c => c.DonationDate, c => c.TaxYear));
return(Ok());
}
