Aggregation count in linq query returns null

Question

User-997500692 posted
I'm implementing asp.net core 3.1 project. In my controller I have a linq query like the following and without defining inprocessapicount and pendingcount which claculates counts of related amounts, it works fine but after adding them to applicants query in select part, applicants returns null and the error is: .Count(a => a.requestStatus == "bb") could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync() . I appreciate if anyone suggests me a solution.


var applicants = (from t1 in _context.VwDesk

let tg = new {

itemID = t1.ItemId,
applicantID = t1.ApplicantId,
applicantName = t1.ApplicantName,

gateName =t1.GateName,

requestStatus = t1.LastReqStatus
}
group tg by new { tg.requestStatus,tg.itemID ,tg.applicantID, tg.applicantName} into ApiAppGp
select new
{
applicantName = ApiAppGp.Key.applicantName,

itemname = ApiAppGp.Key.itemID,

itemcount =ApiAppGp.Count(),

inprocessapicount = ApiAppGp.Where(a => a.requestStatus == "bb").Count(),
pendingapicount = ApiAppGp.Where(a => a.requestStatus == "aa").Count()


}).ToList();


What I want is: There are some applicants in Database and each applicant may have multiple or zero requests. (Each applicant orders multiple or zero itemID) each one of those applicants needs to get some item (itemID) and each of those item has requeststatus =="aa" or requeststatus =="bb" or none of them which the status in that case is "general". Now I want to understand, how many each applicant's itemID has requeststatus=="aa" and how many each applicant's itemID has requeststatus=="bb" according to all ItemId they have ordered.
Also sometimes there is no a => a.requestStatus == "bb" or a => a.requestStatus == "aa" matched condition thus they may return null

Answer 1

User-2121988648 posted

For null as result you can use ?? 0 to set null as 0

If incoming value can be null then 

var result = null ?? 0;

Also, look for null conditionals using ?.Count()

Reference:

https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/operators/null-coalescing-operator

https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/operators/member-access-operators#null-conditional-operators--and-

You can try:

inprocessapicount = ApiAppGp.Where(a => a.requestStatus == "bb")?.Count()??0,
pendingapicount = ApiAppGp.Where(a => a.requestStatus == "aa")?.Count()??0

Please let me know if this doesn't help :)

Answer 2

User-997500692 posted
Thanks for your reply. When I add ? And ?? As you suggested to me, it shows me an error that an expression tree lambda may not contain a null propagating operator

Answer 3

User-2121988648 posted

Can you share the versions of .NET and C# that you are using ?

Answer 4

User-997500692 posted
I am using .net core 3.1

Answer 5

User-2121988648 posted

Please try this:

inprocessapicount = ApiAppGp.ToList().Where(a => a.requestStatus == "bb")?.Count()??0,
pendingapicount = ApiAppGp.ToList().Where(a => a.requestStatus == "aa")?.Count()??0

Answer 6

User-997500692 posted

Thank you for your reply.It still shows me the same error,when i change my code like your suggestion

Answer 7

User-2121988648 posted

We can also try ToList() before select projection!!

var applicants = (from t1 in _context.VwDesk.ToList()

let tg = new {

itemID = t1.ItemId,
applicantID = t1.ApplicantId,
applicantName = t1.ApplicantName,

gateName =t1.GateName,

requestStatus = t1.LastReqStatus
}
group tg by new { tg.requestStatus,tg.itemID ,tg.applicantID, tg.applicantName} into ApiAppGp
select new
{
applicantName = ApiAppGp.Key.applicantName,

itemname = ApiAppGp.Key.itemID,

itemcount =ApiAppGp?.Count()??0,

inprocessapicount = ApiAppGp.Where(a => a.requestStatus == "bb")?.Count()??0,
pendingapicount = ApiAppGp.Where(a => a.requestStatus == "aa")?.Count()??0


}).ToList();

Answer 8

User-997500692 posted
Many thanks for your help, now those count are filled with some numbers and the error disappeared but The numbers are incorrect. I think my query regarding what I need to calculate has got problem but do you have any suggestion about applicant query?

Answer 9

User-2121988648 posted

That's Great :)

Regarding Query I believe you can define the type of final generated result so it is not anonymous. Please create a new class for that. Your result will be strongly typed. 

Rest, Your query is well defined and easy to interpret. :)

var applicants = (from t1 in _context.VwDesk.ToList()

let tg = new {

itemID = t1.ItemId,
applicantID = t1.ApplicantId,
applicantName = t1.ApplicantName,

gateName =t1.GateName,

requestStatus = t1.LastReqStatus
}
group tg by new { tg.requestStatus,tg.itemID ,tg.applicantID, tg.applicantName} into ApiAppGp
select new ApplicantsStatistic
{
applicantName = ApiAppGp.Key.applicantName,

itemname = ApiAppGp.Key.itemID,

itemcount =ApiAppGp?.Count()??0,

inprocessapicount = ApiAppGp.Where(a => a.requestStatus == "bb")?.Count()??0,
pendingapicount = ApiAppGp.Where(a => a.requestStatus == "aa")?.Count()??0


}).ToList();

I hope your issue is resolved :)

Answer 10

User-997500692 posted

<br>
&lt;br&gt;<br>
&amp;lt;br&amp;gt;&lt;br&gt;<br>
Now there is a problem I want to calculate Sum() for ApiAppGp.Where(a => a.requestStatus==“aa”).Sum() ,I mean I wan to know for each applicantId, sum of his requestStatus that is equals to “aa”, but it shows me the error:IEnumerable anonymous type: int itemID,int?
applicantID,... does not contain a definition for ‘Sum’ and the best extension method overload<br>
‘ParallelEnumerable.Sum(ParallelQuery (decimal)’ requires a reciever of type ‘ParallelQuery (decimal) ‘<br>
Could you please help me regarding this issue?

Answer 11

User-2121988648 posted

Issue might be that it is not strongly typed. You might want to try that here as well.

var applicants = (from t1 in _context.VwDesk.ToList()

let tg = new ApplicantGroup{

itemID = t1.ItemId,
applicantID = t1.ApplicantId,
applicantName = t1.ApplicantName,

gateName =t1.GateName,

requestStatus = t1.LastReqStatus
}
group tg by new { tg.requestStatus,tg.itemID ,tg.applicantID, tg.applicantName} into ApiAppGp
select new ApplicantsStatistic
{
applicantName = ApiAppGp.Key.applicantName,

itemname = ApiAppGp.Key.itemID,

itemcount =ApiAppGp?.Count()??0,

inprocessapicount = ApiAppGp.Where(a => a.requestStatus == "bb")?.Count()??0,
pendingapicount = ApiAppGp.Where(a => a.requestStatus == "aa")?.Count()??0


}).ToList();