CODEWITHSUNDEEP
.netsqlsql-serverentity-frameworktimestamp
Sir Hally
Sir Hally

Reputation: 2358

Select new records by timestamp column with Entity Framework

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.

Upvotes: 5

Views: 4120

Answers (3)

BootGenius
BootGenius

Reputation: 281

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.

Upvotes: 17

Sir Hally
Sir Hally

Reputation: 2358

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));

Upvotes: 1

Gert Arnold
Gert Arnold

Reputation: 109185

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.

Upvotes: 1

Related Questions