Select new records by timestamp column with Entity Framework

Question

I have a table with timestamp column (RowId) in my SQL Server database.

I want to query new rows according to this timestamp. The SQL query is following

SELECT *
 FROM [MyTable]
 where RowId>=0x0000000000A99B06

0x0000000000A99B06 is a max timestamp value from the previous query.

How can I make such a query using Entity Framework database-first? RowId maps to byte[] property and I have no idea how to compare byte arrays in a LINQ query.

score 17 · Answer 1 · answered Oct 16 '13 at 11:47

17

Actually you can do small hack. It works for me 100%

internal static class EntityFrameworkHelper
{
   public static int Compare(this byte[] b1, byte[] b2)
   {
      throw new NotImplementedException();
   }  
}

And after that you can use it like this:

public void SomeMethod()
{
   var messages = Set<Message>().Where(m => m.Modified.Compare(filter.TimeStamp) > 0).ToList();
}

It will eventually generate SQL syntax like this: "Select * from Messages Where Modified > @param). And it works. Exception is never thrown.

answered Oct 16 '13 at 11:47

BootGenius

251
3
11

this is excellent solution for this problem. There is as well another more complicated solution that allows creating generic version of this if someone needs: http://stackoverflow.com/a/38152016/2645243 – Lukas K Mar 09 '17 at 19:35
We have this same hack in our code-base. I don't know why EF just doesn't do the right thing out of the box. – Nelson Nov 01 '17 at 19:25

score 1 · Accepted Answer · answered Jul 21 '13 at 09:22

1

You can't do this with Entity Framework because it does not allow the >= operator in timestamp comparisons. It only allows =. You can do e.g.

var b = BitConverter.GetBytes(1000000L);
var query = from x in MyTable
            where x.RowId = b; // not >=

But that would not be very useful. So you've got to find another way to get new rows, e.g. values in an identity column, or add a "real" time stamp (datetime) column.

answered Jul 21 '13 at 09:22

Gert Arnold

105,341
31
202
291

Thank you. In general, I can do it in the following way: (from x in MyTable where x.Id > (from k in MyTable where k.ROWID == b select k.Id).First() select x); – Sir Hally Jul 22 '13 at 07:32

score 1 · Answer 3 · answered Jul 22 '13 at 07:43

The other way is to use plain SQL with EntityFramework.

It can avoid additional inner query (see Gert Arnold's answer and my comment), but it looks like ugly.

long timeStamp = 100500;
IEnumerable<MyTable> result = context.Database.SqlQuery<MyTable>(String.Format("SELECT * FROM MyTable WHERE ROWID>{0} ORDER BY RowId ASC", timeStamp));

Select new records by timestamp column with Entity Framework

3 Answers3

Linked

Related