title: return 0 for items count in linq statement
body: <p>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) :</p>
  
  <pre><code>var q = db.Table1.GroupBy(g =&gt; "Table1").Select(g =&gt; new { Name = g.Key, EntryCount = g.Count() })
            .Union(db.Table2.GroupBy(g =&gt; "Table2").Select(g =&gt; new { Name = g.Key, EntryCount = g.Count() }));
  
  var r = q.ToList();
  </code></pre>
  
  <p>The expected result should be something like </p>
  
  <pre><code>Name    | EntryCount
  ---------------------
  Table1  |   10
  Table2  |    0
  </code></pre>
  
  <p>However because Table2 doesn't have any items it doesn't appear in the final result and I get the following:</p>
  
  <pre><code>Name    | EntryCount
  ---------------------
  Table1  |   10
  </code></pre>
  
  <p>How can I make sure Table 2 always appear in the final list even if its empty and doesn't have any records?</p>
  
  <p>To give you a background on why I am doing this Linq statement , I am converting the following TSQL statement into a linq query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Also its very important to for this linq statement to run in one database trip, not multiple.</p>