CODEWITHSUNDEEP
sqlentity-frameworklinq-to-entitiesentity-framework-6
Benji
Benji

Reputation: 509

Why am I getting different results via LINQ to Entities that via run of SQL generated by the same query?

I'm working on a school project that was started by another group last semester. This semester I'm on a team that is tasked with completing this project. There are ZERO common people between the groups .... my team is a completely new team attempting to finish another teams project with little to no documentation.

Anyway, with that background out of the way, I am having an issue with the project. My entity framework seems to not like the views I have created. It may also be worth mentioning that when creating this view, it is a complex view and was created by joining about 6-7 tables

As an arbitrary test (i dont really need answers that have "what" in them), I have executed this query in SQL Management Studio 

SELECT *
FROM [dbo].[Course_Answers_Report] -- Course_Answers_Report is a View
WHERE question like '%what%'

Which produces the following output:

survey_setup_id | course_number | crn_number | term_offered |        course_title           | Instructor_Name    | question_type_id |                   question                           | answer
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2617            |    107013     |    5001    |    201505    |  Advanced Microsoft Access    | -output ommited-   |      2           | I  understood what the teacher was saying.           |    A
2617            |    107013     |    5001    |    201505    |  Advanced Microsoft Access    | -output ommited-   |      2           | I can apply what I learned in this class.            |    A
2617            |    107013     |    5001    |    201505    |  Advanced Microsoft Access    | -output ommited-   |      2           | I understood what was expected of me in this course. |    A

Now in Visual Studio i have this small bit of code (as a small side note this is in MVC, however the issue doesn't lie in MVC, but rather somewhere in the LINQ, Entity, or Controller.....this has been decided by doing some debugging).

public ActionResult modelTest()
{
        using (SurveyEntities context = new SurveyEntities())
        {
                context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

                var questions = context
                                .Course_Answers_Report
                                .Where(r => r.question.Contains("what"))
                                .ToList();

                ViewBag.Questions = questions;
         }
}

This outputs the following table on the View (again, the problem is decidedly not in the View because when debugging, the var that holds the List has all incorrect data)

survey_setup_id | course_number | crn_number | term_offered |        course_title           | Instructor_Name    | question_type_id |                   question                           | answer
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2617            |    107013     |    5001    |    201505    |  Advanced Microsoft Access    | -output ommited-   |      2           | I understood what the teacher was saying.            |    A
2617            |    107013     |    5001    |    201505    |  Advanced Microsoft Access    | -output ommited-   |      2           | I understood what the teacher was saying             |    A
2617            |    107013     |    5001    |    201505    |  Advanced Microsoft Access    | -output ommited-   |      2           | I understood what the teacher was saying.            |    A

As you can see, this output is incorrect as the question (or rather the record) never changes when it should be

The SQL generated by this linq statement is 

SELECT 
[Extent1].[survey_setup_id] AS [survey_setup_id], 
[Extent1].[course_number] AS [course_number], 
[Extent1].[crn_number] AS [crn_number], 
[Extent1].[term_offered] AS [term_offered], 
[Extent1].[course_title] AS [course_title], 
[Extent1].[Instructor_Name] AS [Instructor_Name], 
[Extent1].[question_type_id] AS [question_type_id], 
[Extent1].[question] AS [question], 
[Extent1].[answer] AS [answer]
FROM (SELECT 
[Course_Answers_Report].[survey_setup_id] AS [survey_setup_id], 
[Course_Answers_Report].[course_number] AS [course_number], 
[Course_Answers_Report].[crn_number] AS [crn_number], 
[Course_Answers_Report].[term_offered] AS [term_offered], 
[Course_Answers_Report].[course_title] AS [course_title], 
[Course_Answers_Report].[Instructor_Name] AS [Instructor_Name], 
[Course_Answers_Report].[question_type_id] AS [question_type_id], 
[Course_Answers_Report].[question] AS [question], 
[Course_Answers_Report].[answer] AS [answer]
FROM [dbo].[Course_Answers_Report] AS [Course_Answers_Report]) AS [Extent1]
WHERE [Extent1].[question] LIKE N'%what%'

When this SQL is run inside SQL management studio, it produces proper results. I am at a loss as to why EF is behaving this way, can anyone offer insight

EDIT: Per request of Danny Varod, the EDMX can be found here http://pastebin.com/dUf6J4fV and the View can be found here http://pastebin.com/sCsqNYWc (the view is kind of ugly/sloppy as it was just supposed to be a test and experiment)

Upvotes: 0

Views: 773

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

Your problem is visible in the edmx file;

warning 6002: The table/view 'wctcsurvey.dbo.Course_Answers_Report' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.

<EntityType Name="Course_Answers_Report">
<Key>
    <PropertyRef Name="survey_setup_id" />
</Key>

You have not defined a primary key in the table, so one has been "guessed". Since the guessed column survey_setup_id is not unique in the table (all 3 rows in the correct result have the same value), EF will get confused and fetch the same object 3 times (it has the same guessed primary key after all).

If you add a correct primary key annotation to your model (ie a unique field), the problem will disappear.

Upvotes: 3

Related Questions