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 if
s in CASE
s 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.
Have you tried
.Where(x => x.id == 5 && x => x.total_true == true)
?– Marconi
@Marconi
x.total_true
there is no.– Matheus Miranda
x
be the typeTabela_DTO
that owns the fieldsCampo1
,Campo2
,Campo3
andCampo4
. What you hope to compare withtrue
in that case?– Woss
@Matheusmiranda I understand what you want, but I don’t quite understand where you’re going, I could explain your doubt better?
– Marconi
@Marconi wants to know how much it’s worth
true
, then you must return3
.– Matheus Miranda
@Andersoncarloswoss I want him to return
3
, because it has 3 valuetrue
and 1false
.– Matheus Miranda
@Matheusmiranda you need to specifically solve this problem or want a more flexible solution?
– Rafael Carvalho
@Rafaelherik with specific form I am very grateful.
– Matheus Miranda
@Matheusmiranda I think that failed to express his real need, your Where returns more than 1 record? You used the . Toarray before Count for what purpose? Your "Minhatabela" table has 4 boleanos fields, your goal is to count how many of these fields are true only?
– Rafael Carvalho
@Rafaelherik the goal is to count how many
true
has. It seems that there is no way to do direct... It seems that I have to do aforeach
.– Matheus Miranda
@Matheusmiranda I left a reply ai, I did the test with data in the same structure and it worked correctly.
– Rafael Carvalho