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); } } }