How to get the amount of "true"?

Question

Follows code:

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 error on line .Count(x => x == true);:

The "==" operator cannot be applied to type operands "Tabela_dto" and "bool"

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

Follow a functional 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 true, but using select and tals... and return how many true has in the database. The goal is to count how many true has in the database.

Answer 1

This LINQ business to take data from the database doesn’t work as well as people imagine and brings several complications when it comes out of the trivial, and doesn’t even need to 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 at Sopt that teach to use EF LINQ in the wrong way, I myself have already fallen into this trap. The tragic part is that it works and one doesn’t always realize the damage they are causing. Some users are giving several answers ignoring the fact that in EF LINQ is not directly translated.

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

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

• a code well done in C#, which is not always possible or easy to do, even more when it does not dominate much LINQ
• a good LINQ provider for the database, which is also complicated to have for cases beyond the trivial
• The LINQ itself be very good, which is not quite the case.

In this case or you create a code in C# that will make the processing take place 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, which is not simple to make LINQ recognize.

I’m not saying it’s appropriate, but it should give the result you want, even if not in a good way (I can’t 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 don’t know if the question is clear. There may be some small difference.

On the other hand it may be that you want something other than what is in the question, then it 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));

Code example (remembering that if use reflection there works well, but in database (IQueryable) will cause something tragic for the performance, which makes me think that all the answers that used this resource are at least 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; }
}

Behold working in the ideone. And in the .NET Fiddle. Also put on the Github for future reference.

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

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

To tell the truth I think that most providers will generate a query bad in the database even using this model without reflection, but it will already be a little better. is possible, but I doubt that any LINQ provider of any database can translate these ifs in CASEs of SQL. What will be tragic. Time in hundreds of ms I think it’s too high, there’s something wrong there, but with the wrong model there’s not much to do, I’d need to remodel the database. I always say that modeling is the most important thing in software development. First make large data structures, the rest is solved well if the base is well made.

Answer 2

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

See how I’d do.

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 way avoid you having to explicitly select your fields or change the query every time any columnar changes.

Remembering that I am not using the entity and yes for an object list, you can give one . Tolist() in your table before using the above example.

Answer 3

Follows code:

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;