CODEWITHSUNDEEP
c#linq-to-sql
Mo
Mo

Reputation:

Equivalent LINQ to SQL code

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

Upvotes: 2

Views: 235

Answers (2)

John Gietzen
John Gietzen

Reputation: 49564

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

Upvotes: 1

Mehrdad Afshari
Mehrdad Afshari

Reputation: 422242

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

Upvotes: 5

Related Questions