locked
Aggregation count in linq query returns null RRS feed

  • 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



    Tuesday, July 28, 2020 10:56 AM

Answers

  • 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 :)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 28, 2020 5:03 PM

All replies

  • 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 :)

    Tuesday, July 28, 2020 1:08 PM
  • 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
    Tuesday, July 28, 2020 3:19 PM
  • User-2121988648 posted

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

    Tuesday, July 28, 2020 3:23 PM
  • User-997500692 posted
    I am using .net core 3.1
    Tuesday, July 28, 2020 3:49 PM
  • 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

    Tuesday, July 28, 2020 4:03 PM
  • User-997500692 posted

    Thank you for your reply.It still shows me the same error,when i change my code like your suggestion
    Tuesday, July 28, 2020 4:17 PM
  • 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();

    Tuesday, July 28, 2020 4:28 PM
  • 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?
    Tuesday, July 28, 2020 4:51 PM
  • 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 :)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 28, 2020 5:03 PM
  • 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?
    Tuesday, July 28, 2020 6:08 PM
  • 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();

    Wednesday, July 29, 2020 6:05 AM