Database records added to top of table instead of bottom using LINQ

Question

Hello I've currently got a program which retrieves WMI information and stores it to a database, after a certain period of time older records are then deleted. Currently I'm having my program delete records over 10 mintues old and it checks this every 2 mintues after populating the database with new information. In another timer which runs every 2 mintues the information stored in the database is displayed to the user, this appears in the same order that it appears in the database table.

The issue I'm having occurs when the program is run from a fresh database after 10 mintues when records are deleted, instead of the new records being added to the end of the database table they appear at the top. It also occurs when the program is re-opened with existing data already populating the database once some of the old data has been deleted.

Below I've added code showing how my data is added to the database and also how it is deleted (This occurs in 2 seperate classes). Below that I have indluded some sample output data so you can understand what I mean.

Basically I need a way to resolve this as the data needs to be shown in the order it was collected based on the DateTime, so if you can spot anything it would be appreciated, if not would the best way to resolve this be sorting the data before it is displayed?

DateTime dateTime = DateTime.Now.Subtract(new TimeSpan(0, 0, 10, 0));

var result2 = (from b in hddInfo
               where b.DateTime < dateTime
               select b).DefaultIfEmpty();

foreach (TblHDDInfo record2 in result2)
{
        if (record2 != null)
        {
        localDB.TblHDDInfo.DeleteOnSubmit(record2);
        }
}

localDB.SubmitChanges();

TblHDDInfo hddInfoAdd = new TblHDDInfo();
                            hddInfoAdd.DeviceID = deviceID;
                            hddInfoAdd.IpAdd = ipAdd;
                            hddInfoAdd.Size = size;
                            hddInfoAdd.FreeSpace = freeSpace;
                            hddInfoAdd.DateTime = dateTime;
                            localDB.TblHDDInfo.InsertOnSubmit(hddInfoAdd);

                            localDB.SubmitChanges();

1st time HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:27:21

HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:29:26

HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:31:31

HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:33:36

HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:35:41

2nd time HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:37:46

HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:29:26

HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:31:31

HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:33:36

HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:35:41

3rd time HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:39:51

HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:37:46

HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:29:26

HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:31:31

HDD Size: 186GB Remaining space:157GB DateTime:19/07/2009 17:33:36

Answer 1

Why not just add an orderby b.DateTime to your LINQ query?

Just a clarification: Databases are by their very nature unordered and the location of inserts is indeterminant, in that, the location of a row in the actual table is a meaningless concept at the level of abstraction you work at with when interacting in your code against the DB. You have to sort the DB output by one or more of the fields to apply an ordering.

Answer 2

I'm not 100% sure what you're trying to accomplish since there is no "top" and "bottom" of a database table. But, this line of code ...

hddInfoAdd.DateTime = dateTime;

... seems wrong to me. Shouldn't it be like ...

hddInfoAdd.DateTime = DateTime.Now;

Answer 3

One of the central tenants of DB theory is that data is stored unsorted. The idea is that there's many ways that people might want to sort the data down the road, so applying a particular sort order to the raw storage is wasteful. It's just an accident / implementation detail that DBMSs display unsorted data in the order that it was inserted (and I've seen cases where that's not always true as well).

If you want a particular order to your data, you should apply that desire explicitly using order by clauses (and potentially indexes).

Answer 4

The only order in SQL is the order that you impose using ORDER BY based on column values.

ALL Other apparent Row Orders are Coinidental.

If you want to control the order of appearance then you must add columns to control it and use ORDER BY on your SELECTs to implement it. If you already have the column (dateTime) then you just have to add the ORDER BYs