CODEWITHSUNDEEP
c#entity-frameworklinqlinq-to-sqllinq-to-entities
MHOOS
MHOOS

Reputation: 5306

return 0 for items count in linq statement

Lets say I have the following EF6 Linq statement which counts number of items for 2 tables (Table1 has 10 items and Table2 has no items) :

var q = db.Table1.GroupBy(g => "Table1").Select(g => new { Name = g.Key, EntryCount = g.Count() })
          .Union(db.Table2.GroupBy(g => "Table2").Select(g => new { Name = g.Key, EntryCount = g.Count() }));

var r = q.ToList();

The expected result should be something like 

Name    | EntryCount
---------------------
Table1  |   10
Table2  |    0

However because Table2 doesn't have any items it doesn't appear in the final result and I get the following:

Name    | EntryCount
---------------------
Table1  |   10

How can I make sure Table 2 always appear in the final list even if its empty and doesn't have any records?

To give you a background on why I am doing this Linq statement , I am converting the following TSQL statement into a linq query:

CREATE FUNCTION [dbo].[ufnGetLookups] ()
RETURNS
@lookupsWithItemCounts TABLE 
(
    [Name] VARCHAR(100),
    [EntryCount] INT
)

AS 
BEGIN
    INSERT INTO @lookupsWithItemCounts([Name],[EntryCount])
     VALUES
               ('Table1', (SELECT COUNT(*) FROM Table1)),
               ('Table2', (SELECT COUNT(*) FROM Table2)),
               ('Table3', (SELECT COUNT(*) FROM Table3))

    RETURN;
END

Also its very important to for this linq statement to run in one database trip, not multiple.

Upvotes: 1

Views: 422

Answers (2)

NetMage
NetMage

Reputation: 26926

You can do this by creating a default table with a single row in it.

var ans = Table1.GroupBy(u => "Table1")
                .Select(ug => new { Name = ug.Key, EntryCount = ug.Count() })
                .Union(Table2.GroupBy(l => "Table2")
                              .Select(lg => new { Name = lg.Key, EntryCount = lg.Count() })
                              .Union(OneRowTable.GroupBy(u2 => 1)
                                                .Select(u2g => new { Name = "Table2", EntryCount = u2g.Count()-1 }) )
                              .OrderByDescending(cg => cg.EntryCount)
                              .Take(1)
               );

This is evaluated in a single round trip to the database by LINQ to SQL. I can't easily test with LINQ to EF.

Note that in EF Core 3.0, the original query is translated in such a way as to return a 0 row for any empty tables.

Upvotes: 2

MKR
MKR

Reputation: 20095

If you have a list of tables then table list can be used to union a row with EntryCount = 0 and then on final result set a GroupBy on Name and Sum of EntryCount will provide desired result. 

//List of tables
var tableList = new string[] { "Table1", "Table2" }; 

var res = db.Table1
    .GroupBy(t1 => "Table1")
    .Select(gt1 => new { Name = gt1.Key, EntryCount = gt1.Count()})
    .Union(db.Table2
           .GroupBy(t2 => "Table2")
           .Select(gt2 => new { Name = gt2.Key, EntryCount = gt2.Count()})
          )
    .Union(tableList
           .GroupBy(s => s)
           .Select(gs => new { Name = gs.Key, EntryCount = 0 })
          )
    .GroupBy(gg => gg.Name)
    .Select(fg => new {Name = fg.Key, EntryCount=fg.Select(ee => ee.EntryCount).Sum()})
       .ToList();

The result will have EntryCount for all tables in list.

Upvotes: 2

Related Questions