Linq with Left Join on SubQuery containing Count

Question

I'm having difficulty translating sql to linq syntax.

I have 2 tables (Category and CategoryListing) which reference each other with CategoryID. I need to get a list of all the CategoryID in Category Table and the Count of CategoryID for all corresponding matches in the CategoryListing table. If a CategoryID is not present in CategoryListing, then the CategoryID should still be returned - but with a frequency of 0.

The following sql query demonstrates expected results:

SELECT c.CategoryID, COALESCE(cl.frequency, 0) as frequency
FROM Category c
LEFT JOIN (
    SELECT cl.CategoryID, COUNT(cl.CategoryID) as frequency 
    FROM CategoryListing cl
    GROUP BY cl.CategoryID
) as cl
ON c.CategoryID = cl.CategoryID
WHERE c.GuideID = 1

Answer 1

Not tested, but this should do the trick:

var q = from c in ctx.Category
     join clg in 
      (
       from cl in ctx.CategoryListing
       group cl by cl.CategoryID into g
       select new { CategoryID = g.Key, Frequency = g.Count()}
      ) on c.CategoryID equals clg.CategoryID into cclg
     from v in cclg.DefaultIfEmpty()
     where c.GuideID==1
     select new { c.CategoryID, Frequency = v.Frequency ?? 0 };

Answer 2

do you know how to convert this to VB. I dont know how to convert Frequency = v.Frequency ?? 0 to VB. So everytime I got error can not convert Nullable to System.Int32.