Consult Context connection RRS feed

  • Question

  • User1329399308 posted

    I would need help to know how I can make a query where the where depends on the selected values ​​in various checks.

    The search for the word in the Search box must be contained in the columns marked in the checks. The query that I detail below does not look at the marked fields, it searches in all the columns.

    Puede ser una imagen de texto que dice "Buscar En los campos Login Nombre Con marca: Buscar Apellidos Eliminar E-mail 56 resultados Nombre completo Login Tipo usuario Fecha Alta"

    listUsers = (from u in dbContect.Users
                                  join ut in dbContect.UserTypes
                                  on u.UserTypesId equals ut.Id
                                 where ((oClassUser.f_name ? u.Name.Contains(oClassUser.f_seed) : u.Id < 0) || (oClassUser.f_login ? u.Login.Contains(oClassUser.f_seed) : u.Id < 0) || (oClassUser.f_email ? u.Email.Contains(oClassUser.f_seed) : u.Id <0) || (oClassUser.f_surname ? u.Surname.Contains(oClassUser.f_seed) : u.Id < 0))
                                  select new ClassUser
                                      id = u.Id,
                                      login = u.Login,
                                      name = u.Name,
                                      surname = u.Surname,
                                      Surname_Name = u.Surname + ", " + u.Name,
                                      email = u.Email,
                                      user_types_id = u.UserTypesId,
                                      desc_type = ut.Name,
                                      fecha_alta = u.Creationdate

    Tuesday, March 9, 2021 10:34 PM

All replies

  • User1312693872 posted


    Could you please show the Models? What is the oclassusers?Does it used to pass the input value?

    Do you want to input a word in Buscar and then search it by the following four columns? For example, input 'abc', select Name, then show the

    related rows if Name column contains the 'abc'?

    If so, lock the User first, then pass selected value to controller and  use if else to decide which column to search, check this demo:

            public IActionResult Search(ClassUser oClassUser,string check)
                var listUsers = from u in dbContect.Users
                                join ut in dbContect.UserTypes
                                on u.UserTypesId equals ut.Id
                                select u;
                if (check == "Name")
                    listUsers.Where(m => m.Name==oClassUser.f_name);
                return RedirectToAction("Index");

    Best Regards,

    Jerry Cai

    Wednesday, March 10, 2021 5:54 AM
  • User1329399308 posted

    Thanks Jerry,

    but your answer would not help me. I have 4 checks that can cause multiple situations that would imply creating multiple "ifs" validating which fields are selected.

    I enclose the screen of the models and the views in case you can give more information to solve the doubt.

    The Model:

    namespace dbContext.Models
        public class ClassUser
            [Display(Name = "Id")]
            public int id { get; set; }
            [Display(Name = "Login")]
            public string login { get; set; }
            [Display(Name = "Nombre")]
            public string name { get; set; }
            [Display(Name = "Apellido")]
            public string surname { get; set; }
            [Display(Name = "Email")]
            public string email { get; set; }
            [Display(Name = "IdUsuario")]
            public int? user_types_id { get; set; }
            [Display(Name = "Tipo usuario")]
            public string desc_type { get; set; }
            [Display(Name = "Fecha Alta")]
            [DisplayFormat(DataFormatString = "{0: dd/MM/yyyy}")]
            public DateTime fecha_alta { get; set; }
            [Display(Name = "Nombre completo")]
            public string Surname_Name { get; set; }
            [Display(Name = "IdGrupo")]
            public int? IdGroup{ get; set; }

            public string f_seed { get; set; }

            public bool f_login { get; set; }
            public bool f_name { get; set; }
            public bool f_surname { get; set; }
            public bool f_email { get; set; }

            public static bool? isGod;
            public static bool? hasmenu;


    The View:

    @model IEnumerable<roject.Models.ClassUser>

        ViewData["Title"] = ViewBag.titleSection;

    <div id="headSection">
        @if (ViewBag.titleSection != "")
            <h2><span class="image"></span><span class="title">@ViewBag.titleSection</span></h2>

    <div id="bodySection">
        <div id="template">
            <div class="controls">
                <form asp-controller="User" asp-action="Search" method="post" id="frmEnviar">
                    <div class="row">
                        <span class="field">Buscar</span>
                        <span class="value"><input type="text" class="text" value="" name="f_seed"></span>
                    <div class="row">
                        <span class="field">En los campos</span>
                        <span class="value">
                            <input type="checkbox" id="ckf_login" name="ckf_login" style="margin-right:3px;">Login
                            <input type="checkbox" id="ckf_name" name="ckf_name" style="margin-right:3px;">Nombre
                            <input type="checkbox" id="ckf_surname" name="ckf_surname" style="margin-right:3px;">Apellidos
                            <input type="checkbox" id="ckf_email" name="ckf_email" style="margin-right:3px;">E-mail
                    <div class="row options">
                        <span class="option"><input type="button" class="option" name="send" value="Buscar" onclick="Buscar()"></span>

                    <input type="hidden" id="f_login" name="f_login" />
                    <input type="hidden" id="f_name" name="f_name" />
                    <input type="hidden" id="f_surname" name="f_surname" />
                    <input type="hidden" id="f_email" name="f_email" />


            <div class="options">Con marca: <input type="submit" value="Eliminar" name="delete"></div>
            <div class="nresults"><span>@Model.Count() resultados</span></div>
            <table class="results">
                    <tr class="header">
                            @Html.DisplayNameFor(model => model.Surname_Name)
                            @Html.DisplayNameFor(model => model.login)
                            @Html.DisplayNameFor(model => model.desc_type)
                        <th> @Html.DisplayNameFor(model => model.fecha_alta)</th>
                    @foreach (var item in Model)
                            <td class="check">
                                <input type="checkbox" name="id" value="@Html.DisplayFor(modelItem => item.id)">
                            <td>@Html.ActionLink(item.Surname_Name, "View", new { Id = @item.id })</td>

                                @Html.DisplayFor(modelItem => item.login)
                                @Html.DisplayFor(modelItem => item.desc_type)
                                @Html.DisplayFor(modelItem => item.fecha_alta)
                function Buscar() {    
                    var cklogin = $('input#ckf_login').is(":checked", true);
                    var ckname = $('input#ckf_name').is(":checked", true);
                    var cksurname = $('input#ckf_surname').is(":checked", true);
                    var ckemail = $('input#ckf_email').is(":checked", true);
                    document.getElementById("f_login").value = cklogin;                  
                    document.getElementById("f_name").value = ckname;          
                    document.getElementById("f_surname").value = cksurname;                  
                    document.getElementById("f_email").value = ckemail;                  

    Wednesday, March 10, 2021 9:11 AM
  • User1312693872 posted


    Your where will just get true or false, it won't filter anything.

    You can select every rows that u.UserTypesId equals ut.Id first, then use four if to select. For example, you click Login, the check will  get the

    value 'Login', then you can filter by Login.  Four checks with four if, that demo is an exmaple.

    If I misunderstood anything, could you please explain your needs?

    Best Regards,

    Jerry Cai

    Friday, March 12, 2021 9:14 AM