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 CreateDatabaseScript()
{
using (testEntities ctx = new testEntities())
{
string s = ctx.CreateDatabaseScript();
}
}
public void CanGroupBySingleColumn()
{
MySqlDataAdapter adapter = new MySqlDataAdapter(
"SELECT Name, COUNT(Id) as Count FROM Companies GROUP BY Name", conn);
DataTable table = new DataTable();
adapter.Fill(table);
using (testEntities context = new testEntities())
{
var companies = from c in context.Companies
group c by c.Name into cgroup
select new
{
Name = cgroup.Key,
Count = cgroup.Count()
};
string sql = companies.ToTraceString();
CheckSql(sql, SQLSyntax.CanGroupBySingleColumn);
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 AverageWithGrouping()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT AVG(Freight) FROM Orders GROUP BY StoreId", 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.Store.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 GuidType()
{
using (testEntities context = new testEntities())
{
DataTypeTest dtt = context.DataTypeTests.First();
string guidAsChar = dtt.idAsChar;
Assert.AreEqual(0, String.Compare(guidAsChar, dtt.id.ToString(), true));
Assert.AreEqual(0, String.Compare(guidAsChar, dtt.id2.ToString(), true));
}
}
public void Delete()
{
using (testEntities context = new testEntities())
{
foreach (Book b in context.Books)
context.DeleteObject(b);
context.SaveChanges();
}
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Books", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(0, dt.Rows.Count);
}
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 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 HourMinuteSecond()
{
using (testEntities context = new testEntities())
{
ObjectQuery<DbDataRecord> q = context.CreateQuery<DbDataRecord>(
@"SELECT c.DateBegan, Hour(c.DateBegan), Minute(c.DateBegan), Second(c.DateBegan)
FROM Companies AS c WHERE c.Id=1");
foreach (DbDataRecord record in q)
{
Assert.AreEqual(5, record[1]);
Assert.AreEqual(18, record[2]);
Assert.AreEqual(23, 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 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 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 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 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 JoinOnRightSideAsDerivedTable()
{
using (testEntities context = new testEntities())
{
var q = from child in context.Children
join emp in context.Employees
on child.EmployeeID equals emp.Id
where child.BirthWeight > 7
select child;
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.JoinOnRightSideAsDerivedTable);
foreach (Child c in q)
{
}
}
}
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 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 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 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();
CheckSql(sql, SQLSyntax.Any);
foreach (Author a in authors)
Assert.AreEqual(dt.Rows[i++]["id"], a.Id);
}
}
public void InsertSingleRow()
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM companies", conn);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow lastRow = dt.Rows[dt.Rows.Count - 1];
int lastId = (int)lastRow["id"];
DateTime dateBegan = DateTime.Now;
using (testEntities context = new testEntities())
{
Company c = new Company();
c.Id = 23;
c.Name = "Yoyo";
c.NumEmployees = 486;
c.DateBegan = dateBegan;
c.Address.Address = "212 My Street.";
c.Address.City = "Helena";
c.Address.State = "MT";
c.Address.ZipCode = "44558";
context.AddToCompanies(c);
int result = context.SaveChanges();
DataTable afterInsert = new DataTable();
da.Fill(afterInsert);
lastRow = afterInsert.Rows[afterInsert.Rows.Count - 1];
Assert.AreEqual(dt.Rows.Count + 1, afterInsert.Rows.Count);
Assert.AreEqual(lastId+1, lastRow["id"]);
Assert.AreEqual("Yoyo", lastRow["name"]);
Assert.AreEqual(486, lastRow["numemployees"]);
DateTime insertedDT = (DateTime)lastRow["dateBegan"];
Assert.AreEqual(dateBegan.Date, insertedDT.Date);
Assert.AreEqual("212 My Street.", lastRow["address"]);
Assert.AreEqual("Helena", lastRow["city"]);
Assert.AreEqual("MT", lastRow["state"]);
Assert.AreEqual("44558", lastRow["zipcode"]);
}
}
public void Exists()
{
MySqlDataAdapter da = new MySqlDataAdapter(
@"SELECT c.* FROM Companies c WHERE EXISTS
(SELECT * FROM Toys t WHERE t.SupplierId=c.Id && t.MinAge < 4)", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = @"SELECT VALUE c FROM Companies AS c WHERE EXISTS(
SELECT p FROM c.Toys AS p WHERE p.MinAge < 4)";
ObjectQuery<Company> query = context.CreateQuery<Company>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.Exists);
int i = 0;
foreach(Company c in query)
Assert.AreEqual(dt.Rows[i++]["id"], c.Id);
}
}
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);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.UnionAll);
int i = 0;
foreach (DbDataRecord r in query)
{
i++;
}
Assert.AreEqual(dt.Rows.Count, i);
}
}
public void CommandTimeout()
{
string connectionString = String.Format(
"metadata=res://*/TestModel.csdl|res://*/TestModel.ssdl|res://*/TestModel.msl;provider=MariaDB.Data.MySqlClient; provider connection string=\"{0};default command timeout=5\"", GetConnectionString(true));
EntityConnection connection = new EntityConnection(connectionString);
using (testEntities context = new testEntities(connection))
{
Author a = new Author();
a.Id = 66; // special value to indicate the routine should take 30 seconds
a.Name = "Test name";
a.Age = 44;
context.AddToAuthors(a);
try
{
context.SaveChanges();
Assert.Fail("This should have timed out");
}
catch (Exception ex)
{
string s = ex.Message;
}
}
}
public void JoinOfUnionsOnRightSideofJoin()
{
using (testEntities context = new testEntities())
{
string eSql = @"SELECT c.Id, c.Name, Union1.Id, Union1.Name,
Union2.Id, Union2.Name FROM
testEntities.Companies AS c JOIN (
((SELECT t.Id, t.Name FROM testEntities.Toys as t)
UNION ALL
(SELECT s.Id, s.Name FROM testEntities.Stores as s)) AS Union1
JOIN
((SELECT a.Id, a.Name FROM testEntities.Authors AS a)
UNION ALL
(SELECT b.Id, b.Name FROM testEntities.Books AS b)) AS Union2
ON Union1.Id = Union2.Id) ON c.Id = Union1.Id";
ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.JoinOfUnionsOnRightSideOfJoin);
foreach (DbDataRecord record in query)
{
Assert.AreEqual(6, record.FieldCount);
}
}
}
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 UnionAllWithBitColumnsDoesNotThrow()
{
using (testEntities entities = new testEntities())
{
// Here, Computer is the base type of DesktopComputer, LaptopComputer and TabletComputer.
// LaptopComputer and TabletComputer include the bit fields that would provoke
// an InvalidCastException (byte[] to bool) when participating in a UNION
// created internally by the Connector/Net entity framework provider.
var computers = from c in entities.Computers
select c;
foreach (Computer computer in computers)
{
Assert.NotNull(computer);
Assert.IsTrue(computer.Id > 0);
}
}
}
public void SimpleSelectWithParam()
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Toys WHERE minage>3", 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>@age");
query.Parameters.Add(new ObjectParameter("age", 3));
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.SimpleSelectWithParam);
foreach (Toy t in query)
{
Assert.AreEqual(toys.Rows[i++]["name"], t.Name);
}
}
}
public void WhereWithRelatedEntities2()
{
MySqlDataAdapter da = new MySqlDataAdapter(
@"SELECT c.* FROM Toys t LEFT JOIN Companies c ON c.Id=t.SupplierId
WHERE c.State<>'TX' AND c.State<>'AZ'", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = @"SELECT VALUE t FROM Toys AS t
WHERE t.Supplier.Address.State<>'TX' AND t.Supplier.Address.State <> 'AZ'";
ObjectQuery<Toy> query = context.CreateQuery<Toy>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.WhereWithRelatedEntities2);
int i = 0;
foreach (Toy t in query)
{
Assert.AreEqual(dt.Rows[i++]["id"], t.Id);
}
}
}
public void WhereLiteralOnRelation()
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT id FROM Companies WHERE city = 'Dallas'", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = "SELECT VALUE c FROM Companies AS c WHERE c.Address.City = 'Dallas'";
ObjectQuery<Company> query = context.CreateQuery<Company>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.WhereLiteralOnRelation);
int i = 0;
foreach (Company c in query)
Assert.AreEqual(dt.Rows[i++]["id"], c.Id);
}
}
