How to get the amount of "true"?

-1

Follow the code below:

int total_true = ctx
    .MinhaTabela
    .Where(x => x.id == 5)
    .Select(x => new Tabela_DTO
    {
        Campo1 = x.Campo1, // database está como true
        Campo2 = x.Campo2, //database está como false
        Campo3 = x.Campo3, //database está como true
        Campo4 = x.Campo4 //database está como true
    })
    .ToArray()
    .Count(x => x == true);
//deve retornar 3;

I get the error on line .Count(x => x == true); :

  

The operator "==" can not be applied to operands of types   "Table_DTO" and "bool"

I want to know how many true has, in which case it should return 3 .

Here is a working example:

bool[] testArray = new bool[10] { true, false, true, true, false, true, true, true, false, false };
int i = testArray.Count(x => x== true);

The idea is the same, however, using select and tals ... and returning how many true has in the database. The goal is to count how many true has in the database.

    
asked by anonymous 31.07.2018 / 19:47

3 answers

6

This business of LINQ to get data from the database does not work as well as people imagine and brings several complications when it comes out of the trivial, and need not go very far. This is a case that seems simple and already complicates immensely. It worsens the fact that people do not know how LINQ works. In fact almost everyone who knows avoids its use in this form, or only uses in very simple cases.

There are several answers here in SOpt that teach how to use EF's LINQ in the wrong way, I already fall into this trap myself. The most tragic part is that it works and not always the person realizes the damage that is causing. Some users are giving multiple answers ignoring the fact that in EF LINQ is not translated directly.

For in-memory data is different, LINQ is much more useful, but it may have an improper performance cost.

EF LINQ converts the query in C # to a query SQL according to provider. Then you need to have:

  • A code well done in C #, which is not always possible or easy to do, especially when you do not have a lot of control over LINQ
  • A good LINQ provider for the database, which is also tricky to have for cases other than the trivial
  • LINQ itself is very good, which is not quite the case.

In this case or you create a C # code that will render the processing occur in memory, that is, it brings everything from the database and processes in your application, which can be a huge traffic without need, or creates a function in the database that can make use of it, which is not easy to make LINQ recognize.

I am not saying that it is appropriate, but this should give the result you want, even if not in a good way (I can not say in this case):

int total_true = ctx
    .MinhaTabela
    .Where(x => x.id == 5)
    .Select(x => (x.Campo1 ? 1 : 0) + (x.Campo2 ? 1 : 0) + (x.Campo3 ? 1 : 0) + (x.Campo4 ? 1 : 0)).ToList()[0];

I'm still not sure if this solves 100% of what you want, because I do not know if the question is clear. It may have some small difference.

On the other hand, it might be that you want something different from what is in the question, that would be like this:

int total_true = ctx
    .MinhaTabela
    .Where(x => x.id == 5)
    .Sum(x => (x.Campo1 ? 1 : 0) + (x.Campo2 ? 1 : 0) + (x.Campo3 ? 1 : 0) + (x.Campo4 ? 1 : 0));

Example code (remembering that if you use reflection there works fine, but in database ( IQueryable ). ) will cause something tragic for performance , which makes me think that all the answers that used this feature are, in the minimum, causing a bad effect, not to say wrong, since it works, but does not produce an acceptable result).

using static System.Console;
using System.Collections.Generic;
using System.Linq;

public class Program
{
    public static void Main(string[] args)
    {
        var lista = new List<Dados>()
        {
            new Dados
            {
                Campo1 = true,
                Campo2 = false,
                Campo3 = true,
                Campo4 = true
            },
            new Dados
            {
                Campo1 = false,
                Campo2 = false,
                Campo3 = true,
                Campo4 = false
            }
        };
        WriteLine(lista
            .Select(x => (x.Campo1 ? 1 : 0) + (x.Campo2 ? 1 : 0) + (x.Campo3 ? 1 : 0) + (x.Campo4 ? 1 : 0))
            .ToList()[0]);
        WriteLine(lista
            .Sum(x => (x.Campo1 ? 1 : 0) + (x.Campo2 ? 1 : 0) + (x.Campo3 ? 1 : 0) + (x.Campo4 ? 1 : 0)));
    }
}

public class Dados
{
    public bool Campo1 { get; set; }
    public bool Campo2 { get; set; }
    public bool Campo3 { get; set; }
    public bool Campo4 { get; set; }
}

See running on .NET Fiddle . And in Coding Ground . Also I placed GitHub for future reference .

Note that only the first case generates the requested result in the question. I did the other because the AP accepted another answer that gives this result.

The first case takes the individual case, which is described in the question. In the second, take the sum of all the lines. Count() can not be used because it is counting columns and not rows, the Count() count rows. The Count() is the same as a Sum() where the value of each line is always 1.

To tell you the truth, I think most providers will generate a bad query in the database even using this template without reflection, but it will already be a little better. It is possible, but I doubt that any LINQ provider in any database will be able to translate these if s into CASE s of SQL. What will be tragic. The time in hundreds of ms seems too high, there is something wrong there, but with the wrong model there is not much to do, I would need to reshape the database. I always say that modeling is the most important thing in software development. First make large data structures, the rest is fine if the database is well made.

    
31.07.2018 / 20:28
1

In a dynamic way using linq you could scan the properties of your class and check which ones are of type bool and add the ones that have the value to true.

See how I would do it.

using System.Collections.Generic;
using System.Linq;
using System.Reflection;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            var list = new List<Tabela_DTO>()
            {
                new Tabela_DTO()
                {
                    Campo1 = true,
                    Campo2 =false,
                    Campo3 = true,
                    Campo4 = true,
                    Campo5 = false,
                    Campo6 = true,
                    Campo7 = "",
                    Campo8 = 1,
                },
                new Tabela_DTO()
                {
                    Campo1 = false,
                    Campo2 =false,
                    Campo3 = true,
                    Campo4 = false,
                    Campo5 = false,
                    Campo6 = true,
                    Campo7 = "",
                    Campo8 = 2,
                },
            };
            int total_true = list.Select(x => new { total = GetTotal(x) }).Sum(x => x.total);
        }

        static int GetTotal(Tabela_DTO obj)
        {
            int total = 0;
            foreach (PropertyInfo propertyInfo in obj.GetType().GetProperties())
            {
                if (propertyInfo.PropertyType == typeof(bool) && (bool)propertyInfo.GetValue(obj, null) == true)
                {
                    total = total + 1;
                }
            }

            return total;
        }
    }

    public class Tabela_DTO
    {
        public bool Campo1 { get; set; }
        public bool Campo2 { get; set; }
        public bool Campo3 { get; set; }
        public bool Campo4 { get; set; }
        public bool Campo5 { get; set; }
        public bool Campo6 { get; set; }
        public string Campo7 { get; set; }
        public int Campo8 { get; set; }
    }
}

This will prevent you from having to explicitly select your fields or change the query every time some columnar changes.

Remembering that I'm not using Linq to entity but rather to an object list, you can give a .Tolist () in your table before using the example above.

    
01.08.2018 / 18:45
-2

Follow the code below:

int total_true = ctx
    .MinhaTabela
    .Where(x => x.id == 5)
    .Select(x => new 
    {
        x.Campo1, //true
        x.Campo2, //false
        x.Campo3, //true
        x.Campo4 //true
    })
    .ToArray()
    .Count(x => x.Campo1 == true || x.Campo2 == true || x.Campo3 == true || x.Campo4 == true ||);
//deve retornar 3;
    
31.07.2018 / 20:02