LINQ-SQL – Incorrect results from Count() from Lambda expressions

Check out the following C# code:

 

using (MyDataContext db = new MyDataContext()) 
{
   int count1 = db.MyTable.Count(t=> t.IsActive == true);
   int count2 = db.MyTable.Select(t => t.IsActive == true).Count();
}

 

Judging by you what you see above, both count1 and count2 should have the same value, no?

No!

If there are 10 records in MyTable, count2 will have be 10.  If there are 5 records in the table that fit the IsActive == true lambda expression, than count2 will be 5. 

Can anyone explain this? 

At any rate, if you to use a Lamda expression to get a count, it’s a good idea to put the expression in the Count method itself, and not rely on a previous Select query to do it for you. 

Incidentally, check out this query:

 

using (MyDataContext db = new MyDataContext()) 
{
   var q = from t in db.MyTable
           where t.IsActive == true
           select t.ID;
   int count3 = q.Count();

}

Guess what?  The value for count3 is correct (as one would expect).

This is one of those cases where you just have to shake your head, do it the way that works, and move on.

More later – joel.

6 Comments

  • I don't like the syntax for count1 anyway, it's not clear. count2 is clear what you are trying to do.

    Perhaps that is why it works?

  • I think your count2 lamba expression is not being used in the proper way. I think the Select extension method is not meant to be used in this way...you seem to want to filter your result. Try replacing the Select with Where and you may get the result you are looking for. Where is normally used to filter results.

  • The problem is that Select isn't for performing filtering -- it's for projection. What you're doing is projecting each row into a single boolean representing whether IsActive is true. If you replace Select with Where I suspect you would get the result you want. Note that that's what the larger query you wrote is doing.

  • JD - you're absolutely correct, good eyes.

  • The answer is because the Select operator is used to create an output sequence of one kind of element to another type of element. In other words…you have a list of “stuff” and you want to extract another list using the first list as the base. The lamba expression used does not FILTER; rather, it MASSAGES the output data to look the way you want it.

    Just as illustration:

    You can do the EXACT same thing on the AdventureWorks database. The following uses a quick 5 minute mock up using AdventureWorks and the Entity Framework to access the data:


    //ObjectQuery employeeQuery
    AdventureWorksEntities entities = new AdventureWorksEntities();

    ObjectQuery employees = entities.Employee;

    int count1 = employees.Count(p => p.Gender == "M");
    int count2 = employees.Select(p => p.Gender == "M").Count();
    int count3 = employees.Count(p => p.Gender == "F");
    int count4 = employees.Where(p => p.Gender == "M").Count();

    System.Diagnostics.Debug.WriteLine(string.Format("Males (way 1)(Correct - uses Count): {0}", count1));
    System.Diagnostics.Debug.WriteLine(string.Format("Males (way 2)(Incorrect - Uses Select): {0}", count2));
    System.Diagnostics.Debug.WriteLine(string.Format("Females (way 1)(Correct - uses Count): {0}", count3));
    System.Diagnostics.Debug.WriteLine(string.Format("Males (way 3)(Correct - uses Where): {0}", count4));

    Your results will be:

    Males (way 1)(Correct - uses Count): 206
    Males (way 2)(Incorrect - Uses Select): 290
    Females (way 1)(Correct - uses Count): 84
    Males (way 3)(Correct - uses Where): 206

    The WHERE statement does the similar thing the count does, so you will get the same results.

    The proper use of Select would be to get a different view set of employee Data….kind of like the following using anonymous types:

    var employeeStuff = employees.Select(p => new
    {
    AmAWoman = p.Gender == "F",
    SSN = p.NationalIDNumber,
    UID = p.LoginID,
    Title = p.Title,
    DOB = p.BirthDate,
    Motto = p.Gender == "M" ? "I am a Man!!" : "A am Woman - watch me Roar!"
    });

    foreach (var item in employeeStuff)
    {
    System.Diagnostics.Debug.WriteLine(string.Format("{0}; {1}; {2}; {3}; {4}; {5}", item.SSN, item.UID, item.Title,
    item.DOB, item.Motto, item.AmAWoman));
    }


    (results – only the first few)
    14417807; adventure-works\guy1; Production Technician - WC60; 5/15/1972 12:00:00 AM; I am a Man!!; False
    253022876; adventure-works\kevin0; Marketing Assistant; 6/3/1977 12:00:00 AM; I am a Man!!; False
    509647174; adventure-works\roberto0; Engineering Manager; 12/13/1964 12:00:00 AM; I am a Man!!; False
    112457891; adventure-works\rob0; Senior Tool Designer; 1/23/1965 12:00:00 AM; I am a Man!!; False
    480168528; adventure-works\thierry0; Tool Designer; 8/29/1949 12:00:00 AM; I am a Man!!; False
    24756624; adventure-works\david0; Marketing Manager; 4/19/1965 12:00:00 AM; I am a Man!!; False
    309738752; adventure-works\jolynn0; Production Supervisor - WC60; 2/16/1946 12:00:00 AM; A am Woman - watch me Roar!; True
    690627818; adventure-works\ruth0; Production Technician - WC10; 7/6/1946 12:00:00 AM; A am Woman - watch me Roar!; True
    695256908; adventure-works\gail0; Design Engineer; 10/29/1942 12:00:00 AM; A am Woman - watch me Roar!; True
    912265825; adventure-works\barry0; Production Technician - WC10; 4/27/1946 12:00:00 AM; I am a Man!!; False
    998320692; adventure-works\jossef0; Design Engineer; 4/11/1949 12:00:00 AM; I am a Man!!; False
    245797967; adventure-works\terri0; Vice President of Engineering; 9/1/1961 12:00:00 AM; A am Woman - watch me Roar!; True
    844973625; adventure-works\sidney0; Production Technician - WC10; 10/1/1946 12:00:00 AM; I am a Man!!; False
    233069302; adventure-works\taylor0; Production Supervisor - WC50; 5/3/1946 12:00:00 AM; I am a Man!!; False

  • int count2 = db.MyTable.Select(t => t.IsActive == true).Count();

    should be

    int count2 = db.MyTable.Where(t => t.IsActive == true).Count();

Comments have been disabled for this content.