CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
Erez Konforti
Erez Konforti

Reputation: 253

C# Entity Framework SQL Query

I have the following models:

class Student
{
        /// <summary>
        /// Student's name.
        /// </summary>
        [Required]
        public string StudentName { get; set; }

        /// <summary>
        /// Student's ID
        /// </summary>
        [Key]
        [Required]
        public string StudentID{get; set; }
}

class Course
{
    [Key]
    [Required]
    public string nm { get; set; }
    [Required]
    public string CourseName { get; set; }
    [Required]
    public string Lecturer { get; set; }
    [Required]
    public string Campus { get; set; }
}

class StudentsCourses
{
    [Key]
    [Required]
    public string nm { get; set; }
    [Required]
    public string CourseName { get; set; }
    [Required]
    public string StudentID { get; set; }
}

and corresponding tables in SQL Server. Entity Framework and the models are working perfectly, but now I want to cross check data from two tables, students and courses.

I wrote:

StringBuilder st = new StringBuilder("SELECT Students.StudentID,Students.StudentName").Append(" FROM Students, StudentsCourses");
st.Append(" WHERE Students.StudentID=StudentsCourses.StudentID AND StudentsCourses.CourseName='").Append(course).Append("'");

List<Student> Try = DataLayer.Students.SqlQuery(st.ToString()).ToList<Student>();

but it throws an exception saying

Incorrect syntax near '.'

How can I run this query in SQL or in LinQ? My final goal is to get a list of students that study a given course name.

Upvotes: 1

Views: 459

Answers (1)

D Stanley
D Stanley

Reputation: 152491

Don't mess with appending strings. Just use one long string (a string in C# can have line breaks) and use parameters (and a JOIN):

string sql = @"SELECT Students.StudentID,Students.StudentName
               FROM Students  
               INNER JOIN StudentsCourses
                  ON Students.StudentID=StudentsCourses.StudentID 
               WHERE StudentsCourses.CourseName=@courseName";

List<Student> Try = DataLayer.Students
                             .SqlQuery(sql, new SqlParameter("@courseName", course))
                             .ToList();

The equivalent Linq query (if you don't have navigation properties) would be something like

List<Student> Try = (  
    from s in DataLayer.Students
    join c in DataLayer.StudentsCourses
      on s.StudentID equals c.StudentID
    where c.CourseName = course
    select s).ToList();

Upvotes: 1

Related Questions