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.