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

Joel Varty recently had a question on his blog:

http://weblogs.asp.net/joelvarty/archive/2009/03/06/linq-sql-incorrect-results-from-count-from-lambda-expressions.aspx

My answer to this issue is below...

I’m surprised Joel didn’t answer his own question of “Can anyone explain this?”…HE is the expert…anyway…I’ll venture my own 2 cents (may be all it’s worth):

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 lambda 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