0

Today I've faced one very strange behavior. After creating an object with Linq query and object initializer with setting property of List<string> type the original collection and the collection that the object contains have different entries order.

public class PrintHeaderModel
{
    public List<string> Ships { get; set; }
}

...

var shipsList = new List<string>() { /* some items */ };

var model = (from inv in db.invoices
                         where inv.ListID == id && inv.RealmID == realmId
                         select new PrintHeaderModel()
                         {
                             Ships = shipsList,
                         }).FirstOrDefault();

After that the orders of entries in model.Ships and shipsList are different

Notes:

  1. db is DbContext instance (I'm using Entity Framework and MySQL database)
  2. shipsList is not sorted after it's filled
  3. If I create model object without Linq (just with "new"), the order of entries is the same in model and in the list

  4. The order becames correct if I reassign model.Ships right after model is created:

    model.Ships = shipsList; // after that the order of entries is correct

  5. The order of entries in model.Ships is not the same always. It changes randomly without any changes in code or database

Where was I wrong?

keymusicman
  • 1,281
  • 1
  • 10
  • 20
  • 5
    Without an OrderBy clause, sets obtained from the DB will be ordered in whichever way the DB sees fit. You're looking at an undefined sort order. It's not guaranteed to be consistent. – spender Dec 20 '16 at 15:22
  • To add to what @spender mentioned, this is probably more related to the indexes in your database. This is why as a matter of practice, you should never assume the rows will come in any order. You should order them explicitly yourself. – JuanR Dec 20 '16 at 15:33
  • @spender, I understand that the order of query result rows is undefined without OrderBy, but why is model.Ships order is undefined while shipsList is not read from DB? – keymusicman Dec 20 '16 at 15:33
  • @VMaleev I understand what you're asking now. Intriguing. – spender Dec 20 '16 at 15:39
  • 1
    I think it's probably because the list is being round-tripped to the database as part of the query, so even though it looks like a straight assignment, actually the list is being translated into the query, so what you get back isn't the same list that you assigned. – spender Dec 20 '16 at 15:41
  • You'd do better with `db.Invoices.Where(inv => inv.ListID == id && inv.RealmID == realmId).AsEnumerable().Select(inv => ...)` so that the list doesn't get round-tripped. – spender Dec 20 '16 at 15:44
  • @spender, I've just logged queries done by Entity Framework and yes, you are right, the list is passed to sql query as parameter. **There should be recomendation not to use initializers within linq queries with databases** because a query becomes really terrible with it. Please post [this](http://stackoverflow.com/questions/41245643/linq-sorts-list-in-object-initializer?noredirect=1#comment69692085_41245643) comment as answer and I'll accept it. Many thanks for your help. – keymusicman Dec 20 '16 at 15:54

0 Answers0