CODEWITHSUNDEEP
c#sqllinqnhibernate
user2107630
user2107630

Reputation: 75

Multiple counts on single table in LINQ query

I am having trouble doing multiple counts on a single table in a LINQ query. I am using NHibernate, LINQ to NHibernate and C#.

query is a populated list. I have a table that has a boolean called FullRef. I want to do a LINQ query to give a count of occurances of FullRef = false and FullRef = true on each TrackId. TrackId gets a new row for each time he gets a track.Source == "UserRef".

In the following query I get the correct number count (from the FullRefTrueCount) of FullRef = true, but it gives an unknown wrong number on the FullRefFalseCount. 

var query2 = from track in query
    where track.Source == "UserRef"
    group track by new { TrackId = track.TrackId, FullRef = track.FullRef } into d

    select new FullReferrer
    {
        Customer = d.Key.TrackId,
        FullRefFalseCount = d.Where(x => x.FullRef == false).Count(),
        FullRefTrueCount = d.Where(x => x.FullRef == true).Count()
    };

I also tried to modify it to not contain the FullRef in the group by. This was done by removing FullRef = track.FullRef on the by like this

var query2 = from track in query
    where track.Source == "UserRef"
    group track by new { TrackId = track.TrackId } into d

    select new FullReferrer
    {
        Customer = d.Key.TrackId,
        FullRefFalseCount = d.Where(x => x.FullRef == false).Count(),
        FullRefTrueCount = d.Where(x => x.FullRef == true).Count()
    };

Now it gives me the total count of TrackId, ignoring my .Where(x => x.FullRef == true/false) statement.

Anyone have any idea on how to fix it? I guess it is the "group by" that is the problem. Can I somehow avoid doing a group by? Do I maybe need a join?

Upvotes: 2

Views: 7158

Answers (5)

user2107630
user2107630

Reputation: 75

To anyone else having a similar problem I solved it by making it ".AsEnumerable()"

var query2 = from track in query.AsEnumerable() // <--- the solution
    where track.Source == "UserRef"
    group track by new { TrackId = track.TrackId } into d

    select new FullReferrer
    {
        Customer = d.Key.TrackId,
        FullRefFalseCount = d.Count(x => !x.FullRef),
        FullRefTrueCount = d.Count(x => !x.FullRef)
    };

Upvotes: 0

Ciniod
Ciniod

Reputation: 71

For NHibernate I don't know, but using Linq With Entity Framework this should get what you want:

var query2 = (
    from track in someDbSet
    select new FullReferrer
    {
          Customer = track.trackId
        , FullRefFalseCount = (from fullRefFalse in someDbSet.tracks
                               where fullRefFalse.IsSale == false 
                               && fullRefFalse.trackId == track.trackId
                               select fullRefFalse).Count()
        , FullRefTrueCount = (from fullRefTrue in someDbSet.tracks
                              where fullRefTrue.IsSale == true
                              && fullRefTrue.trackId == track.trackId
                              select fullRefTrue).Count()
    }
).Distinct();

Upvotes: 3

petchirajan
petchirajan

Reputation: 4352

try this. This takes the expected data from data table.

string source = "UserRef";
var result = from row in dt.AsEnumerable()
             where row["source"].Equals(source)
             group row by row["TrackId"]
             into g
             select new
                {
                   TrackId = g.Key,
                   FullRefTrueCount = ((from track in g where track["FullRef"].Equals("true") select track).Count()),
                   FullRefFalseCount = ((from track in g where track["FullRef"].Equals("false") select track).Count())
                };

Upvotes: 0

Taj
Taj

Reputation: 1728

FullRefFalseCount = d.Where(x => x.FullRef == false).ToList().Count
FullRefTrueCount = d.Where(x => x.FullRef == true).ToList().Count

try this

Upvotes: 1

Marguth
Marguth

Reputation: 191

Try out Count(condition).

    FullRefFalseCount = d.Count(x => x.FullRef == false),
    FullRefTrueCount = d.Count(x => x.FullRef == true)

Upvotes: 0

Related Questions