Correct LINQ search syntax in MVC

Question

Correct LINQ search syntax in MVC

Asked 4 years ago

Viewed 47 times

0

I built an application with MVC that performs a query in an SQL database and returns it as a table, and now I want to add a search bar to that screen. I’ve already set up my "Index.cshtml" view (with the search bar included):

@model List<Pessoa>
@{
    ViewBag.Title = "Consulta de Pessoas";
}
@using ConsultaPessoas.Controllers;
@using ConsultaPessoas.Models
@using ConsultaPessoas.Controllers;

<meta name="viewport" content="width=device-width, initial-scale=1.0">

<style type="text/css">

    h1 {
        font-family: cursive;
        color: darkblue;
        padding: 5px;
        top: 0;
    }

    .tabela-pessoas {
        border-collapse: collapse;
        margin: 25px 0;
        font-size: 0.9em;
        font-family: cursive;
        box-shadow: 0 0 20px rgba(0, 0, 0, 0.15);
    }

        .tabela-pessoas thead tr {
            background-color: darkblue;
            text-align: center;
            color: ghostwhite;
            border-spacing: 1px;
        }

    th {
        padding: 3px;
        text-align: center;
        color: white;
    }


    td {
        padding-left: 5px;
        padding-right: 5px;
        color: black;
        text-align: left;
    }

    .tabela-pessoas tbody tr {
        border-bottom: 1px solid cornflowerblue;
    }

    .tabela-pessoas td:hover {
        background-color: lightgray;
    }
</style>



<h1>Consulta de Pessoas</h1>

@using (Html.BeginForm())
{
    <p>
        Pesquise pelo nome ou matrícula  : @Html.TextBox("Pesquisa")<br />
        <input type="submit" value="Pesquisar" />
    </p>
}

<table class="tabela-pessoas">

    <thead>
        <tr>
            <th style="border-radius: 10px 0px 0px 10px; padding-left:5px;">Matricula</th>
            <th>Login</th>
            <th>Nome</th>
            <th>E-mail</th>
            <th>Departamento</th>
            <th>Unidade</th>
            <th>Cargo</th>
            <th>EstadoCivil</th>
            <th>Cidade</th>
            <th>Bairro</th>
            <th>Endereco</th>
            <th>CPF</th>
            <th>RG</th>
            <th>CEP</th>
            <th>Empresa</th>
            <th>Estabelecimento</th>
            <th>Codigo Centro Custo</th>
            <th>Descrição Centro de Custo</th>
            <th>Código Cargo</th>
            <th>Unidade de Lotação</th>
            <th>Data de Nascimento</th>
            <th>Data de Admissão</th>
            <th>Ramal</th>
            <th>Descrição Estabelecimento</th>
            <th>Data Desligamento</th>
            <th>Tipo Ramal</th>
            <th>Celular</th>
            <th>Situação Afastamento</th>
            <th style="border-radius: 0px 10px 10px 0px;">Aviso Prévio</th>
        </tr>
    </thead>

    @foreach (Pessoa pessoa in Model)
    {
        <tbody>
            <tr>
                <td>@pessoa.Matricula</td>
                <td>@pessoa.Login</td>
                <td>@pessoa.Nome</td>
                <td>@pessoa.Email</td>
                <td>@pessoa.Depto</td>
                <td>@pessoa.Unidade</td>
                <td>@pessoa.Cargo</td>
                <td>@pessoa.EstadoCivil</td>
                <td>@pessoa.Cidade</td>
                <td>@pessoa.Bairro</td>
                <td>@pessoa.Endereco</td>
                <td>@pessoa.Cpf</td>
                <td>@pessoa.Rg</td>
                <td>@pessoa.Cep</td>
                <td>@pessoa.Empresa</td>
                <td>@pessoa.Estabelecimento</td>
                <td>@pessoa.CodCentroCusto</td>
                <td>@pessoa.DescCC</td>
                <td>@pessoa.CodCargo</td>
                <td>@pessoa.UnidadeLotacao</td>
                <td>@pessoa.DataNascimento</td>
                <td>@pessoa.DataAdmissao</td>
                <td>@pessoa.Ramal</td>
                <td>@pessoa.DescEstabel</td>
                <td>@pessoa.DataDesligamento</td>
                <td>@pessoa.TipoRamal</td>
                <td>@pessoa.Celular</td>
                <td>@pessoa.SitAfast</td>
                <td>@pessoa.AvisPrev</td>
            </tr>
        </tbody>
    }


