Filter Postgresql Information on Windows Forms screen using Entity Framework

Question

Is there any way to search for the information in postgresql by nickname? But without using alias, only in the field register in the table I already enter the real name for the field?

Example:

1. id_client will be named "Client Code"
2. dtNascimento as the name of "Date of Birth"

Because I would like to create a generic class that works for any table, thus putting the field name as a filter. Something like:

Where the field for the filter will be the table field with the real name.

That so each field added in the table in the future can already be used as a filter in a search screen and already with the real name for the user.

Is there any way to do that? I just need some hint of what research the code will turn me around, but I don’t know what technology to use or where to start.

Answer 1

Yes. Use [Column]:

[Column("Código Cliente")]
public int ClienteId { get; set; }

[Column("Data de Nascimento")]
public DateTime DataNascimento { get; set; }

---

The question title should be "how to filter in Winforms by the name of the displayed column". I did a simulation and then I must edit this answer and put the example in a chapter of Coding Craft because I unintentionally made a tutorial on how to use Entity Framework with Windows Forms and Postgresql.

Let’s go to the questions:

Is there any way to search the information in postgresql by nickname? but without using alias, only in the field registration in the table I already inform the real name for the field?

No. You need to pass the name of the column yourself. What you can do is the user interface display the friendly column name and the code receive the column name as it is in fact.

Because I would like to create a generic class that works for any table, thus putting the field name as a filter.

That involves using a lot Reflection. Using the "field name" is not a good idea, but you can display the field name and get another value.

To illustrate, I will make a full example.

Model and Tela

I modeled a class Cliente as an example:

public class Cliente
{
    [Key]
    [DisplayName("Identificador do Cliente")]
    public Guid ClienteId { get; set; }

    [Required]
    [DisplayName("Nome do Cliente")]
    public String Nome { get; set; }
    [Required]
    [DisplayName("Nome Fantasia")]
    public String Fantasia { get; set; }
}

That is, each property of the class of Cliente has an attribute [DisplayName]. This attribute will be what I will display to the user.

Once done, I can make the following code to popular the compo "Search Fields":

        var camposPesquisa = typeof(Cliente).GetProperties(BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public |
                           BindingFlags.NonPublic | BindingFlags.FlattenHierarchy)
                           .Select(p => new {
                               Valor = p.Name,
                               Texto = p.GetCustomAttribute<DisplayNameAttribute>().DisplayName
                           }).ToList();

        comboBoxCampoPesquisa.ValueMember = "Valor";
        comboBoxCampoPesquisa.DisplayMember = "Texto";
        comboBoxCampoPesquisa.DataSource = camposPesquisa;

That is, my screen will display the values of [DisplayName], but the values that will go into the code are the names of my fields:

I can do the same thing for the condition by defining a Enum Condicao and put attributes [Display] in them:

public enum Condicao
{
    [Display(Name = "Igual")]
    Igual,
    [Display(Name = "Diferente")]
    Diferente,
    [Display(Name = "Maior")]
    Maior,
    [Display(Name = "Menor")]
    Menor,
    [Display(Name = "Maior ou Igual")]
    MaiorOuIgual,
    [Display(Name = "Menor ou Igual")]
    MenorOuIgual
}

To extract the attribute of Enums, you will need an extension method:

public static class EnumExtensions
{
    public static TAttribute GetAttribute<TAttribute>(this Enum enumValue)
            where TAttribute : Attribute
    {
        return enumValue.GetType()
                        .GetMember(enumValue.ToString())
                        .First()
                        .GetCustomAttribute<TAttribute>();
    }
}

To use it like this:

        var condicoes = Enum.GetValues(typeof(Condicao))
            .Cast<Condicao>()
            .Select(c => new
            {
                Valor = c.ToString(),
                Texto = c.GetAttribute<DisplayAttribute>().Name
            })
            .ToList();

        comboBoxCondicao.ValueMember = "Valor";
        comboBoxCondicao.DisplayMember = "Texto";
        comboBoxCondicao.DataSource = condicoes;

And then:

Grid

I’m guessing the initialization of the Grid is made in the event OnLoad of your Form

    protected override void OnLoad(EventArgs e)
    {
        base.OnLoad(e);
        context.Clientes.Load();

        clienteBindingSource.DataSource =
            context.Clientes.Local.ToBindingList();

        ...
    }

Only with this I have the research of the data and the proper observation by them through the context of the Entity Framework. Done this, I need to put a CheckBox and an event for it to trigger the filtering at the appropriate time:

    private void checkBoxFiltrar_CheckedChanged(object sender, EventArgs e)
    {
        Filtrar(checkBoxFiltrar.Checked);
    }

Just as a test, I filtered by name as follows:

    protected void Filtrar(bool checkFiltrar)
    {
        if (checkFiltrar)
            clienteBindingSource.DataSource = context.Clientes.Where(c => c.Nome == textBoxValor.Text).ToList();
        else
            clienteBindingSource.DataSource = context.Clientes.Local.ToBindingList();

        dataGridView.Refresh();
    }

Here is the starting point for a more robust logic. There are two ways to do it: using the library System.Linq.Dynamic or riding Expressions. For this answer I will use the first (which, at bottom, does the same thing as the second).

Predicados Dinâmica

Let’s just assemble the dynamic predicate for Condicao.Igual:

    protected void Filtrar(bool checkFiltrar)
    {
        if (checkFiltrar)
            // clienteBindingSource.DataSource = context.Clientes.Where(c => c.Nome == textBoxValor.Text).ToList();
            clienteBindingSource.DataSource = context.Clientes
                .Where(comboBoxCampoPesquisa.SelectedValue.ToString() + " == @0", textBoxValor.Text)
                .ToList();
        else
            clienteBindingSource.DataSource = context.Clientes.Local.ToBindingList();

        dataGridView.Refresh();
    }

Checking whether it works:

Finally, as I want to search not for the exact text, but for a part of it, I can do the following:

    protected void Filtrar(bool checkFiltrar)
    {
        if (checkFiltrar)
            // clienteBindingSource.DataSource = context.Clientes.Where(c => c.Nome == textBoxValor.Text).ToList();
            clienteBindingSource.DataSource = context.Clientes
                // .Where(comboBoxCampoPesquisa.SelectedValue.ToString() + " == @0", textBoxValor.Text)
                .Where(comboBoxCampoPesquisa.SelectedValue.ToString() + ".Contains(@0)", textBoxValor.Text)
                .ToList();
        else
            clienteBindingSource.DataSource = context.Clientes.Local.ToBindingList();

        dataGridView.Refresh();
    }

Convert the Enum to an operator String is like homework.