C# Table.Project方法代码示例

 public void Consulta_Zerando_Project_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.Project(null);
     string sql2 = "SELECT * FROM users WHERE users.name LIKE @users_name_1";
     Assert.AreEqual(sql2, users.ToSql());
 }

 public void Consulta_Simples_Com_Disctinct()
 {
     var users = new Table("users");
     users.Project(users["nascimento"])
         .Where(users["nome"].Like("%g"))
         .Distinct();
     string sql_expected = "SELECT DISTINCT users.nascimento FROM users WHERE users.nome LIKE @users_nome_1";
     Assert.AreEqual(sql_expected, users.ToSql());
 }

 public void Consulta_Com_Subselect_No_Where()
 {
     var t_users = new Table("tbUsers");
     var t_groups = new Table("tbGroups");
     t_users.Project(t_users.All)
         .Where(t_users["status"] == t_groups.Project(t_groups["status"]).Where(t_groups["id"] == 1));
     string sql_expected = "SELECT tbUsers.* FROM tbUsers WHERE tbUsers.status = "
         +"(SELECT tbGroups.status FROM tbGroups WHERE tbGroups.id = @tbGroups_id_1)";
     Assert.AreEqual(sql_expected, t_users.ToSql());
 }

 public void Consulta_Com_Order_By()
 {
     var users = new Table("users");
     users.Project(users["name"], users["password"])
         .Where(users["active"] == true)
         .OrderBy(users["created_at"].Desc, users["name"]);
     string sql_expected = "SELECT users.name, users.password FROM users WHERE users.active = @users_active_1 "
         +"ORDER BY users.created_at DESC, users.name";
     Assert.AreEqual(sql_expected, users.ToSql());
 }

 public void Usar_Mais_De_Uma_Table_No_Select_E_Join()
 {
     var alunos = new Table("alunos");
     var cursos = new Table("cursos");
     alunos.Project(alunos.All, cursos["nome"])
         .Join(cursos)
         .On(alunos["curso_id"] == cursos["id"]);
     string sql = "SELECT alunos.*, cursos.nome FROM alunos JOIN cursos ON alunos.curso_id = cursos.id";
     Assert.AreEqual(sql, alunos.ToSql());
 }

 public void Usar_In_Sem_Ser_Array_De_String()
 {
     ITable users = new Table("users");
     users.Project(users.All)
         .Where(users["permissoes"].In("can_remove_user", "can_add_user"));
     string sql_expected = "SELECT users.* FROM users WHERE users.permissoes IN ('can_remove_user', 'can_add_user')";
     Assert.AreEqual(sql_expected, users.ToSql());
 }

 public void Fazer_Consulta_Com_Like()
 {
     var users = new Table("users");
     users.Project(users["nome"], users["senha"])
         .Where(users["nome"].Like("%n"));
     string sql_expected = "SELECT users.nome, users.senha FROM users WHERE users.nome LIKE @users_nome_1";
     Assert.AreEqual(sql_expected, users.ToSql());
 }

 public void Fazer_Consulta_Com_Maior_Igual_E_Menor_Igual()
 {
     var users = new Table("users", "u");
     users.Project(users.All)
         .Where((users["idade"] >= 10) & (users["idade"] <= 20));
     string sql_expected = "SELECT u.* FROM users AS u WHERE (u.idade >= @users_idade_1) AND (u.idade <= @users_idade_2)";
     Assert.AreEqual(sql_expected, users.ToSql());
 }

 public void Fazer_Consulta_E_Ver_Parametros()
 {
     var users = new Table("users", "u");
     var groups = new Table("groups", "g");
     users.Project(users.All, groups["nome"])
         .LeftJoin(groups).On(groups["id"] == users["groups_id"])
         .Where(users["id"] > 10)
         .Where(groups["id"] > 20);
     string sql_expected = "SELECT u.*, g.nome FROM users AS u LEFT JOIN groups AS g ON g.id = u.groups_id WHERE u.id > @users_id_1 AND g.id > @groups_id_1";
     Assert.AreEqual(sql_expected, users.ToSql());
     Assert.AreEqual(users.Params["users_id_1"], 10);
     Assert.AreEqual(groups.Params["groups_id_1"], 20);
 }

 public void Usar_Top_Negativo_Deve_Retornar_Excecao_InvalidClauseException()
 {
     try
     {
         var users = new Table("users");
         users.Project(users.All).OrderBy(users["nome"].Desc)
             .Top(-20);
         Assert.Fail();
     }
     catch (InvalidClauseException)
     {
         Assert.IsTrue(true);
     }
 }

 public void Usar_Table_Com_Alias()
 {
     var users = new Table("users", "u");
     users.Project(users["nome"], users["senha"]);
     Assert.AreEqual("SELECT u.nome, u.senha FROM users AS u", users.ToSql());
 }

 public void Usar_Function_Count_Com_Having_E_Join()
 {
     var employee = new Table("employee");
     var department = new Table("department");
     employee.Project(employee["DepartmentName"], F.Count(employee.All))
         .Join(department).On(employee["DepartmentID"] == department["DepartmentID"])
         .GroupBy(department["DepartmentName"])
         .Having(F.Count(employee.All) > 1);
     string sql_expected = "SELECT employee.DepartmentName, COUNT(employee.*) FROM employee JOIN department ON " +
         "employee.DepartmentID = department.DepartmentID GROUP BY department.DepartmentName HAVING COUNT(employee.*)"+
     " > @employee_count_*_1";
     Assert.AreEqual(sql_expected, employee.ToSql());
 }

 public void Usar_Function_Count_Com_Alias()
 {
     var users = new Table("users");
     users.Project(F.Count(users["ativo"]).As("count_usuarios_ativos"));
     string sql_expected = "SELECT COUNT(users.ativo) AS count_usuarios_ativos FROM users";
     Assert.AreEqual(sql_expected, users.ToSql());
 }

 public void Usar_Field_Com_Alias()
 {
     var users = new Table("users");
     users.Project(users["nome"].As("nome_do_usuario"), users["senha"]);
     Assert.AreEqual("SELECT users.nome AS nome_do_usuario, users.senha FROM users", users.ToSql());
     var groups = new Table("groups");
     users.Join(groups).On(users["nome"] == groups["nome_user"]);
     Assert.AreEqual("SELECT users.nome AS nome_do_usuario, users.senha FROM users JOIN groups ON nome_do_usuario = groups.nome_user", users.ToSql());
 }

 public void Usar_Entidade_User_E_Group_Com_Join_E_Where()
 {
     var user = new User("george");
     user.Ativo = true;
     var group = new Group("admin");
     group.AddPermissao("can_add_user");
     group.AddPermissao("can_remove_user");
     group.AddUser(user);
     ITable t_users = new Table("users");
     ITable t_groups = new Table("groups");
     t_users.Project(t_users["nome"], t_groups["descricao"])
         .Join(t_groups).On(t_users["group_id"] == t_groups["id"]);
     if (!string.IsNullOrEmpty(user.Nome))
     {
         t_users.Where(t_users["nome"].Like(user.Nome));
     }
     if (user.Ativo.HasValue)
     {
         t_users.Where(t_users["ativo"] == user.Ativo);
     }
     if (user.Nivel.HasValue)
     {
         t_users.Where(t_users["nivel"] == user.Nivel);
     }
     if (user.Data.HasValue)
     {
         t_users.Where(t_users["data"] > user.Data);
     }
     if (group.Permissoes.Count() > 0)
     {
         t_users.Where(t_groups["permissoes"].In(group.Permissoes.ToArray()));
     }
     string sql_expected = "SELECT users.nome, groups.descricao FROM users JOIN groups ON users.group_id = groups.id "
         + "WHERE users.nome LIKE @users_nome_1 AND users.ativo = @users_ativo_1 AND users.data > @users_data_1 "
         + "AND groups.permissoes IN ('can_add_user', 'can_remove_user')";
     Assert.AreEqual(sql_expected, t_users.ToSql());
 }