</table>

And this is my Controller:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.Odbc;
using System.Data.Common;
using System.Data.SqlClient;
using ConsultaPessoas.Models;
using System.Web.UI.WebControls;
using Newtonsoft.Json;
using System.Web.UI.WebControls.WebParts;

namespace ConsultaPessoas.Controllers
{
    public class HomeController : Controller
    {
        [HttpGet]
        public ActionResult Index()
        {
            var connectionString = "Data Source=BLABLABLA;Initial Catalog=XPTO;User ID=Usuario;Password=Senha";
            

            var model = new List<Pessoa>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                String sql = "SELECT * FROM Pessoas";
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    var pessoa = new Pessoa();
                    pessoa.Matricula = rdr["matr"].ToString();
                    pessoa.Login = rdr["login"].ToString();
                    pessoa.Nome = rdr["nome"].ToString();
                    pessoa.Email = rdr["email"].ToString();
                    pessoa.Depto = rdr["dpto"].ToString();
                    pessoa.Unidade = rdr["unidd"].ToString();
                    pessoa.Cargo = rdr["cargo"].ToString();
                    pessoa.EstadoCivil = rdr["estado_civil"].ToString();
                    pessoa.Cidade = rdr["cidade"].ToString();
                    pessoa.Bairro = rdr["bairro"].ToString();
                    pessoa.Endereco = rdr["endereco"].ToString();
                    pessoa.Cpf = rdr["cpf"].ToString();
                    pessoa.Rg = rdr["rg"].ToString();
                    pessoa.Cep = rdr["cep"].ToString();
                    pessoa.Empresa = rdr["empresa"].ToString();
                    pessoa.Estabelecimento = rdr["estabelecimento"].ToString();
                    pessoa.CodCentroCusto = rdr["codigo_cc"].ToString();
                    pessoa.DescCC = rdr["descr_cc"].ToString();
                    pessoa.CodCargo = rdr["cod_cargo"].ToString();
                    pessoa.UnidadeLotacao = rdr["unid_lotacao"].ToString();
                    pessoa.DataNascimento = rdr["dtnascimento"].ToString();
                    pessoa.DataAdmissao = rdr["dtAdmissao"].ToString();
                    pessoa.Ramal = rdr["ramal"].ToString();
                    pessoa.DescEstabel = rdr["desc_estabel"].ToString();
                    pessoa.DataDesligamento = rdr["dt_desligto"].ToString();
                    pessoa.TipoRamal = rdr["tipo_ramal"].ToString();
                    pessoa.Celular = rdr["celular"].ToString();
                    pessoa.SitAfast = rdr["SitAfast"].ToString();
                    pessoa.AvisPrev = rdr["AvisPrev"].ToString();
                    model.Add(pessoa);
                }

                return  View(model);
            }

        }
        [HttpPost]
        public ActionResult Index (string pesquisa)
        {
            var search = from model in pesquisa select model;
            return View("Index", search);
        }
    }
}

The problem is in this LINQ query in [Httppost], the page loads normally, but when I click on the Ubmit button, I get an error message indicating that the search result cannot be null, however I believe that the syntax I am using is not correct. Thanks in advance.

What is this code that matters Controller to View? (This can’t be cool)

– novic

2020/10/27 at 16:23
Hello! I’m sorry, I don’t understand...which code matters controller for view?

– Pedro Perondini

2020/10/27 at 16:26
@using ConsultaPessoas.Controllers; that code should not exist there at all!

– novic

2020/10/27 at 16:27
To View only shows data function is to see information, in rare cases some peculiar decision code, at most.

– novic

2020/10/27 at 16:28
1

Thanks for warning, I had put there only to do some tests, but it is not essential for the operation of the application, I already removed!

– Pedro Perondini

2020/10/27 at 16:30
1

Each method in a Controller is independent, you used one model that is being loaded in Index Get, in Index Post has no load, as are independent controls this variable needs a new load of information. That Code in Index Get needs to be refactored to another layer of service for example or crud layer etc

