Is a custom class required for raw SQL queries in the Entity Framework?

Question

I am confused on something. I understand what an ORM is doing, and I understand what EF is. What I'm hazy on is the part where I do not want to use the specialized constructs to get the data. I want to use SQL. If I create a complex query in SQL, lots of subqueries and so on, am I correct that if I want to be able to handle the data like a property, I have to create a special custom class, just for that query.

I'm thinking of a scenario like this. Someone needs information. It requires a complex query that returns 20 columns from 5 different tables. I need to create a class that has those columns and then write the query. Is this correct?

I'm looking at this tutorial,

https://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/creating-an-entity-framework-data-model-for-an-asp-net-mvc-application

using System;
using System.Collections.Generic;

namespace ContosoUniversity.Models
{
    public class Student
    {
        public int ID { get; set; }
        public string LastName { get; set; }
        public string FirstMidName { get; set; }
        public DateTime EnrollmentDate { get; set; }

        public virtual ICollection<Enrollment> Enrollments { get; set; }
    }
}

What if this was something like Moodle or other LMS or a Student Information System? They get complex, and they need lots of joins and data from more than a few tables.

Would this be duplicated, a lot:

public virtual ICollection<Enrollment> Enrollments { get; set; }
public virtual ICollection<Schedule> Schedules{ get; set; }
public virtual ICollection<SubjectContext> SubjectContexts{ get; set; }
public virtual ICollection<Grade> Grades{ get; set; }
public virtual ICollection<Instructor> Instructors{ get; set; }

For example, consider this query:

SELECT user.firstname, user.lastname, user.email, user.country, cc.name AS 'course category name', c.fullname, q.name, question.content AS 'Question', choice.content AS 'Question option', choicerank.rank AS 'Choice value'
FROM mdl_course_categories AS cc
INNER JOIN mdl_course AS c ON c.category = cc.id
INNER JOIN mdl_questionnaire AS q ON q.course = c.id
INNER JOIN mdl_questionnaire_question AS question ON question.survey_id = q.id
INNER JOIN mdl_questionnaire_quest_choice AS choice ON choice.question_id = question.id
INNER JOIN mdl_questionnaire_response_rank AS choicerank ON choicerank.choice_id = choice.id
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS user ON user.id = ra.userid
ORDER BY user.firstname

Does this need a custom class?

If I do something that has .toList(String), is that defeating the purpose of using the framework to begin with?

Answer 1

What benefit will the ORM deliver in this circumstance? Genuine question. I can't think of one.

Use QueryFirst. You edit your SQL in optimum conditions (the sql window), and you have nothing else to build.

disclaimer: I wrote QueryFirst.