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