AnotherDeveloper AnotherDeveloper -4 years ago 74
ASP.NET (C#) Question

C# Insertion Order into List from Entity Framework

I was surprised to see that I am having insertion order issues into a C# list when instantiating it from an Entity Framework select.

I am creating multiple dto's which contain a list of keys, but the insertion order is random per dto object. It's the weirdest thing. I thought that insertion order into c# lists was preserved, so I'm trying to figure out where the order is being determined.

Server Code:

var locations = Context.Location.Where(x => x.fk_LocationId == id).Include(t => t.DoctorLocations);


Region.Locations = locations
.SelectMany(x => x.DoctorLocations)
.Where(y => y.fk_LocationId == id)
.Select(x =>
new SortableItem
{
Keys = new List<int> { x.fk_doctorId, x.fk_locationId },
Type = "Practice",
Priority = x.Priority ?? 0,
}).OrderBy(x => x.Priority).ToList();


View:

@for (var i = 0; i < Model.DoctorLocations.Count(); i++)
{
@Html.ActionLink("Remove", "Delete", "DoctorLocation" new { doctorId= Model.Locations[i].Keys[0], locationId= Model.Locations[i].Keys[1], }, null)
}


Update Per @PaulAbbot

I removed the SelectMany but get the same result. I also created more dto objects to look for a pattern in when they alternate. I don't see one, but they consistent in how they are returned from the server.

Region.Locations = Context.DoctorLocations.Where(y => (y.fk_doctorId == doctorId) && locations.Select(x => x.locationId).Contains(y.fk_locationId))
.Where(y => y.fk_doctorId == doctorid)
.Select(x =>
new SortableItem {
Keys = new List<int> { x.fk_doctorId, x.fk_locationId }
}).OrderBy(x => x.Priority).ToList();

Answer Source

Looks like there is a bug in EF6 processing projections containing constructs like new List<int> { val1, val2, ...}. Although it does not generate NotSupportedException, the generated SQL ORDER BY clause needed for correctly materializing the result is wrong (using a constant instead of a index selector expression).

In case you don't need IQueryable<T> result, I would suggest the usual double projection workaround - use normal anonymous type projection in LINQ to Entities query, then at the end switch to LINQ to Objects and do the desired projection:

var result = locations
    .SelectMany(x => x.DoctorLocations)
    .Where(y => y.fk_LocationId == id)
    .Select(x => new
    {
        K1 = x.fk_doctorId, K2 = x.fk_locationId,
        Type = "Practice",
        Priority = x.Priority ?? 0,
    })
    .OrderBy(x => x.Priority)
    .AsEnumerable() // switch to L2O
    .Select(x => new SortableItem
    {
        Keys = new List<int> { x.K1, x.K2 },
        Type = x.Type,
        Priority = x.Priority,
    })
    .ToList();

I know, annoying, but at least works as expected :)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download