C# testEntities.CreateQuery方法代码示例

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