Convert this SQL to lambda for Code-First EF

Question

I have this SQL, which produces the desired results:

select * from Categories as c
inner join Questions as q on c.Id = q.CategoryId
inner join Surveys as s on s.Id = q.SurveyId
where s.RoleId = 2

I would like to convert it to a lambda expression.

How it works:

• A single survey exists for each user role
• A survey contains questions
• Sets of questions belong to a single category

I am trying to pull the entire survey, walking through the results with Category.Questions, etc (loop already constructed).

Any help on this would be appreciated, as I'm trying to get back into the .NET scene after more than 5 years of avoidance... Thank you in advance.

Answer 1

Something like this if you meant LINQ:

var query = (from c in dataContext.Categories
                  join q in dataContext.Questions on q.CategoryId = c.Id
                  join s in dataContext.Surveys on q.SurveyId = s.Id
             where c.RoleId = 5
             select new
             {
                [Your Fields]
             });

Answer 2

Solved this by using:

List<Question> questions = db.questions.Where(q => q.Survey.RoleId == iroleId).ToList();

And filtered the result set within my view, with:

foreach (Tracker.Models.Category category in Model.Select(x => x.Category).Distinct().ToList())
{
  ...
  foreach (Tracker.Models.Question question in Model.Where(x => x.Survey.RoleId == ViewBag.UserRoleId && x.CategoryId == catLoop.Id).ToList())

Seems a little messier than I expected, but the solution works. There's got to be a better way than this, though...

Answer 3

for this specific query I would persaonally prefer writing as as a LINQ without lambada expression

var query=(from c in db.Categories  from q in Questions from n in Surveys where c.Id ==
q.CategoryId && q.SurveyId==n.Id && n..RoleId == 2).ToList();