CODEWITHSUNDEEP
entity-frameworklinq
Ben Rubin
Ben Rubin

Reputation: 7331

Weird LINQ OrderBy Behavior

I'm trying to get a sorted list using LINQ and Entity Framework, and I'm getting some really weird results. My code looks something like this:

// My class that corresponds to a database table
class MyClass
{
   public string FirstField { get; set; }
   public string SecondField { get; set; }
   public string ThirdField { get; set; }
}

// My Entity Framework code
List<MyClass> firstList = (from p in context.myObjects select p).OrderBy(p => p.FirstField).ToList();
List<MyClass> secondList = firstList.OrderBy(p => p.FirstField).ToList();

When I look at my results, secondList is sorted correctly, but firstList is sorted incorrectly. For the most part it's sorted correctly, but some elements are out of order. For instance, firstList would look something like this:

AAA
BBB
CCC
DDD
NNN   <--- wrong order
EEE
FFF
GGG
PPP   <--- wrong order
HHH
III

However, secondList is sorted entirely correctly.

I've examined the query that LINQ is creating, and the query has the correct ORDER BY. I ran that same query in SQL Management Studio, and the results are ordered correctly (in the same order as secondList, but not firstList). I've tried doing this using orderby in my LINQ query rather than using OrderBy(), and the results are incorrect in the same way.

I don't understand what's going on here. Shouldn't firstList be sorted, and shouldn't firstList and secondList both be the same?

I'm using Fluent API to map my objects to tables and properties to fields if that makes any difference.

Upvotes: 1

Views: 280

Answers (2)

Tinypond
Tinypond

Reputation: 51

In your example the firstList was doing the ordering from the database, the secondList was doing the ordering from the object in memory. (both list should give you the same results, however they are doing the ordering differently one from the database the other sorting in memory, so it is possible there could be a bad index would be one cause)

if you try the following code the ordering we be done in memory for both the firstList and secondList and they will have the same results.

I have added a second .ToList() to your firstList syntax, this will cause the data to be retrieved and then ordered. rather the building a orderby clause in your sql code.

List<MyClass> firstList = (from p in context.myObjects select p).ToList().OrderBy (p=>p.FirstField).ToList();


List<MyClass> secondList = firstList.OrderBy(p => p.FirstField).ToList();

Upvotes: 0

Ben Rubin
Ben Rubin

Reputation: 7331

I see what the problem is. Thank you everyone for your suggestions; it got me looking in the right place. The problem is that the table has a composite key that is comprised of FirstField, SecondField, and ThirdField. When I was configuring my Context with Fluent API, I was copying some code from elsewhere in my Context, and I accidentally only set the table's primary key to FirstField. This resulted in Entity Framework assigning the same values of SecondField and ThirdField to each distinct value of FirstField. For instance, if the rows in the database were {A, BB, CCC}, {A, EE, FFF}, {A, HH, III}, Entity Framework was giving me {A, BB, CCC}, {A, BB, CCC}, {A, BB, CCC}. I corrected my HasKey(), and now everything's working.

Upvotes: 1

Related Questions