LINQ List Initializer not keeping order from initialization

Question

I have the following problem: I select data from a DB, and for each item from the table, I create a model. That model contains a list, wich contains the name in 3 languages. But for some reason, LINQ does not keep my list initialization order.

Code:

db.SA_BamaType
            .Select(b => new BamaTypeModel()
            {
                id = b.p_bamatype,
                bamatypeNames = new List<string>()
                {b.bamatypeafdrukNL, b.bamatypeafdrukFR, b.bamatypeafdrukEN}
            }).ToList();

But when I debug this list, I see that the items are random switched from position. EN would become index 0, while NL should be index 0.

A dictionary would be the best use for this, but it seems LINQ can't translate a dictionary, that's why I used a List.

You can convert to a dictionary. See [ToDictionary() method](http://msdn.microsoft.com/en-us/library/system.linq.enumerable.todictionary.aspx)... Also, when you retrieve rows from a database, the order is never guaranteed unless you have a clustered index on a particular key (or group of keys), or sort the result set before processing it. — Mario J Vargas, Jan 23 '12 at 00:51
Order from database may not be guaranteed. But List() should guarantee storing the items in the passed order!! Strange. — Manish Basantani, Jan 23 '12 at 01:01
It's pretty strange indeed. It's not the order of the rows that's important, it's the order of the items I assign in the list initializer, wich seem to lose their order. I used a dictionary now, wich is actually the best method in this situation, but still I don't get why the list doesn't want to keep the order of the items I initialized. — Mortana, Jan 23 '12 at 01:02

score 3 · Accepted Answer · answered Jan 23 '12 at 00:49

3

but it seems LINQ can't translate a dictionary

Of course it can:

db.SA_BamaType
            .AsEnumerable()
            .Select(b => new BamaTypeModel()
            {
                id = b.p_bamatype,
                bamatypeNames = new Dictionary<string, string>
                {
                    { "NL", b.bamatypeafdrukNL },
                    { "FR", b.bamatypeafdrukFR },
                    { "EN", b.bamatypeafdrukEN }
                }
            }).ToList();

answered Jan 23 '12 at 00:49

Thomas Levesque

286,951
70
623
758

Thanks! It's the .AsEnumerable() that was missing, wich made it possible to use a dictionary with an initializer. – Mortana Jan 23 '12 at 00:57
BTW, analyzing this, I got a difference of +15sec pageload when using the .AsEnumerable() with dictionary, instead of a simple list. People who are interested why: using the .AsEnumerable() before the .Where() loads the whole table in memory, and then performs the where. This causes more load. More information: http://stackoverflow.com/questions/3311244/understanding-asenumerable-in-linq-to-sql – Mortana Jan 23 '12 at 01:15
@Mortana, it is the ToList that loads all the data in memory, not the AsEnumerable. The effect of AsEnumerable is that the table is treated as an IEnumerable rather than an IQueryable, so that the rest of the query is executed in memory (row by row) instead of translated to SQL. – Thomas Levesque Jan 23 '12 at 08:35

score 0 · Answer 2 · answered Jan 23 '12 at 05:37

var items = db.SA_BamaType
        .Select(b => 
        {
            id = b.p_bamatype,
            NL =  b.bamatypeafdrukNL,
            EN =  b.bamatypeafdrukEN,
            FR =  b.bamatypeafdrukFR
        }).ToList();
var models = items.Select(b=>
    new BamaTypeModel()
    { 
        id = b.id,
        bamatypeNames = new List<string>()
            {b.NL, b.FR, b.EN} 
    }
).ToList();

Small notes: new List() {..,..} is not a construction which LINQ2SQL can translate to SQL statements, so you can't use it in a LINQ query. If it worked for you, then you might transformed the query into objects already (as you noticed when AsEnumerable() is called).
And new List() {"A","B"} is the equivalent for:

var list = new List<string>();
list.Add("A");
list.Add("B");

So it should keep the declaration order ("A" should be at position 0 and "B" should be at position 1).

LINQ List Initializer not keeping order from initialization

2 Answers2