I am looking for linq query to count the records from multiple table with same id in a single query.

Table 1: Orders

Table 2: Subscribe

Table 3: Notes

I would like to count the above table records which has vendorID = 34 and Date = '2022-09-01' and Status = 'Active'.

As of now, I getting the count in each separate query

var OCount = Orders.Where(a=> a.VendorID = 34 && a.Date = '2022-09-01' && a.Status = 'Active').ToCount();

var SCount = Subscribe.Where(a=> a.VendorID = 34 && a.Date = '2022-09-01' && a.Status = 'Active').ToCount();

var NCount = Notes.Where(a=> a.VendorID = 34 && a.Date = '2022-09-01' && a.Status = 'Active').ToCount();

So that I am getting the output as,

OCount: 2 SCount: 1 NCount: 3

I am looking for a one linq query to count all the records.

Thanks in advance

CodePudding user response：

int result = Orders.Union(Subscribe).Union(Notes).Count(a => a.VendorID == 34 && a.Date == "2022-09-01" && a.Status == "Active");

CodePudding user response：

Use known technique GroupBy by constant and then Concat result sets:

var OCount = Orders.Where(a => a.VendorID == 34 && a.Date == "2022-09-01" && a.Status == "Active")
    .GroupBy(x => 1).Select(g => g.Count());

var SCount = Subscribe.Where(a => a.VendorID == 34 && a.Date == "2022-09-01" && a.Status == "Active")
    .GroupBy(x => 1).Select(g => g.Count());

var NCount = Notes.Where(a => a.VendorID == 34 && a.Date == "2022-09-01" && a.Status == "Active")
    .GroupBy(x => 1).Select(g => g.Count());

var result = OCount.Concat(SCount).Concat(NCount).Sum();

This query will generate approximately the following SQL (if you are using EF):

SELECT SUM(s.count)
FROM (
    SELECT COUNT(*) as count
    FROM Orders o
    WHERE o.VendorID = 34 AND o.Date = '2022-09-01' AND o.Status = 'Active'
    UNION ALL
    SELECT COUNT(*) as count 
    FROM Subscribe s
    WHERE s.VendorID = 34 AND s.Date = '2022-09-01' AND s.Status = 'Active'
    UNION ALL
    SELECT COUNT(*) as count
    FROM Notes n
    WHERE n.VendorID = 34 AND n.Date = '2022-09-01' AND n.Status = 'Active'
) s