CODEWITHSUNDEEP
c#linq
Ramanan
Ramanan

Reputation: 13

Get Count from multiple table in single linq

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

Table 1: Orders

OID VendorID VendorName Date Status
1 34 Ras 2022-09-01 Active
2 33 Tek 2022-09-02 Processed
3 34 Ras 2022-09-01 Active
4 45 Yel 2022-09-05 Active
5 57 Kaw 2022-09-07 Processed

Table 2: Subscribe

SID VendorID SubsribePack Date Status
1 34 Goo 2022-09-01 Active
2 34 Yoo 2022-09-01 Processed
3 35 Too 2022-09-04 Active

Table 3: Notes

NID VendorID NoteDesc Date Status
1 34 IIT 2022-09-01 Active
2 34 NIT 2022-09-01 Active
3 34 KIT 2022-09-01 Active
4 26 LIT 2022-09-03 Active
5 74 PIT 2022-09-04 Processed

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

Upvotes: 1

Views: 325

Answers (3)

likquietly
likquietly

Reputation: 228

In case of using same table column types:

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

With different types:

int result = Orders.Select(x => new { x.VendorID, x.Date, x.Status }).Concat(
          Subscribe.Select(x => new { x.VendorID, x.Date, x.Status })).Concat(
              Notes.Select(x => new { x.VendorID, x.Date, x.Status }))
         .Where(x => x.VendorID == 34 && x.Date == "2022-09-01" && x.Status == "Active").Count();

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460108

Maybe you can select from the different tables the common fields and then apply the same filter:

int totalCount = Orders
    .Select(x => (x.VendorID, x.Date, x.Status))
    .Concat(Subscribe.Select(x => (x.VendorID, x.Date, x.Status)))
    .Concat(Notes.Select(x => (x.VendorID, x.Date, x.Status)))
    .Count(x => x.VendorID == 34 && x.Date == "2022-09-01" && x.Status == "Active");

If that count with condition is not supported by your version of EF, use Where(condition).Count().

Upvotes: 0

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

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

Upvotes: 2

Related Questions