c# - 等效的 LINQ to SQL 代码

Question

这是我的 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

等效的 LINQ to SQL 代码是什么？任何帮助将不胜感激

抱歉，我忘了提到没有关系数据库端，表在数据库中根本没有定义关联，这就是主要问题，这实际上只是示例 sql，看看如何为 T-SQL 编写链接到 SQL需要:左外连接、外连接表记录计数及排序

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) };

或者:

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) };