Read Records then Count

[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.