– novic

2020/10/27 at 16:33

Show 1 more comment

1 answer

Browser other questions tagged c# .net web-application mvc linq

You are not signed in. Login or sign up in order to post.

by novic • **35,673** points · Answer 1 · 2020-10-27T16:44:30+00:00

The initial problem is not the LINQ, and how your code is being organized. Your code is spelled wrong LINQ, but, it is also unnecessary to use it, follow the minimum example created by me that solves your problem, at least makes understand how it should be organized, there are better options and the main purpose is to show the separation of responsibility of the layers.

An example: create a Service layer to offer you the list of people at any time:

public class ServicoPeople 
{
    private connectionString = "";
    public ServicoPeople()
    {
        connectionString = "Data Source=BLABLABLA;Initial Catalog=XPTO;User ID=Usuario;Password=Senha";
    }
    
    public List<People> Get(string pesquisa)
    {
        List<Pessoa> model = new List<Pessoa>();
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            String sql = "SELECT * FROM Pessoas";
            if (!string.IsNullOrEmpty(pesquisa))
            {
                sql += " WHERE nome = @Nome ";              
            }           
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (!string.IsNullOrEmpty(pesquisa))
            {               
                cmp.Parameters.Add("@Nome", pesquisa);
            }           
            conn.Open();
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    var pessoa = new Pessoa();
                    pessoa.Matricula = rdr["matr"].ToString();
                    pessoa.Login = rdr["login"].ToString();
                    pessoa.Nome = rdr["nome"].ToString();
                    pessoa.Email = rdr["email"].ToString();
                    pessoa.Depto = rdr["dpto"].ToString();
                    pessoa.Unidade = rdr["unidd"].ToString();
                    pessoa.Cargo = rdr["cargo"].ToString();
                    pessoa.EstadoCivil = rdr["estado_civil"].ToString();
                    pessoa.Cidade = rdr["cidade"].ToString();
                    pessoa.Bairro = rdr["bairro"].ToString();
                    pessoa.Endereco = rdr["endereco"].ToString();
                    pessoa.Cpf = rdr["cpf"].ToString();
                    pessoa.Rg = rdr["rg"].ToString();
                    pessoa.Cep = rdr["cep"].ToString();
                    pessoa.Empresa = rdr["empresa"].ToString();
                    pessoa.Estabelecimento = rdr["estabelecimento"].ToString();
                    pessoa.CodCentroCusto = rdr["codigo_cc"].ToString();
                    pessoa.DescCC = rdr["descr_cc"].ToString();
                    pessoa.CodCargo = rdr["cod_cargo"].ToString();
                    pessoa.UnidadeLotacao = rdr["unid_lotacao"].ToString();
                    pessoa.DataNascimento = rdr["dtnascimento"].ToString();
                    pessoa.DataAdmissao = rdr["dtAdmissao"].ToString();
                    pessoa.Ramal = rdr["ramal"].ToString();
                    pessoa.DescEstabel = rdr["desc_estabel"].ToString();
                    pessoa.DataDesligamento = rdr["dt_desligto"].ToString();
                    pessoa.TipoRamal = rdr["tipo_ramal"].ToString();
                    pessoa.Celular = rdr["celular"].ToString();
                    pessoa.SitAfast = rdr["SitAfast"].ToString();
                    pessoa.AvisPrev = rdr["AvisPrev"].ToString();
                    model.Add(pessoa);
                }
            }           
        }
        return model;
    }
}

and in the Controller:

namespace ConsultaPessoas.Controllers
{
    public class HomeController : Controller
    {
        private ServicoPeople ServicoPeople { get; }
        public HomeController()
        {
            ServicoPeople = new ServicoPeople();
        }
        [HttpGet]
        public ActionResult Index()
        {
            return View(ServicoPeople.Get(null));
        }
        [HttpPost]
        public ActionResult Index (string pesquisa)
        {           
            return View("Index", ServicoPeople.Get(pesquisa));
        }
    }
}

is one more example to organize and have this code available anywhere in the application.

Could use ORM (Entity Framework) and Dapper (Micro ORM) to not have to do these conversions by hand.