Equivalent LINQ to SQL code

Question

Hi there,

Am new to this here is my T-SQL

SELECT category.id, category.name,COUNT(job.id) AS countofjobs 
FROM category 
LEFT OUTER JOIN job ON category.id = job.categoryid AND job.active=1 
WHERE category.featured=1 
GROUP BY category.id, category.name
ORDER BY category.name

what will be the equivalent LINQ to SQL code? any help will be appreciated

Sorry I forgot to mention that there is no relationship database side, tables have no association at all defined in db, thats the main issue, this is really just sample sql to see how I can write Link to SQL for T-SQL that requires: Left outer join, Count of outer join table records and sorting

Answer 1

var result = dataContext.Categories
                  .Where(c => c.Featured)
                  .OrderBy(c => c.Name)
                  .Select(c => new { c.Id, 
                                     c.Name, 
                                     CountOfJobs = c.Jobs.Count(j => j.Active) };

Alternatively:

var result = from c in dataContext.Categories
             where c.Featured
             orderby c.Name
             select new { c.Id, c.Name, CountOfJobs = c.Jobs.Count(j => j.Active) };

Answer 2

Since you don't have relationships:

var result = from c in dataContext.Categories
             where c.Featured
             orderby c.Name
             select new {
                 c.Id,
                 c.Name,
                 CountOfJobs = dataContext.Jobs.Count(j => j.categoryId == c.Id && j.Active)
             };