C# Table.Where方法代码示例

 public void LinqCount_Where_Async()
 {
     var table = new Table<AllDataTypesEntity>(_session, new MappingConfiguration());
     AllDataTypesEntity expectedEntity = _entityList[2];
     long count = table.Where(e => e.StringType == expectedEntity.StringType && e.GuidType == expectedEntity.GuidType).Count().ExecuteAsync().Result;
     Assert.AreEqual(1, count);
 }

 public void Delete_Com_Where()
 {
     var users = new Table("users");
     users.Where(users["idade"] < 18)
         .Delete();
     string sql_expected = "DELETE FROM users WHERE users.idade < @users_idade_1";
     Assert.AreEqual(sql_expected, users.ToSql());
 }

 public void Update_Com_Where_E_Valor_Nulo()
 {
     var users = new Table("users");
     users.Where(users["nome"] != null)
         .Update(new { idade = Utils.NULL });
     string sql_expected = "UPDATE users SET idade=NULL WHERE users.nome IS NOT NULL";
     Assert.AreEqual(sql_expected, users.ToSql());
 }

 public void Table_Constructor_Uses_Provided_Mappings_With_Custom_TableName()
 {
     var config = new MappingConfiguration().Define(new Map<AllTypesEntity>().TableName("tbl4").Column(t => t.Int64Value, cm => cm.WithName("id1")));
     var table = new Table<AllTypesEntity>(null, config, "tbl_overridden1");
     Assert.AreEqual(
         @"SELECT BooleanValue, DateTimeValue, DecimalValue, DoubleValue, id1, IntValue, StringValue, UuidValue FROM tbl_overridden1 WHERE id1 = ?",
         table.Where(t => t.Int64Value == 1).ToString());
 }

 public void Delete_Com_Where_Complexo()
 {
     var users = new Table("users");
     users.Where(users["idade"] < 18 | users["nome"] == null)
         .Where(users["ativo"] != null)
         .Delete();
     string sql_expected = "DELETE FROM users WHERE (users.idade < @users_idade_1) OR (users.nome IS NULL) AND users.ativo IS NOT NULL";
     Assert.AreEqual(sql_expected, users.ToSql());
 }

 public void Delete_Com_Where_Muito_Simples()
 {
     var users = new Table("users");
     users.Where(users["id"] == 1)
         .Delete();
     string sql_expected = "DELETE FROM users WHERE users.id = @users_id_1";
     Assert.AreEqual(sql_expected, users.ToSql());
     Assert.AreEqual(1, users.Params["users_id_1"]);
 }

 public void Consulta_Where_E_Zerando_Where_Com_Null()
 {
     var users = new Table("users");
     users.Project(users["name"])
         .Where(users["name"].Like("%n"));
     string sql1 = "SELECT users.name FROM users WHERE users.name LIKE @users_name_1";
     Assert.AreEqual(sql1, users.ToSql());
     users.Where(null).Top(10);
     string sql2 = "SELECT TOP 10 users.name FROM users";
     Assert.AreEqual(sql2, users.ToSql());
 }

 public void Update_Com_Where_Primeiro()
 {
     var users = new Table("users");
     users.Where(users["idade"] > 20)
     .Update(new
     {
         ativo = true,
         salario = 500
     });
     string sql_expected = "UPDATE users SET [email protected]_ativo_1, [email protected]_salario_1 WHERE users.idade > @users_idade_1";
     Assert.AreEqual(sql_expected, users.ToSql());
 }

        public void Delete_DeleteMultipleContains_Sync()
        {
            var table = new Table<AllDataTypesEntity>(_session, new MappingConfiguration());
            var count = table.Count().Execute();
            Assert.AreEqual(_entityList.Count, count);

            List<string> uniqueStringKeys = _entityList.Select(m => m.StringType).ToList();
            var deleteRequest = table.Where(m => uniqueStringKeys.Contains(m.StringType)).Delete();
            deleteRequest.Execute();
            count = table.Count().Execute();
            Assert.AreEqual(0, count);
        }

 public void Update_Com_Where_E_Hashtable()
 {
     var users = new Table("users");
     Hashtable hs = new Hashtable();
     hs.Add("superuser", false);
     hs.Add("ativo", true);
     users.Where(users["salario"] <= 2000)
         .Update(hs);
     string sql_expected = "UPDATE users SET [email protected]_ativo_1, [email protected]_superuser_1 "+
         "WHERE users.salario <= @users_salario_1";
     Assert.AreEqual(sql_expected, users.ToSql());
     Assert.AreEqual(true, users.Params["users_ativo_1"]);
     Assert.AreEqual(false, users.Params["users_superuser_1"]);
 }

        public void LinqUpdate_Single_Async()
        {
            // Setup
            Table<Movie> table = new Table<Movie>(_session, new MappingConfiguration());
            table.CreateIfNotExists();
            Movie movieToUpdate = _movieList[1];

            var expectedMovie = new Movie(movieToUpdate.Title, movieToUpdate.Director, "something_different_" + Randomm.RandomAlphaNum(10), movieToUpdate.MovieMaker, 1212);
            table.Where(m => m.Title == movieToUpdate.Title && m.MovieMaker == movieToUpdate.MovieMaker && m.Director == movieToUpdate.Director)
                 .Select(m => new Movie { Year = expectedMovie.Year, MainActor = expectedMovie.MainActor })
                 .Update()
                 .Execute();

            List<Movie> actualMovieList = table.ExecuteAsync().Result.ToList();
            Assert.AreEqual(_movieList.Count, actualMovieList.Count());
            Assert.IsFalse(Movie.ListContains(_movieList, expectedMovie));
            Movie.AssertListContains(actualMovieList, expectedMovie);
        }

        public void DeleteIf_ConditionSucceeds()
        {
            var table = new Table<Movie>(_session, new MappingConfiguration());
            table.Create();
            Movie actualMovie = Movie.GetRandomMovie();
            table.Insert(actualMovie).Execute();
            long count = table.Count().Execute();
            Assert.AreEqual(1, count);

            var deleteIfStatement = table
                .Where(m => m.Title == actualMovie.Title && m.MovieMaker == actualMovie.MovieMaker && m.Director == actualMovie.Director)
                .DeleteIf(m => m.MainActor == actualMovie.MainActor);

            var appliedInfo = deleteIfStatement.Execute();
            Assert.True(appliedInfo.Applied);
            Assert.Null(appliedInfo.Existing);
            count = table.Count().Execute();
            Assert.AreEqual(0, count);
        }

        public void LinqInsert_WithSetTimestamp_Test()
        {
            Table<Movie> nerdMoviesTable = new Table<Movie>(_session, new MappingConfiguration());
            Movie movie1 = Movie.GetRandomMovie();
            nerdMoviesTable.Insert(movie1).Execute();

            string mainActor = "Samuel L. Jackson";
            movie1.MainActor = mainActor;

            nerdMoviesTable
                .Insert(movie1)
                .SetTimestamp(DateTime.Now.AddDays(1))
                .Execute();

            Movie updatedMovie = nerdMoviesTable
                .Where(m => m.Title == movie1.Title && m.MovieMaker == movie1.MovieMaker)
                .Execute()
                .First();

            Assert.AreEqual(updatedMovie.MainActor, mainActor);
        }

        public void LinqInsert_WithNullInFirstColumnValue_Test()
        {
            Table<Movie> nerdMoviesTable = new Table<Movie>(_session, new MappingConfiguration());
            Movie movie1 = Movie.GetRandomMovie();
            movie1.MainActor = null; //Setting first column
            nerdMoviesTable.Insert(movie1, false).Execute();

            Movie updatedMovie = nerdMoviesTable
                .Where(m => m.Title == movie1.Title && m.MovieMaker == movie1.MovieMaker)
                .Execute()
                .First();

            Assert.IsNull(updatedMovie.MainActor);
        }

        public void LinqUpdate_Batch()
        {
            // Setup
            Table<Movie> table = new Table<Movie>(_session, new MappingConfiguration());
            table.CreateIfNotExists();
            Movie movieToUpdate1 = _movieList[1];
            Movie movieToUpdate2 = _movieList[2];

            BatchStatement batch = new BatchStatement();

            var expectedMovie1 = new Movie(movieToUpdate1.Title, movieToUpdate1.Director, "something_different_" + Randomm.RandomAlphaNum(10), movieToUpdate1.MovieMaker, 1212);
            var update1 = table.Where(m => m.Title == movieToUpdate1.Title && m.MovieMaker == movieToUpdate1.MovieMaker && m.Director == movieToUpdate1.Director)
                 .Select(m => new Movie { Year = expectedMovie1.Year, MainActor = expectedMovie1.MainActor })
                 .Update();
            batch.Add(update1);

            var expectedMovie2 = new Movie(movieToUpdate2.Title, movieToUpdate2.Director, "also_something_different_" + Randomm.RandomAlphaNum(10), movieToUpdate2.MovieMaker, 1212);
            var update2 = table.Where(m => m.Title == movieToUpdate2.Title && m.MovieMaker == movieToUpdate2.MovieMaker && m.Director == movieToUpdate2.Director)
                 .Select(m => new Movie { Year = expectedMovie2.Year, MainActor = expectedMovie2.MainActor })
                 .Update();
            batch.Add(update2);

            table.GetSession().Execute(batch);

            List<Movie> actualMovieList = table.Execute().ToList();
            Assert.AreEqual(_movieList.Count, actualMovieList.Count());
            Assert.AreNotEqual(expectedMovie1.MainActor, expectedMovie2.MainActor);
            Assert.IsFalse(Movie.ListContains(_movieList, expectedMovie1));
            Assert.IsFalse(Movie.ListContains(_movieList, expectedMovie2));
            Movie.AssertListContains(actualMovieList, expectedMovie1);
            Movie.AssertListContains(actualMovieList, expectedMovie2);
        }