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?

Raw SQL or LINQ query, you need a class to represent the query result. — Ivan Stoev, Jan 05 '17 at 18:46
So I understand. Please. I need to build, possibly, many parts in addition to the SQL itself, correct? In other words, I need to examine how many queries I have, judge their complexity, etc., to see if I benefit from the ORM. — johnny, Jan 05 '17 at 18:53

bbsimonbb · Answer 1 · 2017-01-06T14:27:05.710

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.

edited Jan 06 '17 at 14:27

answered Jan 06 '17 at 08:40

bbsimonbb

27,056
15
80
110

I don't understand. I thought EF also generated classes. – johnny Jan 06 '17 at 14:13
EF will generate classes for your _schema objects_. If you write "custom sql", which they don't want you to do, you get to write and maintain the classes that will receive the result, and it's runtime errors if ever they don't line up. – bbsimonbb Jan 06 '17 at 14:23

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

1 Answers1