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.Equal(5, record[1]);
Assert.Equal(18, record[2]);
Assert.Equal(23, record[3]);
}
}
}
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.Equal(1996, record[1]);
Assert.Equal(11, record[2]);
Assert.Equal(15, record[3]);
}
}
}
public void ToUpperToLowerReverse()
{
using (testEntities context = new testEntities())
{
ObjectQuery <DbDataRecord> q = context.CreateQuery <DbDataRecord>(
@"SELECT ToUpper(c.Name),ToLower(c.Name),
Reverse(c.Name) FROM Companies AS c WHERE c.Id=1");
foreach (DbDataRecord r in q)
{
Assert.Equal("HASBRO", r[0]);
Assert.Equal("hasbro", r[1]);
Assert.Equal("orbsaH", r[2]);
}
}
}
public void JoinOnRightSideNameClash()
{
using (testEntities context = new testEntities())
{
string eSql = @"SELECT c.Id, c.Name, a.Id, a.Name, b.Id, b.Name FROM
testEntities.Companies AS c JOIN (testEntities.Authors AS a
JOIN testEntities.Books AS b ON a.Id = b.Id) ON c.Id = a.Id";
ObjectQuery <DbDataRecord> query = context.CreateQuery <DbDataRecord>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.JoinOnRightSideNameClash);
foreach (DbDataRecord record in query)
{
Assert.AreEqual(6, record.FieldCount);
}
}
}
public void AverageSimple()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT AVG(minAge) FROM Toys", conn);
object avgAge = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string sql = "SELECT VALUE Avg(t.MinAge) FROM Toys AS t";
ObjectQuery <Decimal> q = context.CreateQuery <Decimal>(sql);
foreach (Decimal r in q)
{
Assert.AreEqual(avgAge, r);
}
}
}
public void SumWithPredicate()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT SUM(Freight) FROM Orders WHERE storeId=2", conn);
object freight = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string sql = "SELECT VALUE SUM(o.Freight) FROM Orders AS o WHERE o.Store.Id = 2";
ObjectQuery <Double> q = context.CreateQuery <Double>(sql);
foreach (Double r in q)
{
Assert.AreEqual(freight, r);
}
}
}
public void SumSimple()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT SUM(minage) FROM Toys", conn);
object sumAge = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string sql = "SELECT VALUE Sum(t.MinAge) FROM Toys AS t";
ObjectQuery <Int32> q = context.CreateQuery <Int32>(sql);
foreach (int r in q)
{
Assert.AreEqual(sumAge, r);
}
}
}
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 sql = "SELECT VALUE Count(t.Id) FROM Toys AS t WHERE t.MinAge > 3";
ObjectQuery <Int32> q = context.CreateQuery <Int32>(sql);
foreach (int count in q)
{
Assert.AreEqual(trueCount, count);
}
}
}
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.Equal(current.Year, dt.Year);
Assert.Equal(current.Month, dt.Month);
Assert.Equal(current.Day, dt.Day);
// we don't check time as that will be always be different
}
}
}
public void MaxWithPredicate()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT MAX(Freight) FROM Orders WHERE storeId=1", conn);
object freight = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string sql = "SELECT MAX(o.Freight) FROM Orders AS o WHERE o.Store.Id = 1";
ObjectQuery <DbDataRecord> q = context.CreateQuery <DbDataRecord>(sql);
foreach (DbDataRecord r in q)
{
Assert.AreEqual(freight, r.GetDouble(0));
}
}
}
public void AverageWithPredicate()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT AVG(Freight) FROM Orders WHERE storeId=3", conn);
Double freight = (Double)trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string sql = "SELECT VALUE AVG(o.Freight) FROM Orders AS o WHERE o.Store.Id = 3";
ObjectQuery <Double> q = context.CreateQuery <Double>(sql);
foreach (Double r in q)
{
Assert.AreEqual(Convert.ToInt32(freight), Convert.ToInt32(r));
}
}
}
public void MaxSimple()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT MAX(minage) FROM Toys", conn);
int trueMax = (int)trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string sql = "SELECT VALUE MAX(t.MinAge) FROM Toys AS t";
ObjectQuery <Int32> q = context.CreateQuery <Int32>(sql);
foreach (int max in q)
{
Assert.AreEqual(trueMax, max);
}
}
}
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 BigCountSimple()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT COUNT(*) FROM Toys", conn);
object trueCount = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string eSql = "SELECT VALUE BigCount(t.Id) FROM Toys AS t";
ObjectQuery<Int32> q = context.CreateQuery<Int32>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.BigCountSimple);
foreach (int count in q)
Assert.AreEqual(trueCount, count);
}
}
public void MinSimple()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT MIN(minage) FROM Toys", conn);
int trueMin = (int)trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string eSql = "SELECT VALUE MIN(t.MinAge) FROM Toys AS t";
ObjectQuery<Int32> q = context.CreateQuery<Int32>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.MinSimple);
foreach (int age in q)
Assert.AreEqual(trueMin, age);
}
}
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 sql = @"SELECT c.LastName FROM Employees AS c WHERE c.Age > 20";
ObjectQuery <DbDataRecord> query = context.CreateQuery <DbDataRecord>(sql);
int i = 0;
foreach (DbDataRecord s in query)
{
Assert.AreEqual(dt.Rows[i++][0], s.GetString(0));
}
}
}
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 sql = "SELECT VALUE c FROM Companies AS c WHERE c.Address.City = 'Dallas'";
ObjectQuery <Company> query = context.CreateQuery <Company>(sql);
int i = 0;
foreach (Company c in query)
{
Assert.AreEqual(dt.Rows[i++]["id"], c.Id);
}
}
}
public void AverageWithPredicate()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT AVG(Freight) FROM Orders WHERE shopId=3", st.conn);
Double freight = (Double)trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string eSql = "SELECT VALUE AVG(o.Freight) FROM Orders AS o WHERE o.Shop.Id = 3";
ObjectQuery <Double> q = context.CreateQuery <Double>(eSql);
string sql = q.ToTraceString();
st.CheckSql(sql, SQLSyntax.AverageWithPredicate);
foreach (Double r in q)
{
Assert.Equal(Convert.ToInt32(freight), Convert.ToInt32(r));
}
}
}
public void MinWithPredicate()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT MIN(Freight) FROM Orders WHERE shopId=2", conn);
object freight = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string eSql = "SELECT Min(o.Freight) FROM Orders AS o WHERE o.Shop.Id = 2";
ObjectQuery <DbDataRecord> q = context.CreateQuery <DbDataRecord>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.MinWithPredicate);
foreach (DbDataRecord r in q)
{
Assert.AreEqual(freight, r.GetDouble(0));
}
}
}
public void CountWithPredicate()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT COUNT(*) FROM Toys AS t WHERE t.MinAge > 3", st.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();
st.CheckSql(sql, SQLSyntax.CountWithPredicate);
foreach (int count in q)
{
Assert.Equal(Convert.ToInt32(trueCount), count);
}
}
}
public void SumWithPredicate()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT SUM(Freight) FROM Orders WHERE shopId=2", st.conn);
object freight = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string eSql = "SELECT VALUE SUM(o.Freight) FROM Orders AS o WHERE o.Shop.Id = 2";
ObjectQuery <Double> q = context.CreateQuery <Double>(eSql);
string sql = q.ToTraceString();
st.CheckSql(sql, SQLSyntax.SumWithPredicate);
foreach (Double r in q)
{
Assert.Equal(freight, r);
}
}
}
public void SumSimple()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT SUM(minage) FROM Toys", st.conn);
int sumAge = Convert.ToInt32(trueCmd.ExecuteScalar());
using (testEntities context = new testEntities())
{
string eSql = "SELECT VALUE Sum(t.MinAge) FROM Toys AS t";
ObjectQuery <Int32> q = context.CreateQuery <Int32>(eSql);
string sql = q.ToTraceString();
st.CheckSql(sql, SQLSyntax.SumSimple);
foreach (int r in q)
{
Assert.Equal(sumAge, r);
}
}
}
public void AverageSimple()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT AVG(minAge) FROM Toys", st.conn);
Decimal avgAge = (Decimal)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();
st.CheckSql(sql, SQLSyntax.AverageSimple);
foreach (Decimal r in q)
{
Assert.Equal(avgAge, r);
}
}
}
public void Round()
{
using (testEntities context = new testEntities())
{
ObjectQuery <DbDataRecord> q = context.CreateQuery <DbDataRecord>(@"
SELECT o.Id, o.Freight,
Round(o.Freight) AS [Rounded Freight],
Floor(o.Freight) AS [Floor of Freight],
Ceiling(o.Freight) AS [Ceiling of Freight]
FROM Orders AS o WHERE o.Id=1");
foreach (DbDataRecord r in q)
{
Assert.Equal(1, r[0]);
Assert.Equal(65.3, r[1]);
Assert.Equal(65, Convert.ToInt32(r[2]));
Assert.Equal(65, Convert.ToInt32(r[3]));
Assert.Equal(66, Convert.ToInt32(r[4]));
}
}
}
public void MaxWithGrouping()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT MAX(Freight) FROM Orders GROUP BY StoreId", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = "SELECT VALUE MAX(o.Freight) FROM Orders AS o GROUP BY o.Store.Id";
ObjectQuery<Double> q = context.CreateQuery<Double>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.MaxWithGrouping);
int i = 0;
foreach (double freight in q)
Assert.AreEqual(Convert.ToInt32(dt.Rows[i++][0]), Convert.ToInt32(freight));
}
}
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 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 WhereWithRelatedEntities1()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT c.* FROM Toys t LEFT JOIN Companies c ON c.id=t.SupplierId WHERE c.State='TX'", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string sql = "SELECT VALUE t FROM Toys AS t WHERE t.Supplier.Address.State = 'TX'";
ObjectQuery <Toy> query = context.CreateQuery <Toy>(sql);
int i = 0;
foreach (Toy t in query)
{
Assert.AreEqual(dt.Rows[i++]["id"], t.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 SumWithGrouping()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT SUM(Freight) FROM Orders GROUP BY StoreId", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string sql = "SELECT VALUE SUM(o.Freight) FROM Orders AS o GROUP BY o.Store.Id";
ObjectQuery <Double> q = context.CreateQuery <Double>(sql);
int i = 0;
foreach (double freight in q)
{
Assert.AreEqual(Convert.ToInt32(dt.Rows[i++][0]), Convert.ToInt32(freight));
}
}
}