C# Entity Framework SQL Query

Question

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.

You need spaces. For example, between "StudentName" and "FROM". — Yacoub Massad
– Yacoub Massad, Commented May 24, 2016 at 12:47
.StudentName").Append("FROM will result in StudentNameFROM so make spaces — fubo
– fubo, Commented May 24, 2016 at 12:47
By the way, why are you writing your own SQL queries? That goes against the whole idea of using entity framework in most cases. — Yacoub Massad
– Yacoub Massad, Commented May 24, 2016 at 12:48
@Erez, I suggest that you go though some tutorial before you use entity framework. In most projects, the time you will save by using the entity framework features is much more than the time you need to learn them. — Yacoub Massad
– Yacoub Massad, Commented May 24, 2016 at 13:14

D Stanley · Accepted Answer · 2016-05-24 13:05:31Z

1

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

edited May 24, 2016 at 13:05

answered May 24, 2016 at 12:50

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Erez Konforti Over a year ago

OK, Now new Exception: The value of a property that is part of an object's key does not match the corresponding property value stored in the ObjectContext. This can occur if properties that are part of the key return inconsistent or incorrect values or if DetectChanges is not called after changes are made to a property that is part of the key. Even though StudentID is defined as key both in the table and in the model. In the model its string and in the table its nvarchar(9)..

D Stanley Over a year ago

Are there more columns in Student that just ID and Name? I believe you have to output all columns if you are projecting to an entity type. Is there a reason you're writing SQL directly instead of using Linq and Navigation properties? That may solve both problems.

Erez Konforti Over a year ago

@D Stanley, mainly because I don't really know LinQ, searched google a bit and didn't found..

Erez Konforti Over a year ago

And thats all the columns, ID and Name, both in model and table

DevilSuichiro Over a year ago

does the error occur on .ToList() or later in the code, like in .SaveChanges()?

|

Collectives™ on Stack Overflow

C# Entity Framework SQL Query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related