public void CreateDatabaseScript()
{
using (testEntities ctx = new testEntities())
{
string s = ctx.CreateDatabaseScript();
}
}
public void AverageWithGrouping()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT AVG(Freight) FROM Orders GROUP BY ShopId", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = "SELECT AVG(o.Freight) FROM Orders AS o GROUP BY o.Shop.Id";
ObjectQuery<DbDataRecord> q = context.CreateQuery<DbDataRecord>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.AverageWithGrouping);
foreach (object x in q)
{
string s = x.GetType().ToString();
}
int i = 0;
foreach (var freight in q)
{
// Assert.AreEqual(Convert.ToInt32(dt.Rows[i++][0]), Convert.ToInt32(freight));
}
}
}
public void DoubleValuesNonEnglish()
{
CultureInfo curCulture = Thread.CurrentThread.CurrentCulture;
CultureInfo curUICulture = Thread.CurrentThread.CurrentUICulture;
CultureInfo newCulture = new CultureInfo("da-DK");
Thread.CurrentThread.CurrentCulture = newCulture;
Thread.CurrentThread.CurrentUICulture = newCulture;
try
{
using (testEntities context = new testEntities())
{
Child c = new Child();
c.Id = 20;
c.EmployeeID = 1;
c.FirstName = "Bam bam";
c.LastName = "Rubble";
c.BirthWeight = 8.65;
c.Modified = DateTime.Now;
context.AddToChildren(c);
context.SaveChanges();
}
}
finally
{
Thread.CurrentThread.CurrentCulture = curCulture;
Thread.CurrentThread.CurrentUICulture = curUICulture;
}
}
public void SimpleJoinWithPredicate()
{
MySqlDataAdapter da = new MySqlDataAdapter(
@"SELECT b.id,b.name,a.name as author_name from books b JOIN
authors a ON b.author_id=a.id WHERE b.pages > 300", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
var q = from b in context.Books
join a in context.Authors
on b.Author.Id equals a.Id
where b.Pages > 300
select new
{
bookId = b.Id,
bookName = b.Name,
authorName = a.Name
};
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.SimpleJoinWithPredicate);
int i = 0;
foreach (var o in q)
Assert.AreEqual(dt.Rows[i++][0], o.bookId);
Assert.AreEqual(dt.Rows.Count, i);
}
}
public void ConversionToLike()
{
// Generates queries for each LIKE + wildcards case and checks SQL generated.
using (testEntities ctx = new testEntities())
{
// Like 'pattern%'
var q = from c in ctx.Employees where c.FirstName.StartsWith( "B" )
orderby c.FirstName select c;
string query = q.ToTraceString();
CheckSql(query, SQLSyntax.StartsWithTranslatedToLike);
Assert.AreEqual(2, q.Count());
Assert.AreEqual("Barney", q.First().FirstName);
Assert.AreEqual("Betty", q.Skip(1).First().FirstName);
// Like '%pattern%'
q = from c in ctx.Employees where c.FirstName.Contains("r")
orderby c.FirstName select c;
query = q.ToTraceString();
CheckSql(query, SQLSyntax.ContainsTranslatedToLike);
Assert.AreEqual(2, q.Count());
Assert.AreEqual("Barney", q.First().FirstName);
Assert.AreEqual("Fred", q.Skip(1).First().FirstName);
// Like '%pattern'
q = from c in ctx.Employees
where c.FirstName.EndsWith("y")
orderby c.FirstName
select c;
query = q.ToTraceString();
CheckSql(query, SQLSyntax.EndsWithTranslatedToLike);
Assert.AreEqual(3, q.Count());
Assert.AreEqual("Barney", q.First().FirstName);
Assert.AreEqual("Betty", q.Skip(1).First().FirstName);
Assert.AreEqual("Scooby", q.Skip(2).First().FirstName);
}
}
public void CanGroupByMultipleColumns()
{
MySqlDataAdapter adapter = new MySqlDataAdapter(
"SELECT Name, COUNT(Id) as Count FROM Companies GROUP BY Name, NumEmployees, DateBegan", conn);
DataTable table = new DataTable();
adapter.Fill(table);
using (testEntities context = new testEntities())
{
var companies = from c in context.Companies
group c by new { c.Name, c.NumEmployees, c.DateBegan } into cgroup
select new
{
Name = cgroup.Key.Name,
Count = cgroup.Count()
};
string sql = companies.ToTraceString();
CheckSql(sql, SQLSyntax.CanGroupByMultipleColumns);
int i = 0;
foreach (var company in companies)
{
Assert.AreEqual(table.Rows[i][0], company.Name);
Assert.AreEqual(table.Rows[i][1], company.Count);
i++;
}
}
}
public void SimpleJoin()
{
MySqlDataAdapter da = new MySqlDataAdapter(
@"SELECT b.id,b.name,a.name as author_name from books b JOIN
authors a ON b.author_id=a.id", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
var q = from b in context.Books
join a in context.Authors
on b.Author.Id equals a.Id
select new
{
bookId = b.Id,
bookName = b.Name,
authorName = a.Name
};
int i = 0;
foreach (var o in q)
Assert.AreEqual(dt.Rows[i++][0], o.bookId);
Assert.AreEqual(dt.Rows.Count, i);
}
}
public void JoinOfNestedUnionsWithLimit()
{
using (testEntities context = new testEntities())
{
var q = context.Books.Include("Author");
q = q.Include("Publisher");
q = q.Include("Publisher.Books");
string sql = q.ToTraceString();
var i = 0;
foreach (var o in q.Where(p => p.Id > 0).OrderBy(p => p.Name).ThenByDescending(p => p.Id).Skip(0).Take(32).ToList())
{
switch (i)
{
case 0:
Assert.AreEqual(5, o.Id);
Assert.AreEqual("Debt of Honor", o.Name);
break;
case 1:
Assert.AreEqual(1, o.Id);
Assert.AreEqual("Debt of Honor", o.Name);
break;
case 4:
Assert.AreEqual(3, o.Id);
Assert.AreEqual("Rainmaker", o.Name);
break;
}
i++;
}
}
}
public void UnsignedValues()
{
using (testEntities context = new testEntities())
{
var row = context.Children.First();
context.Detach(row);
context.Attach(row);
}
}
public void FirstSimple()
{
MySqlCommand cmd = new MySqlCommand("SELECT id FROM orders", conn);
int id = (int)cmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
var q = from o in context.Orders
select o;
Order order = q.First() as Order;
Assert.AreEqual(id, order.Id);
}
}
public void FirstPredicate()
{
MySqlCommand cmd = new MySqlCommand("SELECT id FROM orders WHERE freight > 100", conn);
int id = (int)cmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
var q = from o in context.Orders
where o.Freight > 100
select o;
Order order = q.First() as Order;
Assert.AreEqual(id, order.Id);
}
}
public void BigCountSimple()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT COUNT(*) FROM Toys", conn);
object trueCount = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string sql = "SELECT VALUE BigCount(t.Id) FROM Toys AS t";
ObjectQuery<Int32> q = context.CreateQuery<Int32>(sql);
foreach (int count in q)
Assert.AreEqual(trueCount, count);
}
}
public void CanInsertRowWithDefaultTimeStamp()
{
using (testEntities context = new testEntities())
{
// The default timestamp is in the CreatedDate column.
Product product = new Product();
product.Name = "Coca Cola";
context.AddToProducts(product);
context.SaveChanges();
Assert.AreEqual(DateTime.Today.Day, product.CreatedDate.Day);
}
}
public void YearMonthDay()
{
using (testEntities context = new testEntities())
{
ObjectQuery<DbDataRecord> q = context.CreateQuery<DbDataRecord>(
@"SELECT c.DateBegan, Year(c.DateBegan), Month(c.DateBegan), Day(c.DateBegan)
FROM Companies AS c WHERE c.Id=1");
foreach (DbDataRecord record in q)
{
Assert.AreEqual(1996, record[1]);
Assert.AreEqual(11, record[2]);
Assert.AreEqual(15, record[3]);
}
}
}
public void Bitwise()
{
using (testEntities context = new testEntities())
{
ObjectQuery<Int32> q = context.CreateQuery<Int32>("BitwiseAnd(255,15)");
foreach (int i in q)
Assert.AreEqual(15, i);
q = context.CreateQuery<Int32>("BitwiseOr(240,31)");
foreach (int i in q)
Assert.AreEqual(255, i);
q = context.CreateQuery<Int32>("BitwiseXor(255,15)");
foreach (int i in q)
Assert.AreEqual(240, i);
}
}
public void SimpleSelectWithFilter()
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Toys WHERE minage=4", conn);
DataTable toys = new DataTable();
da.Fill(toys);
int i = 0;
using (testEntities context = new testEntities())
{
var query = context.CreateQuery<Toy>("SELECT VALUE t FROM Toys AS t WHERE t.MinAge=4");
foreach (Toy t in query)
{
Assert.AreEqual(toys.Rows[i++]["name"], t.Name);
}
}
}
public void Distinct()
{
using (testEntities context = new testEntities())
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Companies LIMIT 2", conn);
DataTable dt = new DataTable();
da.Fill(dt);
int i = 0;
var query = context.Companies.Top("2");
foreach (Company c in query)
{
Assert.AreEqual(dt.Rows[i++]["id"], c.Id);
}
}
}
public void SimpleDeleteAllRows()
{
using (testEntities context = new testEntities())
{
foreach (Toy t in context.Toys)
context.DeleteObject(t);
context.SaveChanges();
EntityConnection ec = context.Connection as EntityConnection;
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM toys",
(MySqlConnection)ec.StoreConnection);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(0, dt.Rows.Count);
}
}
public void UpdateAllRows()
{
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM toys", conn);
object count = cmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
foreach (Toy t in context.Toys)
t.Name = "Top";
context.SaveChanges();
}
cmd.CommandText = "SELECT COUNT(*) FROM Toys WHERE name='Top'";
object newCount = cmd.ExecuteScalar();
Assert.AreEqual(count, newCount);
}
public void CurrentDateTime()
{
DateTime current = DateTime.Now;
using (testEntities context = new testEntities())
{
ObjectQuery<DateTime> q = context.CreateQuery<DateTime>("CurrentDateTime()");
foreach (DateTime dt in q)
{
Assert.AreEqual(current.Year, dt.Year);
Assert.AreEqual(current.Month, dt.Month);
Assert.AreEqual(current.Day, dt.Day);
// we don't check time as that will be always be different
}
}
}
public void OrderBySimple()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT id FROM Companies c ORDER BY c.Name", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string sql = "SELECT VALUE c FROM Companies AS c ORDER BY c.Name";
ObjectQuery<Company> query = context.CreateQuery<Company>(sql);
int i = 0;
foreach (Company c in query)
Assert.AreEqual(dt.Rows[i++][0], c.Id);
}
}
public void Any()
{
MySqlDataAdapter da = new MySqlDataAdapter(
@"SELECT a.id FROM authors a WHERE NOT EXISTS(SELECT * FROM books b WHERE b.author_id=a.id)", conn);
DataTable dt = new DataTable();
da.Fill(dt);
int i = 0;
// find all authors that are in our db with no books
using (testEntities context = new testEntities())
{
var authors = from a in context.Authors where !a.Books.Any() select a;
string sql = authors.ToTraceString();
foreach (Author a in authors)
Assert.AreEqual(dt.Rows[i++]["id"], a.Id);
}
}
public void CountWithPredicate()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT COUNT(*) FROM Toys AS t WHERE t.MinAge > 3", conn);
object trueCount = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string eSql = "SELECT VALUE Count(t.Id) FROM Toys AS t WHERE t.MinAge > 3";
ObjectQuery<Int32> q = context.CreateQuery<Int32>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.CountWithPredicate);
foreach (int count in q)
Assert.AreEqual(trueCount, count);
}
}
public void AverageSimple()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT AVG(minAge) FROM Toys", conn);
object avgAge = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string eSql = "SELECT VALUE Avg(t.MinAge) FROM Toys AS t";
ObjectQuery<Decimal> q = context.CreateQuery<Decimal>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.AverageSimple);
foreach (Decimal r in q)
Assert.AreEqual(avgAge, r);
}
}
public void Skip()
{
using (testEntities context = new testEntities())
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Companies LIMIT 3,20", conn);
DataTable dt = new DataTable();
da.Fill(dt);
int i = 0;
var query = context.Companies.Skip("it.Id", "3");
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.Skip);
foreach (Company c in query)
{
Assert.AreEqual(dt.Rows[i++]["id"], c.Id);
}
}
}
public void SimpleSelect()
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Toys", conn);
DataTable toys = new DataTable();
da.Fill(toys);
int i = 0;
using (testEntities context = new testEntities())
{
var query = context.CreateQuery<Toy>("SELECT VALUE c FROM Toys AS c");
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.SimpleSelect);
foreach (Toy t in query)
{
Assert.AreEqual(toys.Rows[i++]["name"], t.Name);
}
}
}
public void SelectWithComplexType()
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT c.LastName FROM Employees AS c WHERE c.Age > 20", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = @"SELECT c.LastName FROM Employees AS c WHERE c.Age > 20";
ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.SelectWithComplexType);
int i = 0;
foreach (DbDataRecord s in query)
Assert.AreEqual(dt.Rows[i++][0], s.GetString(0));
}
}
public void SimpleDeleteRowByParameter()
{
using (testEntities context = new testEntities())
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM toys WHERE minage=3", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.IsTrue(dt.Rows.Count > 0);
ObjectQuery<Toy> toys = context.Toys.Where("it.MinAge = @age", new ObjectParameter("age", 3));
foreach (Toy t in toys)
context.DeleteObject(t);
context.SaveChanges();
dt.Clear();
da.Fill(dt);
Assert.AreEqual(0, dt.Rows.Count);
}
}
public void UnionAll()
{
using (testEntities context = new testEntities())
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT t.Id FROM Toys t UNION ALL SELECT c.Id FROM Companies c", conn);
DataTable dt = new DataTable();
da.Fill(dt);
string entitySQL = @"(SELECT t.Id, t.Name FROM Toys AS t)
UNION ALL (SELECT c.Id, c.Name FROM Companies AS c)";
ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(entitySQL);
int i = 0;
foreach (DbDataRecord r in query)
{
i++;
}
Assert.AreEqual(dt.Rows.Count, i);
}
}
public void OrderByWithPredicate()
{
using (testEntities context = new testEntities())
{
using (EntityConnection ec = context.Connection as EntityConnection)
{
ec.Open();
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT id FROM Companies c WHERE c.NumEmployees > 100 ORDER BY c.Name", conn);
DataTable dt = new DataTable();
da.Fill(dt);
string sql = "SELECT VALUE c FROM Companies AS c WHERE c.NumEmployees > 100 ORDER BY c.Name";
ObjectQuery<Company> query = context.CreateQuery<Company>(sql);
int i = 0;
foreach (Company c in query)
Assert.AreEqual(dt.Rows[i++][0], c.Id);
}
}
}
