Read Records then Count

[OriginalGriff]

As Richard says, this is easiest in SQL:

SELECT SUM(CASE WHEN fieldA IS NOT NULL THEN 1 ELSE 0 END) FROM MyTable

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

[Richard Deeming]

Pete O'Hanlon wrote:

int.TryParse(output, ref retValue);

That's not going to compile - TryParse expects the first parameter to be a string, not an object. It should be safe to unbox the output variable directly to an int. Otherwise, Convert.ToInt32 should work. Also, it would probably be a good idea to wrap the IDbCommand in a using block, and test whether or not you need to open the connection, since it might already be open.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Lost User]

Also, the CommandType "text" does not need to be set, it is (very conveniently) the default; since it is not a nullable enum it will always have a value, meaning a default must exist. ..but still bonuspoints, as the code is not tied to a specific IDbCommand implementation, and he's using the factory-method to create a command of the correct type :)

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)

[Pete OHanlon]

To be fair, I just coded this in the textblock as a minimal version. It's been a long time since I went away from the Db libraries I've built up that hide all this implementation.

[Matt T Heffron]

Wouldn't just the COUNT function do this?

SELECT COUNT(fieldA) FROM MyTable

In this form it counts the NON NULL values. See: SQL COUNT() Function[^] and COUNT (Transact-SQL)[^]

"Fairy tales do not tell children the dragons exist. Children already know that dragons exist. Fairy tales tell children the dragons can be killed." - G.K. Chesterton

[OriginalGriff]

Yes...it would... :-O Sorry about that, it's been one of those weeks...

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

[macca24]

I should have said I am trying to do this using LINQ: I have a 20 records in a database table. Each record has and ID. I want to read a field in each record fieldA. I only want to read the records that have an ID '1'. For each record that has an ID '1' and fieldA Is Not Null I want to return a count of those records. So if 10 records have data in fieldA and have ID '1' then my count for fieldA will be 10. Anyone know how to do this in Linq?

[Agent__007]

How about something like:

int count = yourDbContext.yourEntityCollection.Where(entity => entity.ID == 1 && entity.FieldA != null).Count();

You have just been Sharapova'd.

[Richard Deeming]

Or, more simply:

int count = yourDbContext.yourEntityCollection.Count(entity => entity.ID == 1 && entity.FieldA != null);

:)

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Agent__007]

Not sure about Linq to SQL, but (for Linq to objects and XML) I have always found Where(predicate).Count() to be performing better than .Count(predicate). --EDIT Here's a StackOverflow question[^] that might interest you. :)

You have just been Sharapova'd.

[Richard Deeming]

The optimizations only apply to in-memory LINQ queries (LINQ to Objects and LINQ to XML). With LINQ to SQL and Entity Framework, both versions should produce an identical SQL query.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[macca24]

This is the code that I am looking to get a Count of complexQuery.

string refName = entity.GetAttributeValue<string>("contractorname");

var complexQuery = (from c in orgContext.CreateQuery<contractorframe>()
join e in orgContext.CreateQuery<dcc_eval>()
on c.ContractorName equals e.RegardingObjectId.ToString()
where e.RegardingObjectId.ToString() == refName
and e.QW Is Not Null)

[Agent__007]

:thumbsup: Indeed, they should. But I have always used the Where(predicate).Count() version, so that came implicitly when I posted the answer. :)

You have just been Sharapova'd.

[Agent__007]

macca24 wrote:

I am looking to get a Count of complexQuery

Not sure what you mean by that, but if your complexQuery is already working, then you can simply use the Count() method:

int complexQueryCount = (from c in orgContext.CreateQuery()
join e in orgContext.CreateQuery()
on c.ContractorName equals e.RegardingObjectId.ToString()
where e.RegardingObjectId.ToString() == refName
and e.QW Is Not Null).Count(); // <-- here

You have just been Sharapova'd.

[macca24]

int complexQuery = (from e in mycontext.CreateQuery()

                                        where e.new\_QW1 != null
                                        select e.new\_QW1).Count();

                    

                    
                   if (complexQuery < 1)
                    {
                        var newQuery = "HasData";
                        entity.Attributes.Add("new\_testcomplexquery", newQuery);
                    }
                    else
                    {
                        var newQueryToo = "Blank";
                        entity.Attributes.Add("new\_testcomplexquery", newQueryToo);
                        

                    }

I want to use the results of complexQuery in an If statement but the value of complexQuery doesnt appear to be getting picked up. Anyone know why?

[Agent__007]

I am not sure what you are trying to do, but shouldn't your if condition be reversed? i.e. it "HasData" if complexQuery > 0 and it's "Blank" if complextQuery == 0. Try this:

if (complexQuery != 0) // <-- reverse the condition here
{
var newQuery = "HasData";
entity.Attributes.Add("new_testcomplexquery", newQuery);
}
else
{
var newQueryToo = "Blank";
entity.Attributes.Add("new_testcomplexquery", newQueryToo);
}

You have just been Sharapova'd.