public void BadOrderBy() { ReInitDb(); using (MovieDBContext db = new MovieDBContext()) { db.Database.Initialize(true); DateTime filterDate = new DateTime(1986, 1, 1); var q = db.Movies.Where(p => p.ReleaseDate >= filterDate). OrderByDescending(p => p.ReleaseDate).Take(2); string sql = q.ToString(); st.CheckSql(SQLSyntax.NestedOrderBy, sql); // Data integrity testing Movie[] data = new Movie[] { new Movie() { ID = 4, Title = "Star Wars, The Sith Revenge", ReleaseDate = new DateTime(2005, 5, 19) }, new Movie() { ID = 2, Title = "The Matrix", ReleaseDate = new DateTime(1999, 3, 31) } }; int i = 0; foreach (Movie m in q) { Assert.Equal(data[i].ID, m.ID); Assert.Equal(data[i].Title, m.Title); Assert.Equal(data[i].ReleaseDate, m.ReleaseDate); i++; } } }
public void BadContainsOrderByTake3() { ReInitDb(); using (MovieDBContext db = new MovieDBContext()) { db.Database.Initialize(true); var q = db.Movies. Where(m => !string.IsNullOrEmpty(m.Title) && m.Title.Contains("x")). OrderByDescending(m => m.ID). Skip(1). Take(1).Select(m => new { Id = m.ID, CriticsScore = ( m.Title == "Terminator 1" ? "Good" : m.Title == "Predator" ? "Sunday best, cheese" : m.Title == "The Matrix" ? "Really Good" : m.Title == "Star Wars, The Sith Revenge" ? "Really Good" : "Unknown") }); string sql = q.ToString(); #if DEBUG Debug.WriteLine(sql); #endif foreach (var row in q) { } } }
public void CallStoredProcedure() { using (MovieDBContext context = new MovieDBContext()) { context.Database.Initialize(true); int count = context.Database.SqlQuery <int>("GetCount").First(); Assert.AreEqual(5, count); } }
public void SimpleCodeFirstSelect() { MovieDBContext db = new MovieDBContext(); db.Database.Initialize(true); var l = db.Movies.ToList(); foreach (var i in l) { } }
public void CheckByteArray() { MovieDBContext db = new MovieDBContext(); db.Database.Initialize(true); string dbCreationScript = ((IObjectContextAdapter)db).ObjectContext.CreateDatabaseScript(); Regex rx = new Regex(@"`Data` (?<type>[^\),]*)", RegexOptions.Compiled | RegexOptions.Singleline); Match m = rx.Match(dbCreationScript); Assert.AreEqual("longblob", m.Groups["type"].Value); }
public void TestPrecisionNscale() { MovieDBContext db = new MovieDBContext(); db.Database.Initialize(true); var l = db.Movies.ToList(); IDataReader r = execReader(string.Format( @"select numeric_precision, numeric_scale from information_schema.columns where table_schema = '{0}' and table_name = 'movies' and column_name = 'Price'", conn.Database)); r.Read(); Assert.AreEqual(16, r.GetInt32(0)); Assert.AreEqual(2, r.GetInt32(1)); }
public void ConcurrencyCheck() { using (MovieDBContext db = new MovieDBContext()) { db.Database.Delete(); db.Database.CreateIfNotExists(); db.Database.ExecuteSqlCommand( @"DROP TABLE IF EXISTS `MovieReleases`"); db.Database.ExecuteSqlCommand( @"CREATE TABLE IF NOT EXISTS `MovieReleases` ( `Id` int(11) NOT NULL, `Name` varbinary(45) NOT NULL, `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=binary"); MySqlTrace.Listeners.Clear(); MySqlTrace.Switch.Level = SourceLevels.All; GenericListener listener = new GenericListener(); MySqlTrace.Listeners.Add(listener); try { MovieRelease mr = db.MovieReleases.Create(); mr.Id = 1; mr.Name = "Commercial"; db.MovieReleases.Add(mr); db.SaveChanges(); mr.Name = "Director's Cut"; db.SaveChanges(); } finally { db.Database.ExecuteSqlCommand(@"DROP TABLE IF EXISTS `MovieReleases`"); } // Check sql Regex rx = new Regex(@"Query Opened: (?<item>UPDATE .*)", RegexOptions.Compiled | RegexOptions.Singleline); foreach (string s in listener.Strings) { Match m = rx.Match(s); if (m.Success) { st.CheckSql(m.Groups["item"].Value, SQLSyntax.UpdateWithSelect); //Assert.Pass(); } } //Assert.Fail(); } }
public void AddingEmptyRow() { using (MovieDBContext ctx = new MovieDBContext()) { ctx.Database.Initialize(true); ctx.EntitySingleColumns.Add(new EntitySingleColumn()); ctx.SaveChanges(); } using (MovieDBContext ctx2 = new MovieDBContext()) { var q = from esc in ctx2.EntitySingleColumns where esc.Id == 1 select esc; Assert.AreEqual(1, q.Count()); } }
public void BadContainsOrderByTake4() { ReInitDb(); using (MovieDBContext db = new MovieDBContext()) { db.Database.Initialize(true); bool q = db.Movies.Any(m => m.ReleaseDate.Year > 1985); // string sql = q.ToString(); //#if DEBUG // Debug.WriteLine(sql); //#endif //foreach (var row in q) //{ //} } }
public void AlterTableTest() { ReInitDb(); MovieDBContext db = new MovieDBContext(); db.Database.Initialize(true); var l = db.MovieFormats.ToList(); foreach (var i in l) { } MovieFormat m = new MovieFormat(); m.Format = 8.0f; db.MovieFormats.Add(m); db.SaveChanges(); }
public void BadContainsOrderByTake5() { ReInitDb(); using (MovieDBContext db = new MovieDBContext()) { db.Database.Initialize(true); // TODO: add subquery like // var shifts = Shifts.Where(s => !EmployeeShifts.Where(es => es.ShiftID == s.ShiftID).Any()); bool q = db.Movies.Where(m => m.ReleaseDate.Month != 10).Any(m => m.ReleaseDate.Year > 1985); // string sql = q.ToString(); //#if DEBUG // Debug.WriteLine(sql); //#endif // foreach (var row in q) // { // } } }
public void FirstOrDefaultNested() { using (MovieDBContext ctx = new MovieDBContext()) { ctx.Database.Initialize(true); int DirectorId = 1; var q = ctx.Movies.Where(p => p.Director.ID == DirectorId).Select(p => new { Id = p.ID, FirstMovieFormat = p.Formats.Count == 0 ? 0.0 : p.Formats.FirstOrDefault().Format }); string sql = q.ToString(); foreach (var r in q) { } } }
public void BadContainsOrderByTake6() { ReInitDb(); using (MovieDBContext db = new MovieDBContext()) { db.Database.Initialize(true); var q = from m in db.Movies where m.Title.Contains("x") && db.Medias.Where(mm => mm.Format == "Digital").Any() select m; string sql = q.ToString(); #if DEBUG Debug.WriteLine(sql); #endif foreach (var row in q) { } } }
public void BadContainsOrderByTake2() { ReInitDb(); using (MovieDBContext db = new MovieDBContext()) { db.Database.Initialize(true); var q = db.Movies. Where(m => !string.IsNullOrEmpty(m.Title) && m.Title.Contains("x")). OrderByDescending(m => m.ID). Skip(1). Take(1); string sql = q.ToString(); #if DEBUG Debug.WriteLine(sql); #endif List <Movie> l = q.ToList(); foreach (Movie m in l) { } } }
public void BadContainsOrderByTake() { ReInitDb(); using (MovieDBContext db = new MovieDBContext()) { db.Database.Initialize(true); string title = "T"; var q = from m in db.Movies where m.Title.Contains(title) orderby m.ID descending select m; var q1 = q.Take(10); string sql = q1.ToString(); st.CheckSql(SQLSyntax.QueryWithOrderByTakeContains, sql); int i = 0; foreach (var row in q1) { Assert.Equal(MovieDBInitialize.data[i].ID, row.ID); Assert.Equal(MovieDBInitialize.data[i].Title, row.Title); Assert.Equal(MovieDBInitialize.data[i].ReleaseDate, row.ReleaseDate); i++; } } }