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