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 ShopId", 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.Shop.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 BigCountSimple()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT COUNT(*) FROM Toys", conn);
object trueCount = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string sql = "SELECT VALUE BigCount(t.Id) FROM Toys AS t";
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.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 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 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 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 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 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 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 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 OrderByWithPredicate()
{
using (testEntities context = new testEntities())
{
using (EntityConnection ec = context.Connection as EntityConnection)
{
ec.Open();
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT id FROM Companies c WHERE c.NumEmployees > 100 ORDER BY c.Name", conn);
DataTable dt = new DataTable();
da.Fill(dt);
string eSql = "SELECT VALUE c FROM Companies AS c WHERE c.NumEmployees > 100 ORDER BY c.Name";
ObjectQuery<Company> query = context.CreateQuery<Company>(eSql);
string sql = query.ToTraceString();
CheckSql(sql, SQLSyntax.OrderByWithPredicate);
int i = 0;
foreach (Company c in query)
Assert.AreEqual(dt.Rows[i++][0], c.Id);
}
}
}
public void AverageWithPredicate()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT AVG(Freight) FROM Orders WHERE shopId=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.Shop.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 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 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.Shops 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 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 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 MaxInSubQuery2()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT s.* FROM Shops AS s WHERE s.id=(SELECT MAX(o.shopId) FROM Orders AS o)", conn);
DataTable dt = new DataTable();
da.Fill(dt);
using (testEntities context = new testEntities())
{
string eSql = @"SELECT VALUE s FROM Shops AS s WHERE s.Id =
ANYELEMENT(SELECT VALUE MAX(o.Shop.Id) FROM Orders As o)";
ObjectQuery<Shop> q = context.CreateQuery<Shop>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.MaxInSubQuery2);
int i = 0;
foreach (Shop s in q)
Assert.AreEqual(dt.Rows[i++]["id"], s.Id);
}
}
public void SumWithGrouping()
{
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT SUM(Freight) FROM Orders GROUP BY ShopId", 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.Shop.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 SumWithPredicate()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT SUM(Freight) FROM Orders WHERE shopId=2", 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();
CheckSql(sql, SQLSyntax.SumWithPredicate);
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 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 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 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 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 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 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 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 MaxWithPredicate()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT MAX(Freight) FROM Orders WHERE shopId=1", conn);
object freight = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string eSql = "SELECT MAX(o.Freight) FROM Orders AS o WHERE o.Shop.Id = 1";
ObjectQuery<DbDataRecord> q = context.CreateQuery<DbDataRecord>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.MaxWithPredicate);
foreach (DbDataRecord r in q)
Assert.AreEqual(freight, r.GetDouble(0));
}
}