C# Query.Count方法代码示例

        public void OptimisedCountFailureTest()
        {
            Mockery mocks = new Mockery();

            IDbConnection mockConnection = mocks.NewMock<IDbConnection>();

            /* We will first try to execute the optimised form of the command, which will fail to return
             * any results. This should lead to the default command being executed, which will return
             * a correct result set */
            IDbCommand optimisedMockCommand = mocks.NewMock<IDbCommand>();
            IDataReader optimisedReader = mocks.NewMock<IDataReader>();

            IDbCommand defaultMockCommand = mocks.NewMock<IDbCommand>();

            IDataReader mockReader = mocks.NewMock<IDataReader>();

            Expect.Once.On(mockConnection)
                .Method("CreateCommand")
                .Will(Return.Value(optimisedMockCommand));

            Expect.Once.On(optimisedMockCommand)
                .SetProperty("CommandText").To("SELECT stat FROM pre_calculated_count;");

            Expect.Once.On(optimisedMockCommand)
                .Method("ExecuteReader")
                .Will(Return.Value(optimisedReader));

            Expect.Once.On(optimisedReader)
                .Method("Read")
                .Will(Return.Value(false));

            Expect.Once.On(optimisedReader)
                .Method("Dispose");

            Expect.Once.On(mockConnection)
                .Method("Dispose");

            /* NB: We dispose of the mock connection, then we start using it again. In reality, we've called
             * the provider function again, which returns the same value. For more realism, we could have two
             * mocks and have a provider function that successively returns one, then the other */

            Expect.Once.On(mockConnection)
                .Method("CreateCommand")
                .Will(Return.Value(defaultMockCommand));

            Expect.Once.On(defaultMockCommand)
                .SetProperty("CommandText").To("SELECT COUNT(*) AS numrows FROM employees;");

            Expect.Once.On(defaultMockCommand)
                .Method("ExecuteReader")
                .Will(Return.Value(mockReader));

            Expect.Once.On(mockReader)
                .Method("Read")
                .Will(Return.Value(true));

            Expect.Once.On(mockReader)
                .Get["numrows"]
                .Will(Return.Value("42"));

            /* You might expect us to call Read() on the reader until it returns false - but actually, in the
             * case of an aggregate function we don't bother doing this. Since we currently don't support
             * GROUP BY, there's never going to be more than one row in an aggregate result set */
            /*Expect.Once.On(mockReader)
                .Method("Read")
                .Will(Return.Value(false));*/

            Expect.Once.On(mockReader)
                .Method("Dispose");

            Expect.Once.On(mockConnection)
                .Method("Dispose");

            SQLBuilder sqlBuilder = new MySQLBuilder();
            sqlBuilder.AddSelectTypeClause("employees", typeof(Employee));
            sqlBuilder.AddFromClause("employees");

            LazyDBQueryProvider<Employee> provider = new LazyDBQueryProvider<Employee>(() => mockConnection, sqlBuilder, new Dictionary<string, object>());
            Query<Employee> query = new Query<Employee>(provider);

            provider.SetOptimisedCountExpression("SELECT stat FROM pre_calculated_count;");

            int count = query.Count();

            Assert.AreEqual(42, count);
            mocks.VerifyAllExpectationsHaveBeenMet();
        }

        public void ExpressionOptimisationTest()
        {
            Mockery mocks = new Mockery();

            IDbConnection mockConnection = mocks.NewMock<IDbConnection>();
            IDbCommand mockCommand = mocks.NewMock<IDbCommand>();
            IDataReader mockReader = mocks.NewMock<IDataReader>();

            Expect.Once.On(mockConnection)
                .Method("CreateCommand")
                .Will(Return.Value(mockCommand));

            Expect.Once.On(mockCommand)
                .SetProperty("CommandText").To("SELECT stat FROM pre_calculated_count;");

            Expect.Once.On(mockCommand)
                .Method("ExecuteReader")
                .Will(Return.Value(mockReader));

            Expect.Exactly(1).On(mockReader)
                .Method("Read")
                .Will(Return.Value(true));

            Expect.Once.On(mockReader)
                .Get["numrows"]
                .Will(Return.Value("42"));

            Expect.Once.On(mockReader)
                .Method("Dispose");

            Expect.Once.On(mockConnection)
                .Method("Dispose");

            SQLBuilder sqlBuilder = new MySQLBuilder();

            sqlBuilder.AddSelectTypeClause("employees", typeof(Employee));
            sqlBuilder.AddFromClause("employees");

            LazyDBQueryProvider<Employee> provider = new LazyDBQueryProvider<Employee>(() => mockConnection, sqlBuilder, new Dictionary<string, object>());
            Query<Employee> query = new Query<Employee>(provider);

            provider.SetOptimisedCountExpression("SELECT stat FROM pre_calculated_count;");

            int count = query.Count();

            Assert.AreEqual(42, count);

            mocks.VerifyAllExpectationsHaveBeenMet();
        }

        public void QueryRepeatabilityTest()
        {
            Mockery mocks = new Mockery();

            IDbConnection mockConnection = mocks.NewMock<IDbConnection>();
            IDbCommand firstMockCommand = mocks.NewMock<IDbCommand>();
            IDbCommand secondMockCommand = mocks.NewMock<IDbCommand>();
            IDataReader firstMockReader = mocks.NewMock<IDataReader>();
            IDataReader secondMockReader = mocks.NewMock<IDataReader>();

            Expect.Once.On(mockConnection)
                .Method("CreateCommand")
                .Will(Return.Value(firstMockCommand));

            Expect.Once.On(firstMockCommand)
                .SetProperty("CommandText").To("SELECT COUNT(*) AS numrows FROM employees WHERE  id=42 ;");

            Expect.Once.On(firstMockCommand)
                .Method("ExecuteReader")
                .Will(Return.Value(firstMockReader));

            Expect.Once.On(firstMockReader)
                .Method("Read")
                .Will(Return.Value(true));

            Expect.Once.On(firstMockReader)
                .Get["numrows"]
                .Will(Return.Value(1));

            Expect.Once.On(firstMockReader)
                .Method("Dispose");

            Expect.Once.On(mockConnection)
                .Method("CreateCommand")
                .Will(Return.Value(secondMockCommand));

            Expect.Once.On(secondMockCommand)
                .SetProperty("CommandText").To("SELECT DISTINCT id AS employee_id, name AS employee_name FROM employees WHERE  id=42 ;");

            Expect.Once.On(secondMockCommand)
                .Method("ExecuteReader")
                .Will(Return.Value(secondMockReader));

            Expect.Once.On(secondMockReader)
                .Method("Read")
                .Will(Return.Value(true));

            Expect.Once.On(secondMockReader)
                .Get["employee_id"]
                .Will(Return.Value(1));

            Expect.Once.On(secondMockReader)
                .Get["employee_name"]
                .Will(Return.Value("Bob"));

            Expect.Once.On(secondMockReader)
                .Method("Read")
                .Will(Return.Value(false));

            Expect.Once.On(secondMockReader)
                .Method("Dispose");

            // NB: We're returning the same connection object twice, so we'll dispose it twice. This
            // isn't really correct, but since these are mock objects nobody will know that they're
            // the same object
            Expect.Exactly(2).On(mockConnection)
                .Method("Dispose");

            SQLBuilder sqlBuilder = new MySQLBuilder();

            sqlBuilder.AddSelectClause("id AS employee_id");
            sqlBuilder.AddSelectClause("name AS employee_name");
            sqlBuilder.AddFromClause("employees");
            sqlBuilder.AddWhereClause("id=42", ExpressionType.And);

            LazyDBQueryProvider<Employee> provider = new LazyDBQueryProvider<Employee>(() => mockConnection, sqlBuilder, new Dictionary<string, object>());

            Query<Employee> query = new Query<Employee>(provider);

            int numEmployees = query.Count();
            Assert.AreEqual(1, numEmployees);

            List<Employee> employees = query.ToList();
        }

        public void CountExpressionTest()
        {
            Mockery mocks = new Mockery();

            IDbConnection mockConnection = mocks.NewMock<IDbConnection>();
            IDbCommand mockCommand = mocks.NewMock<IDbCommand>();
            IDataReader mockReader = mocks.NewMock<IDataReader>();

            Expect.Once.On(mockConnection)
                .Method("CreateCommand")
                .Will(Return.Value(mockCommand));

            Expect.Once.On(mockCommand)
                .SetProperty("CommandText").To("SELECT COUNT(*) AS numrows FROM employees WHERE  name LIKE '%smith' ;");

            Expect.Once.On(mockCommand)
                .Method("ExecuteReader")
                .Will(Return.Value(mockReader));

            Expect.Once.On(mockReader)
                .Method("Read")
                .Will(Return.Value(true));

            // Note that we only call the reader once, rather than iterating through until
            // it returns false. This works fine when we know we have only one result,
            // which will be the case until we implement GroupBy
            /*Expect.Once.On(mockReader)
                  .Method("Read")
                  .Will(Return.Value(false));*/

            Expect.Once.On(mockReader)
                .Get["numrows"]
                .Will(Return.Value(16));

            Expect.Once.On(mockReader)
                .Method("Dispose");

            Expect.Once.On(mockConnection)
                .Method("Dispose");

            SQLBuilder builder = new MySQLBuilder();

            builder.AddSelectClause("id");
            builder.AddSelectClause("name");
            builder.AddFromClause("employees");
            builder.AddWhereClause("name LIKE '%smith'", ExpressionType.And);

            LazyDBQueryProvider<Employee> provider = new LazyDBQueryProvider<Employee>(() => mockConnection, builder, new Dictionary<string, object>());
            Query<Employee> myQuery = new Query<Employee>(provider);

            Assert.AreEqual(16, myQuery.Count());

            mocks.VerifyAllExpectationsHaveBeenMet();
        }

		public void Query_Execute1()
		{
			IEnumerable<string> data = GetStrings();
			IQueryable<string> queryable = data.AsQueryable<string>();
			IQueryProvider provider = queryable.Provider;//IQueryProvider: Linq.EnumerableQuery`1

			//create the Query
			IQueryable<string> query = new Query<string>(provider, queryable.Expression);

			//Execute the Query with (borrowed) queryable.Provider
			//IQueryProvider: Linq.EnumerableQuery`1
			//Expression is a ConstantExpression
			IEnumerable<string> executedresults = query.Provider.Execute<IEnumerable<string>>(queryable.Expression);

			//Expression is MethodCallExpression.
			//Return type is IEnumerable`1
			query = from s in query
					where s.StartsWith("H") || s.StartsWith("J")
					select s;
			executedresults = query.Provider.Execute<IEnumerable<string>>(query.Expression);


			//Create a MethodCallExpression for Queryable.Count
			//Return type is int
			IEnumerable<MethodInfo> methods = from m in typeof(Queryable).GetMethods()
								where m.Name == "Count" && m.IsGenericMethod
								select m;
			MethodInfo method = methods.First();
			MethodInfo generic = method.MakeGenericMethod(typeof(string));
			MethodCallExpression countCall = Expression.Call(null,//static
				generic, new Expression[] { Expression.Constant(query)});

			//check that the MethodCallExprssion is valid.
			LambdaExpression lambda = Expression.Lambda(countCall, 
				Expression.Parameter(typeof(IQueryable)));//I'm not even sure this is the correct parameter Type!
			int count = (int)lambda.Compile().DynamicInvoke(new object[] {  query });

			//call IQueryProvider.Execute:
			count = (int)query.Provider.Execute<int>(countCall);
			Assert.AreEqual(query.Count(), count);
		}