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 AverageWithGrouping()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT AVG(Freight) FROM Orders GROUP BY StoreId", 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.Store.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 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.AreEqual(5, record[1]);
Assert.AreEqual(18, record[2]);
Assert.AreEqual(23, record[3]);
}
}
}
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 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 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 Exists()
{
MySqlDataAdapter da = new MySqlDataAdapter(
@"SELECT c.* FROM Companies c WHERE EXISTS
(SELECT * FROM Toys t WHERE t.SupplierId=c.Id && t.MinAge < 4)", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = @"SELECT VALUE c FROM Companies AS c WHERE EXISTS(
SELECT p FROM c.Toys AS p WHERE p.MinAge < 4)";
ObjectQuery<Company> query = context.CreateQuery<Company>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.Exists);
int i = 0;
foreach(Company c in query)
Assert.AreEqual(dt.Rows[i++]["id"], c.Id);
}
}
public void JoinOfUnionsOnRightSideofJoin()
{
using (testEntities context = new testEntities())
{
string eSql = @"SELECT c.Id, c.Name, Union1.Id, Union1.Name,
Union2.Id, Union2.Name FROM
testEntities.Companies AS c JOIN (
((SELECT t.Id, t.Name FROM testEntities.Toys as t)
UNION ALL
(SELECT s.Id, s.Name FROM testEntities.Stores as s)) AS Union1
JOIN
((SELECT a.Id, a.Name FROM testEntities.Authors AS a)
UNION ALL
(SELECT b.Id, b.Name FROM testEntities.Books AS b)) AS Union2
ON Union1.Id = Union2.Id) ON c.Id = Union1.Id";
ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.JoinOfUnionsOnRightSideOfJoin);
foreach (DbDataRecord record in query)
{
Assert.AreEqual(6, record.FieldCount);
}
}
}
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);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.UnionAll);
int i = 0;
foreach (DbDataRecord r in query)
{
i++;
}
Assert.AreEqual(dt.Rows.Count, i);
}
}
public void Replace()
{
using (testEntities context = new testEntities())
{
ObjectQuery<string> q = context.CreateQuery<string>(
@"Replace('abcdefghi', 'def', 'zzz')");
foreach (string s in q)
Assert.AreEqual("abczzzghi", s);
}
}
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 eSql = "SELECT VALUE AVG(o.Freight) FROM Orders AS o WHERE o.Store.Id = 3";
ObjectQuery<Double> q = context.CreateQuery<Double>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.AverageWithPredicate);
foreach (Double r in q)
Assert.AreEqual(Convert.ToInt32(freight), Convert.ToInt32(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 eSql = "SELECT VALUE SUM(o.Freight) FROM Orders AS o WHERE o.Store.Id = 2";
ObjectQuery<Double> q = context.CreateQuery<Double>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.SumWithPredicate);
foreach (Double r in q)
Assert.AreEqual(freight, 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.AreEqual(1, r[0]);
Assert.AreEqual(65.3, r[1]);
Assert.AreEqual(65, r[2]);
Assert.AreEqual(65, r[3]);
Assert.AreEqual(66, r[4]);
}
}
}
public void WhereWithRelatedEntities2()
{
MySqlDataAdapter da = new MySqlDataAdapter(
@"SELECT c.* FROM Toys t LEFT JOIN Companies c ON c.Id=t.SupplierId
WHERE c.State<>'TX' AND c.State<>'AZ'", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = @"SELECT VALUE t FROM Toys AS t
WHERE t.Supplier.Address.State<>'TX' AND t.Supplier.Address.State <> 'AZ'";
ObjectQuery<Toy> query = context.CreateQuery<Toy>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.WhereWithRelatedEntities2);
int i = 0;
foreach (Toy t in query)
{
Assert.AreEqual(dt.Rows[i++]["id"], t.Id);
}
}
}
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 Length()
{
using (testEntities context = new testEntities())
{
string entitySQL = "Length('abc')";
ObjectQuery<Int32> query = context.CreateQuery<Int32>(entitySQL);
foreach (int len in query)
Assert.AreEqual(3, len);
}
}
public void MinWithPredicate()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT MIN(Freight) FROM Orders WHERE storeId=2", conn);
object freight = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string eSql = "SELECT Min(o.Freight) FROM Orders AS o WHERE o.Store.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", 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 Substring()
{
using (testEntities context = new testEntities())
{
ObjectQuery<string> query = context.CreateQuery<string>("SUBSTRING('foobarfoo',4,3)");
query = context.CreateQuery<string>("SUBSTRING('foobarfoo',4,30)");
foreach (string s in query)
Assert.AreEqual("barfoo", s);
}
}
public void IndexOf()
{
using (testEntities context = new testEntities())
{
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 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.AreEqual("HASBRO", r[0]);
Assert.AreEqual("hasbro", r[1]);
Assert.AreEqual("orbsaH", r[2]);
}
}
}
public void Trims()
{
using (testEntities context = new testEntities())
{
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 SimpleSelectWithParam()
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Toys WHERE minage>3", 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>@age");
query.Parameters.Add(new ObjectParameter("age", 3));
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.SimpleSelectWithParam);
foreach (Toy t in query)
{
Assert.AreEqual(toys.Rows[i++]["name"], t.Name);
}
}
}
public void SumSimple()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT SUM(minage) FROM Toys", conn);
object sumAge = 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();
CheckSql(sql, SQLSyntax.SumSimple);
foreach (int r in q)
Assert.AreEqual(sumAge, r);
}
}
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 eSql = "SELECT VALUE c FROM Companies AS c WHERE c.Address.City = 'Dallas'";
ObjectQuery<Company> query = context.CreateQuery<Company>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.WhereLiteralOnRelation);
int i = 0;
foreach (Company c in query)
Assert.AreEqual(dt.Rows[i++]["id"], c.Id);
}
}
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 eSql = "SELECT VALUE SUM(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.SumWithGrouping);
int i = 0;
foreach (double freight in q)
Assert.AreEqual(Convert.ToInt32(dt.Rows[i++][0]), Convert.ToInt32(freight));
}
}
public void MaxInSubQuery2()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT s.* FROM Stores AS s WHERE s.id=(SELECT MAX(o.storeId) FROM Orders AS o)", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = @"SELECT VALUE s FROM Stores AS s WHERE s.Id =
ANYELEMENT(SELECT VALUE MAX(o.Store.Id) FROM Orders As o)";
ObjectQuery<Store> q = context.CreateQuery<Store>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.MaxInSubQuery2);
int i = 0;
foreach (Store s in q)
Assert.AreEqual(dt.Rows[i++]["id"], s.Id);
}
}
public void CanRoundToNonZeroDigits()
{
using (testEntities context = new testEntities())
{
DbDataRecord order = context.CreateQuery<DbDataRecord>(@"
SELECT o.Id, o.Freight,
Round(o.Freight, 2) AS [Rounded Freight]
FROM Orders AS o WHERE o.Id=10").First();
Assert.AreEqual(350.54721, order[1]);
Assert.AreEqual(350.55, order[2]);
}
}
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 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);
}
}