public void FutureCountTest()
{
var db = new TrackerEntities();
// build up queries
string emailDomain = "@battlestar.com";
var q1 = db.Users
.Where(p => p.EmailAddress.EndsWith(emailDomain))
.Future();
string search = "Earth";
var q2 = db.Tasks
.Where(t => t.Summary.Contains(search))
.FutureCount();
// should be 2 queries
//Assert.AreEqual(2, db.FutureQueries.Count);
// this triggers the loading of all the future queries
var users = q1.ToList();
Assert.IsNotNull(users);
// should be cleared at this point
//Assert.AreEqual(0, db.FutureQueries.Count);
// this should already be loaded
Assert.IsTrue(((IFutureQuery)q2).IsLoaded);
int count = q2;
Assert.AreNotEqual(count, 0);
}
public void Add <T>(T obj) where T : class
{
using (var ctx = new TrackerEntities())
{
ctx.Set <T>().Add(obj);
}
}
public void InsertBulkAsync()
{
using (var db = new TrackerEntities())
{
_InsertBulk(db, true);
}
}
public void InsertNoTransaction()
{
using (var db = new TrackerEntities())
{
_Insert(db);
}
}
public void NoTransactionObjectContext()
{
Locator.Current.Register <IBatchRunner>(() => new MySqlBatchRunner());
try
{
using (var db = new TrackerEntities())
{
db.Database.Log = s => System.Diagnostics.Trace.WriteLine(s);
string emailDomain = "@test.com";
int count = db.users
.Where(u => u.EmailAddress.EndsWith(emailDomain))
.Update(u => new user {
IsApproved = false, LastActivityDate = DateTime.Now
});
count = db.users
.Where(u => u.EmailAddress.EndsWith(emailDomain))
.Delete();
}
}
finally
{
Locator.Current.Register <IBatchRunner>(() => new SqlServerBatchRunner());
}
}
public void GetEntityMap()
{
var db = new TrackerEntities();
var metadata = db.MetadataWorkspace;
var map = db.Tasks.GetEntityMap <Task>();
}
public void Delete()
{
var db = new TrackerEntities();
string emailDomain = "@test.com";
int count = db.Users
.Where(u => u.EmailAddress.EndsWith(emailDomain))
.Delete();
}
public void InsertInTransaction()
{
using (var db = new TrackerEntities())
using (var tx = db.Database.BeginTransaction())
{
_Insert(db);
tx.Commit();
}
}
public void InsertInTransactionScope()
{
using (var tx = new TransactionScope())
using (var db = new TrackerEntities())
{
_Insert(db);
tx.Complete();
}
}
public void GetEntityMapTask()
{
var db = new TrackerEntities();
var metadata = db.MetadataWorkspace;
var map = db.Tasks.GetEntityMap <Task>();
Assert.AreEqual("[dbo].[Task]", map.TableName);
}
public void Update()
{
var db = new TrackerEntities();
string emailDomain = "@test.com";
int count = db.Users.Update(
u => u.EmailAddress.EndsWith(emailDomain),
u => new User {
IsApproved = false, LastActivityDate = DateTime.Now
});
}
public List <T> GetProducts <T>() where T : class
{
var Products = new List <T>();
using (var ctx = new TrackerEntities())
{
Products = ctx.Set <T>().ToList();
}
return(Products);
}
public void DeleteWhereWithExpressionContainingNullParameter()
{
// This test verifies that the delete is processed correctly when the where expression uses a parameter with a null parameter
var db = new TrackerEntities();
string emailDomain = "@test.com";
string optionalComparisonString = null;
int count = db.Users
.Where(u => (string.IsNullOrEmpty(optionalComparisonString) || u.AvatarType == optionalComparisonString) && u.EmailAddress.EndsWith(emailDomain))
.Delete();
}
public void UpdateConditional()
{
var db = new TrackerEntities();
string emailDomain = "@test.com";
int count = db.Users
.Where(u => u.EmailAddress.EndsWith(emailDomain))
.Update(u => new User()
{
EmailAddress = u.EmailAddress.EndsWith(emailDomain) ? u.EmailAddress : emailDomain,
LastActivityDate = DateTime.Now
});
}
private void _Insert(TrackerEntities db)
{
db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
db.ProductSummaries.Delete();
var query = from product in db.Products
join item2 in (
from item in db.Items
group item by item.ProductId into grItem
select new
{
ProductId = grItem.Key,
AvgPrice = grItem.Average(x => x.ListPrice + x.UnitCost)
}
) on product.ProductId equals item2.ProductId into items
from item3 in items.DefaultIfEmpty()
select new ProductSummary2
{
ProductId = product.ProductId,
Name = product.Name,
AvgPrice = item3.AvgPrice ?? 0
};
db.ProductSummaries.Insert(query);
var source = query.ToArray();
var result = db.ProductSummaries.ToArray();
for (int i = 0; i < source.Length; i++)
{
source[i].AvgPrice = Math.Round(source[i].AvgPrice, 2, MidpointRounding.AwayFromZero); //In database, only two digits after decimal point
source[i].Verified = true; //Verified was not set in query. In database, its default value is true (1)
}
Assert.True(result.OrderBy(i => i.ProductId).SequenceEqual(source.OrderBy(i => i.ProductId), new ProductSummaryComparer()));
db.Item_2.Delete();
var query2 = db.Items.Where(item => item.ListPrice / item.UnitCost >= 5);
db.Item_2.Insert(query2);
var source2 = query2.ToArray().OrderBy(i => i.ItemId);
var result2 = db.Item_2.ToArray().Select(i => ItemComparer.GetItem(i)).OrderBy(i => i.ItemId);
Assert.True(result2.SequenceEqual(source2, new ItemComparer()));
db.Item_2.Delete();
var query3 = from item in db.Items where item.ProductId == "K9-RT-02" select item;
db.Item_2.Insert(query3);
var source3 = query3.ToArray().OrderBy(item => item.ItemId);
var result3 = db.Item_2.ToArray().Select(i => ItemComparer.GetItem(i)).OrderBy(item => item.ItemId);
Assert.True(result3.SequenceEqual(source3, new ItemComparer()));
}
public void UpdateWithExpressionContainingNullParameter()
{
// This test verifies that the update is interpreted correctly when the where expression uses a parameter with a null parameter
var db = new TrackerEntities();
string emailDomain = "@test.com";
string optionalComparisonString = null;
int count = db.Users
.Where(u => u.EmailAddress.EndsWith(emailDomain) && (string.IsNullOrEmpty(optionalComparisonString) || u.AvatarType == optionalComparisonString))
.Update(u => new User {
IsApproved = false, LastActivityDate = DateTime.Now
});
}
public void NoTransactionObjectContext()
{
using (var db = new TrackerEntities())
{
string emailDomain = "@test.com";
int count = db.Users.Update(
u => u.EmailAddress.EndsWith(emailDomain),
u => new User {
IsApproved = false, LastActivityDate = DateTime.Now
});
count = db.Users.Delete(u => u.EmailAddress.EndsWith(emailDomain));
}
}
public void PageTest()
{
var db = new TrackerEntities();
// base query
var q = db.Tasks
.Where(p => p.PriorityId == 2)
.OrderByDescending(t => t.CreatedDate);
// get total count
var q1 = q.FutureCount();
// get first page
var q2 = q.Skip(0).Take(10).Future();
// triggers sql execute as a batch
var tasks = q2.ToList();
int total = q1.Value;
Assert.IsNotNull(tasks);
}
public void FutureValueReverseTest()
{
var db = new TrackerEntities();
// build up queries
string emailDomain = "@battlestar.com";
var q1 = db.Users
.Where(p => p.EmailAddress.EndsWith(emailDomain))
.FutureFirstOrDefault();
string search = "Earth";
var q2 = db.Tasks
.Where(t => t.Summary.Contains(search))
.FutureCount();
// duplicate query except count
var q3 = db.Tasks
.Where(t => t.Summary.Contains(search))
.Future();
// should be 3 queries
//Assert.AreEqual(3, db.FutureQueries.Count);
// access q2 first to trigger loading, testing loading from FutureCount
// this triggers the loading of all the future queries
var count = q2.Value;
Assert.AreNotEqual(count, 0);
// should be cleared at this point
//Assert.AreEqual(0, db.FutureQueries.Count);
// this should already be loaded
Assert.IsTrue(((IFutureQuery)q1).IsLoaded);
var users = q1.Value;
Assert.IsNotNull(users);
var tasks = q3.ToList();
Assert.IsNotNull(tasks);
}
public void BeginTransactionObjectContext()
{
using (var db = new TrackerEntities())
using (var tx = db.Database.BeginTransaction())
{
string emailDomain = "@test.com";
int count = db.Users
.Where(u => u.EmailAddress.EndsWith(emailDomain))
.Update(u => new User {
IsApproved = false, LastActivityDate = DateTime.Now
});
count = db.Users
.Where(u => u.EmailAddress.EndsWith(emailDomain))
.Delete();
tx.Commit();
}
}
public void Delete()
{
var db = new TrackerEntities();
string emailDomain = "@test.com";
int count = db.Users.Delete(u => u.Email.Contains(emailDomain));
}
/*** MySQL seems not to support TransactionScope ***/
//[Fact]
//public void TransactionScopeObjectContext()
//{
// using (var tx = new TransactionScope())
// using (var db = new TrackerEntities())
// {
// string emailDomain = "@test.com";
// int count = db.users
// .Where(u => u.EmailAddress.EndsWith(emailDomain))
// .Update(u => new user { IsApproved = false, LastActivityDate = DateTime.Now });
// count = db.users
// .Where(u => u.EmailAddress.EndsWith(emailDomain))
// .Delete();
// tx.Complete();
// }
//}
private void _Insert(TrackerEntities db, bool isAsync = false)
{
Locator.Current.Register <IBatchRunner>(() => new MySqlBatchRunner());
try
{
db.Database.Log = s => System.Diagnostics.Trace.WriteLine(s);
db.productsummaries.Delete();
var query = from product in db.products
join item2 in (
from item in db.items
group item by item.ProductId into grItem
select new
{
ProductId = grItem.Key,
AvgPrice = grItem.Average(x => x.ListPrice + x.UnitCost)
}
) on product.ProductId equals item2.ProductId into items
from item3 in items.DefaultIfEmpty()
select new ProductSummary2
{
ProductId = product.ProductId,
Name = product.Name,
AvgPrice = item3.AvgPrice ?? 0
};
if (isAsync)
{
db.productsummaries.InsertAsync(query).Wait();
}
else
{
db.productsummaries.Insert(query);
}
var source = query.ToArray();
var result = db.productsummaries.ToArray();
for (int i = 0; i < source.Length; i++)
{
source[i].AvgPrice = Math.Round(source[i].AvgPrice, 2, MidpointRounding.AwayFromZero); //In database, only two digits after decimal point
source[i].Verified = true; //Verified was not set in query. In database, its default value is true (1)
}
Assert.True(result.OrderBy(i => i.ProductId).SequenceEqual(source.OrderBy(i => i.ProductId), new ProductSummaryComparer()));
db.item_2.Delete();
var query2 = db.items.Where(item => item.ListPrice / item.UnitCost >= 5);
if (isAsync)
{
db.item_2.InsertAsync(query2).Wait();
}
else
{
db.item_2.Insert(query2);
}
var source2 = query2.ToArray().OrderBy(i => i.ItemId);
var result2 = db.item_2.ToArray().Select(i => ItemComparer.GetItem(i)).OrderBy(i => i.ItemId);
Assert.True(result2.SequenceEqual(source2, new ItemComparer()));
db.item_2.Delete();
//var query3 = from item in db.items where item.ProductId == "K9-RT-02" select item; //Using MySQL provider, ObjectQuery.Parameters is not filled if its parameter is constant
string productId = "K9-RT-02";
var query3 = from item in db.items where item.ProductId == productId select item;
if (isAsync)
{
db.item_2.InsertAsync(query3).Wait();
}
else
{
db.item_2.Insert(query3);
}
var source3 = query3.ToArray().OrderBy(item => item.ItemId);
var result3 = db.item_2.ToArray().Select(i => ItemComparer.GetItem(i)).OrderBy(item => item.ItemId);
Assert.True(result3.SequenceEqual(source3, new ItemComparer()));
}
finally
{
Locator.Current.Register <IBatchRunner>(() => new SqlServerBatchRunner());
}
}
private void _InsertBulk(TrackerEntities db, bool isAsync = false)
{
db.Database.Log = s => System.Diagnostics.Trace.WriteLine(s);
db.ProductSummaries.Delete();
db.Database.ExecuteSqlCommand("TRUNCATE TABLE ATable");
//Create 1000 random records
Random r = new Random();
int n = 1000;
var entities = new List <ProductSummary>();
int m = (int)Math.Pow(10, 8) - 1;
for (int i = 1; i <= n; i++)
{
entities.Add(new ProductSummary
{
ProductId = i.ToString(),
Name = Guid.NewGuid().ToString().Replace("-", "").PadRight(80).Substring(0, 80),
AvgPrice = r.Next(-m, m),
Verified = r.Next() % 2 == 0,
Date = DateTime.Now.AddDays(r.Next(-100, 100)),
});
}
var start = DateTime.Now;
if (isAsync)
{
db.ProductSummaries.InsertAsync(entities).Wait();
}
else
{
db.ProductSummaries.Insert(entities);
}
/**
* Compare the execution time with these two lines of commented code below
* // * **/
//db.ProductSummaries.AddRange(entities);
//db.SaveChanges();
System.Diagnostics.Trace.WriteLine("***** Executing bulk insert (" + n + " items) takes "
+ DateTime.Now.Subtract(start).ToString(@"hh\:mm\:ss\.ffffff"));
Assert.True(db.ProductSummaries.Count() == n);
/** Dealing with Identity Column **/
var entities2 = new List <ATable>();
for (int i = 1; i <= n; i++)
{
entities2.Add(new ATable
{
A = r.NextDouble(),
B = Guid.NewGuid().ToString().Replace("-", "").PadRight(50).Substring(0, 50),
//C is an Identity column
D = DateTime.Now.AddDays(r.Next(-100, 100)),
E = r.Next() % 2 == 0,
});
}
if (isAsync)
{
db.ATables.InsertAsync(entities2).Wait();
}
else
{
db.ATables.Insert(entities2);
}
Assert.True(db.ATables.Count() == n);
}
// Initialize and open the database connection
static DatabaseManager()
{
entities = new TrackerEntities();
entities.Database.Connection.Open();
}
