How Can Solved Enumeration yielded no results In LINQ

Question

I want to union the group by sum value of my two tables. But when no data is found between the time parameter values ​​it shows Enumeration yielded no results. Is it possible that if there is no data it will show me company id and other values ​​will show Zero.

    public JsonResult FindResult(DateTime _fitsdate, DateTime _lastdate, int companyid)
    {  var Result1 = (from p in _context.tbl_Companypayments
                         where p.Company_ID == companyid 
                         where p.paymentDate <= _fitsdate
      group p by p.Company_ID into g
                         select new JoinClass
                         {
                             CompanyId = g.Key,
                             PaymentAmount = g.Sum(x=>x.PaymentAmount) ,
                             PurchaseAmount=0
                         });
  var Result2 = (from p in _context.tbl_Purchases
                 where p.companyId == companyid
                 where p.PurchaseDate <= _fitsdate
                 group p by p.companyId into g
                 select new JoinClass
                 {
                     CompanyId = g.Key,
                     PaymentAmount=0,
                     PurchaseAmount = g.Sum(x => x.PurchaseValue)
                 });
  var final = Result1.Union(Result2);
    return Json(final );
}

    public class JoinClass

    {

    

        public int? CompanyId { get; set; }

        public decimal? PaymentAmount { get; set; }

        public decimal? PurchaseAmount { get; set; }

    }

//I Wan Result Like This

CompanyId =1

PaymentAmount =0

PurchaseAmount =0

Answer 1

Hi @jewel,

To achieve your requirement you could use the DefaultIfEmpty() method.

https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.defaultifempty?view=net-9.0

https://learn.microsoft.com/en-us/dotnet/api/system.linq.queryable.defaultifempty?view=net-9.0

It will help you return the default value if it is empty.

Controller:

using CompanyPaymentsApp.Data;
using CompanyPaymentsApp.Models;
using Microsoft.AspNetCore.Mvc;
namespace CompanyPaymentsApp.Controllers
{
    public class CompanyController : Controller
    {
        private readonly ApplicationDbContext _context;
        public CompanyController(ApplicationDbContext context)
        {
            _context = context;
        }
        public JsonResult FindResult(DateTime _fitsdate, DateTime _lastdate, int companyid)
        {
            var Result1 = (from p in _context.tbl_Companypayments
                           where p.Company_ID == companyid
                           where p.PaymentDate <= _fitsdate
                           group p by p.Company_ID into g
                           select new JoinClass
                           {
                               CompanyId = g.Key,
                               PaymentAmount = g.Sum(x => x.PaymentAmount),
                               PurchaseAmount = 0
                           }).ToList(); // Convert to List to materialize the query
            var Result2 = (from p in _context.tbl_Purchases
                           where p.CompanyId == companyid
                           where p.PurchaseDate <= _fitsdate
                           group p by p.CompanyId into g
                           select new JoinClass
                           {
                               CompanyId = g.Key,
                               PaymentAmount = 0,
                               PurchaseAmount = g.Sum(x => x.PurchaseValue)
                           }).ToList(); // Convert to List to materialize the query
            var final = Result1.Union(Result2).ToList(); // Convert final result to a List
            // Ensure at least one record exists even if both Result1 and Result2 return empty
            if (!final.Any())
            {
                final.Add(new JoinClass { CompanyId = companyid, PaymentAmount = 0, PurchaseAmount = 0 });
            }
            return Json(final);
        }
    }
}

Result:

Best Regards,

Jalpa Panchal

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.