public void CurrentDateTime()
{
DateTime current = DateTime.Now;
using (DefaultContext ctx = new DefaultContext(ConnectionString))
{
var context = ((IObjectContextAdapter)ctx).ObjectContext;
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 JoinOnRightSideAsDerivedTable()
{
using (DefaultContext ctx = GetDefaultContext())
{
var q = from b in ctx.Books
join a in ctx.ContractAuthors
on b.Author.Id equals a.Author.Id
where b.Pages > 300
select b;
string sql = q.ToString();
var expected = @"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent1`.`PubDate`, `Extent1`.`Pages`, `Extent1`.`Author_Id`
FROM `Books` AS `Extent1` INNER JOIN `ContractAuthors` AS `Extent2` ON (`Extent1`.`Author_Id` =
`Extent2`.`Author_Id`) OR ((`Extent1`.`Author_Id` IS NULL) AND (`Extent2`.`Author_Id` IS NULL))
WHERE `Extent1`.`Pages` > 300";
CheckSql(sql, expected);
}
}
public void CommandTimeout()
{
MySqlCommand cmd = new MySqlCommand("CREATE FUNCTION spFunc() RETURNS INT BEGIN DO SLEEP(5); RETURN 4; END", Connection);
cmd.ExecuteNonQuery();
var sb = new MySqlConnectionStringBuilder(ConnectionString);
sb.DefaultCommandTimeout = 3;
sb.UseDefaultCommandTimeoutForEF = true;
using (DefaultContext ctx = new DefaultContext(sb.ToString()))
{
var exception = Assert.Throws <MySqlException>(() =>
{
int val = ctx.Database.SqlQuery <int>(@"SELECT spFunc()").Single();
});
}
}
public void CanGroupBySingleColumn()
{
using (DefaultContext ctx = st.GetDefaultContext())
{
var authors = from a in ctx.Authors
group a by a.Age into cgroup
select new
{
Name = cgroup.Key,
Count = cgroup.Count()
};
string sql = authors.ToString();
st.CheckSql(sql,
@"SELECT `GroupBy1`.`K1` AS `Age`, `GroupBy1`.`A1` AS `C1` FROM (SELECT
`Extent1`.`Age` AS `K1`, COUNT(1) AS `A1` FROM `Authors` AS `Extent1`
GROUP BY `Extent1`.`Age`) AS `GroupBy1`");
}
}
public void SimpleDeleteRowByParameter()
{
using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
{
int total = ctx.Products.Count();
int cntLeft = ctx.Products.Where(b => b.MinAge >= 18).Count();
// make sure the test is valid
Assert.True(total > cntLeft);
foreach (Product p in ctx.Products.Where(b => b.MinAge < 18).ToList())
{
ctx.Products.Remove(p);
}
ctx.SaveChanges();
Assert.Equal(cntLeft, ctx.Products.Count());
st.NeedSetup = true;
}
}
public void SimpleDeleteAllRows()
{
using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
{
Assert.True(ctx.Products.Count() > 0);
foreach (Product p in ctx.Products)
{
ctx.Products.Remove(p);
}
ctx.SaveChanges();
Assert.Equal(0, ctx.Products.Count());
}
// set the flag that will cause the setup to happen again
// since we just blew away a table
st.NeedSetup = true;
}
public void ComplexListIn()
{
using (DefaultContext ctx = new DefaultContext(ConnectionString))
{
int[] Ages = new int[] { 8, 9, 10 };
var q = from e in ctx.Products
where (Ages.Contains(e.MinAge) && e.Name.Contains("Hoop")) ||
!Ages.Contains(e.MinAge)
orderby e.Name
select e;
var sql = q.ToString();
CheckSql(sql,
@"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent1`.`MinAge`, `Extent1`.`Weight`, `Extent1`.`CreatedDate`
FROM `Products` AS `Extent1` WHERE ((`Extent1`.`MinAge` IN ( 8,9,10 )) AND
(`Extent1`.`Name` LIKE @gp1)) OR (`Extent1`.`MinAge` NOT IN ( 8,9,10 )) ORDER BY
`Extent1`.`Name` ASC");
}
}
public void TimeType()
{
using (DefaultContext ctx = GetDefaultContext())
{
TimeSpan birth = new TimeSpan(11, 3, 2);
Child c = new Child();
c.ChildId = "ABC";
c.Name = "first";
c.BirthTime = birth;
c.Label = Guid.NewGuid();
ctx.Children.Add(c);
ctx.SaveChanges();
Child d = ctx.Children.Where(x => x.ChildId == "ABC").Single();
Assert.AreEqual(birth, d.BirthTime);
}
}
public void SimpleJoin()
{
using (DefaultContext ctx = GetDefaultContext())
{
var q = from b in ctx.Books
join a in ctx.Authors
on b.Author.Id equals a.Id
select new
{
bookId = b.Id,
bookName = b.Name,
authorName = a.Name
};
var expected = @"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent2`.`Name` AS `Name1`
FROM `Books` AS `Extent1` INNER JOIN `Authors` AS `Extent2` ON `Extent1`.`Author_Id` = `Extent2`.`Id`";
CheckSql(q.ToString(), expected);
}
}
public void IndexOf()
{
using (DefaultContext ctx = new DefaultContext(ConnectionString))
{
var context = ((IObjectContextAdapter)ctx).ObjectContext;
ObjectQuery <Int32> q = context.CreateQuery <Int32>(@"IndexOf('needle', 'haystackneedle')");
foreach (int index in q)
{
Assert.AreEqual(9, index);
}
q = context.CreateQuery <Int32>(@"IndexOf('haystack', 'needle')");
foreach (int index in q)
{
Assert.AreEqual(0, index);
}
}
}
public void GuidType()
{
using (DefaultContext ctx = st.GetDefaultContext())
{
TimeSpan birth = new TimeSpan(11, 3, 2);
Guid g = Guid.NewGuid();
Child c = new Child();
c.ChildId = "GUID";
c.Name = "first";
c.BirthTime = birth;
c.Label = g;
ctx.Children.Add(c);
ctx.SaveChanges();
Child d = ctx.Children.Where(x => x.ChildId == "GUID").Single();
Assert.Equal(g, d.Label);
}
}
public void CanGroupByMultipleColumns()
{
using (DefaultContext ctx = GetDefaultContext())
{
var authors = from a in ctx.Authors
group a by new { a.Age, a.Name } into cgroup
select new
{
Name = cgroup.Key.Name,
Count = cgroup.Count()
};
string sql = authors.ToString();
CheckSql(sql,
@"SELECT `GroupBy1`.`K2` AS `Age`, `GroupBy1`.`K1` AS `Name`, `GroupBy1`.`A1` AS `C1`
FROM (SELECT `Extent1`.`Name` AS `K1`, `Extent1`.`Age` AS `K2`, COUNT(1) AS `A1`
FROM `Authors` AS `Extent1` GROUP BY `Extent1`.`Name`, `Extent1`.`Age`) AS `GroupBy1`");
}
}
void LoadData()
{
using (DefaultContext ctx = new DefaultContext(st.ConnectionString))
{
ctx.Products.Add(new Product()
{
Name = "Garbage Truck", MinAge = 8
});
ctx.Products.Add(new Product()
{
Name = "Fire Truck", MinAge = 12
});
ctx.Products.Add(new Product()
{
Name = "Hula Hoop", MinAge = 18
});
ctx.SaveChanges();
}
}
public virtual void SetUp()
{
if (NeedSetup)
{
NeedSetup = false;
using (DefaultContext ctx = new DefaultContext(ConnectionString))
{
if (ctx.Database.Exists())
{
ctx.Database.Delete();
}
var context = ((IObjectContextAdapter)ctx).ObjectContext;
context.CreateDatabase();
}
LoadData();
}
}
public override void LoadData()
{
using (DefaultContext ctx = new DefaultContext(ConnectionString))
{
ctx.Products.Add(new Product()
{
Id = 1, Name = "Garbage Truck", Weight = 8.865f
});
ctx.Products.Add(new Product()
{
Id = 2, Name = "Fire Truck", Weight = 12.623f
});
ctx.Products.Add(new Product()
{
Id = 3, Name = "Hula Hoop", Weight = 2.687f
});
ctx.SaveChanges();
}
}
public void SimpleDeleteRowByParameter()
{
using (DefaultContext ctx = new DefaultContext(ConnectionString))
{
int total = ctx.Products.Count();
int cntLeft = ctx.Products.Where(b => b.MinAge >= 18).Count();
// make sure the test is valid
Assert.True(total > cntLeft);
foreach (Product p in ctx.Products.Where(b => b.MinAge < 18).ToList())
{
ctx.Products.Remove(p);
}
ctx.SaveChanges();
Assert.AreEqual(cntLeft, ctx.Products.Count());
}
// set the flag that will cause the setup to happen again
// since we just blew away a table
NeedSetup = true;
}
public void MultipleOrs()
{
using (DefaultContext ctx = new DefaultContext(ConnectionString))
{
// 3rd test, using only ||'s
var q = from e in ctx.Products
where e.MinAge == 37 || e.MinAge == 38 || e.MinAge == 39 ||
e.MinAge == 40 || e.MinAge == 40 || e.MinAge == 41 ||
e.MinAge == 42 || e.MinAge == 43
orderby e.Name
select e;
var sql = q.ToString();
CheckSql(sql,
@"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent1`.`MinAge`, `Extent1`.`Weight`, `Extent1`.`CreatedDate`
FROM `Products` AS `Extent1` WHERE (((((((37 = `Extent1`.`MinAge`) OR (38 = `Extent1`.`MinAge`)) OR
(39 = `Extent1`.`MinAge`)) OR (40 = `Extent1`.`MinAge`)) OR (40 = `Extent1`.`MinAge`)) OR
(41 = `Extent1`.`MinAge`)) OR (42 = `Extent1`.`MinAge`)) OR (43 = `Extent1`.`MinAge`)
ORDER BY `Extent1`.`Name` ASC");
}
}
public void SimpleJoinWithPredicate()
{
using (DefaultContext ctx = st.GetDefaultContext())
{
var q = from b in ctx.Books
join a in ctx.Authors
on b.Author.Id equals a.Id
where b.Pages > 300
select new
{
bookId = b.Id,
bookName = b.Name,
authorName = a.Name
};
var expected = @"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent2`.`Name` AS `Name1` FROM `Books` AS `Extent1`
INNER JOIN `Authors` AS `Extent2` ON `Extent1`.`Author_Id` = `Extent2`.`Id`
WHERE `Extent1`.`Pages` > 300";
st.CheckSql(q.ToString(), expected);
}
}
public void TimestampColumn()
{
DateTime now = DateTime.Now;
using (DefaultContext ctx = GetDefaultContext())
{
Product p = new Product()
{
Name = "My Product", MinAge = 7, Weight = 8.0f
};
ctx.Products.Add(p);
ctx.SaveChanges();
p = ctx.Products.First();
p.CreatedDate = now;
ctx.SaveChanges();
p = ctx.Products.First();
Assert.AreEqual(now, p.CreatedDate);
}
}
public void Round()
{
using (DefaultContext ctx = new DefaultContext(ConnectionString))
{
var context = ((IObjectContextAdapter)ctx).ObjectContext;
ObjectQuery <DbDataRecord> q = context.CreateQuery <DbDataRecord>(@"
SELECT p.Id, p.Weight,
Round(p.Weight) AS [Rounded Weight],
Floor(p.Weight) AS [Floor of Weight],
Ceiling(p.Weight) AS [Ceiling of Weight]
FROM Products AS p WHERE p.Id=1");
foreach (DbDataRecord r in q)
{
Assert.AreEqual(1, r[0]);
Assert.AreEqual(8.865f, (float)r[1]);
Assert.AreEqual(9, Convert.ToInt32(r[2]));
Assert.AreEqual(8, Convert.ToInt32(r[3]));
Assert.AreEqual(9, Convert.ToInt32(r[4]));
}
}
}
public void Bitwise()
{
using (DefaultContext ctx = new DefaultContext(ConnectionString))
{
var context = ((IObjectContextAdapter)ctx).ObjectContext;
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 Trims()
{
using (DefaultContext ctx = new DefaultContext(ConnectionString))
{
var context = ((IObjectContextAdapter)ctx).ObjectContext;
ObjectQuery <string> query = context.CreateQuery <string>("LTrim(' text ')");
foreach (string s in query)
{
Assert.AreEqual("text ", s);
}
query = context.CreateQuery <string>("RTrim(' text ')");
foreach (string s in query)
{
Assert.AreEqual(" text", s);
}
query = context.CreateQuery <string>("Trim(' text ')");
foreach (string s in query)
{
Assert.AreEqual("text", s);
}
}
}
public void ConversionToLike()
{
// Generates queries for each LIKE + wildcards case and checks SQL generated.
using (DefaultContext ctx = new DefaultContext(ConnectionString))
{
// Like 'pattern%'
var q = from c in ctx.Products
where c.Name.StartsWith("B")
orderby c.Name
select c;
var sql = q.ToString();
CheckSql(sql,
@"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent1`.`MinAge`, `Extent1`.`Weight`,
`Extent1`.`CreatedDate` FROM `Products` AS `Extent1` WHERE `Extent1`.`Name` LIKE @gp1
ORDER BY `Extent1`.`Name` ASC");
// Like '%pattern%'
q = from c in ctx.Products
where c.Name.Contains("r")
orderby c.Name
select c;
sql = q.ToString();
CheckSql(sql,
@"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent1`.`MinAge`, `Extent1`.`Weight`,
`Extent1`.`CreatedDate` FROM `Products` AS `Extent1` WHERE `Extent1`.`Name` LIKE @gp1
ORDER BY `Extent1`.`Name` ASC");
// Like '%pattern'
q = from c in ctx.Products
where c.Name.EndsWith("y")
orderby c.Name
select c;
sql = q.ToString();
CheckSql(sql,
@"SELECT `Extent1`.`Id`, `Extent1`.`Name`, `Extent1`.`MinAge`, `Extent1`.`Weight`, `Extent1`.`CreatedDate`
FROM `Products` AS `Extent1` WHERE `Extent1`.`Name` LIKE @gp1 ORDER BY `Extent1`.`Name` ASC");
}
}
public void OneTimeSetup()
{
if (NeedSetup)
{
NeedSetup = false;
database = "db-" + this.GetType().Name.ToLower();
if (database.Length > 32)
{
database = database.Substring(0, 32);
}
MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder();
sb.Server = "localhost";
string port = Environment.GetEnvironmentVariable("MYSQL_PORT");
sb.Port = Port = string.IsNullOrEmpty(port) ? 3306 : uint.Parse(port);
sb.UserID = "root";
sb.Pooling = false;
sb.AllowUserVariables = true;
sb.Database = database;
ConnectionString = sb.ToString();
using (DefaultContext ctx = new DefaultContext(ConnectionString))
{
if (ctx.Database.Exists())
{
ctx.Database.Delete();
}
var context = ((IObjectContextAdapter)ctx).ObjectContext;
context.CreateDatabase();
}
Connection = new MySqlConnection(ConnectionString);
Connection.Open();
LoadData();
}
}
public virtual bool Setup(Type t)
{
if (!NeedSetup)
{
return(false);
}
NeedSetup = false;
database = "db-" + t.Name.ToLower();
if (database.Length > 32)
{
database = database.Substring(0, 32);
}
MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder();
sb.Server = "localhost";
sb.Port = 3306;
sb.UserID = "root";
sb.Pooling = false;
sb.AllowUserVariables = true;
sb.Database = database;
ConnectionString = sb.ToString();
using (DefaultContext ctx = new DefaultContext(ConnectionString))
{
if (ctx.Database.Exists())
{
ctx.Database.Delete();
}
ctx.Database.Create();
}
Connection = new MySqlConnection(ConnectionString);
Connection.Open();
return(true);
}