Guid type and ( '>' greater than comparison) in LINQ to SQL

Question

Please, help me. I try compare two guid in .Where() query method. But I get compile error. Error 1 Operator '>' can not be applied to operands of type "System.Guid" and "System.Guid

Guid startUser = ////Here I get user by url id param
List<Friends> friends = Common.DataContext.Friends.Where(u => u.FriendID > startUser).Take(5);

So far you are describing a problem but you're not asking a question. This is a question-and-answer site; you'll get better results if you *ask a question*. *Guids are not intended to be ordered*, so the fact that you're getting an error is *good*. Guids are intended to be compared for identity only. That you are attempting to order based on guids is a red flag that indicates that you might be abusing the use of guids. Take a step back and ask if that's really what you want to order on. — Eric Lippert, Jan 13 '13 at 16:14
My series of articles on the right and wrong ways to use guids begins here: http://blogs.msdn.com/b/ericlippert/archive/2012/04/24/guid-guide-part-one.aspx It might help you. — Eric Lippert, Jan 13 '13 at 16:15
@EricLippert: _Guids are not intended to be ordered_ Maybe so, but the struct still implements `IComparable<>` in some funny way (lexicographical, same byte order as in `Guid.ToString()`), and I know this because I've seen people use a `SortedDictionary` ... — Jeppe Stig Nielsen, Jan 13 '13 at 16:25
@JeppeStigNielsen: That is an excellent point, thanks for mentioning it. Indeed, people often use GUIDs as keys and it is helpful to be able to lexocographically order them precisely so that the key lookup algorithm can be fast in an AVL tree / b tree / whatever. — Eric Lippert, Jan 13 '13 at 16:59
That is strange as GUID implements CompareTo http://msdn.microsoft.com/en-us/library/system.guid(v=vs.100).aspx — paparazzo, Jan 13 '13 at 17:56
I have the same problem in NHibernate. There is a perfectly good reason to do something like this. It's a common pattern for id based paging. Rather than using row_number() > rows past with "TOP n", you can take the next n rows whose id is greater than the last id from the previous page. That is much more efficient, particularly for the last page of many. In my case, I am paging through the whole table to build a search index. I go in GUID order because it is the clustered index and order doesn't matter. TSQL allows comparison in the query! We just need a way to write it in LINQ. — Jeff Walker Code Ranger, Aug 26 '13 at 22:31
There should be no problem with comparing GUIDs. It's stored as 16 bytes in SQL Server, and you can compare them there. You CAN do this in L2E. The answer is here. https://stackoverflow.com/questions/14116986/how-to-compare-two-guids-in-linq-to-entities — N73k, Oct 25 '17 at 22:35

score 6 · Answer 1 · answered Jan 13 '13 at 16:11

6

And with :

u.FriendID.CompareTo(startUser) > 0

?

answered Jan 13 '13 at 16:11

Pragmateek

13,174
9
74
108

This most likely doesn't work in "LINQ to SQL" I know it doesn't work in NHibernate. Just because to runs in code doesn't mean it knows how to translate it to a database query. – Jeff Walker Code Ranger Aug 26 '13 at 22:16
Similar question: https://stackoverflow.com/questions/14116986/how-to-compare-two-guids-in-linq-to-entities – Valderann Aug 20 '19 at 09:12

Lars Køhler · Answer 2 · 2016-12-02T15:33:38.693

I know this is an old question, but i have seen this data case before, and it might be relevant for someone in the future. This can be relevant where you for some reason have clustered your table (physically organized, default with Primary key) on a uniqueidentifier. If you need to batch read or page the table, you can end up with SQL queries that read:

SELECT TOP (@batchsize)
    *
FROM myTable
WHERE UserId > @previousBatchUserId

That is in general not a very optimal design, and as you have observed don't work with LINQ-to-SQL. Also, what is in a batch will change if you do the same query later after more rows are added. If you have a CreatedDate(time) column ordering by that instead would be much better. Or using the created date column as the first criteria, and then a != check on the GUID (if there can be more than one created with the same stamp). Ideally you would add a surrogate identity column (and cluster on it), and then you could filter on that ID where it is larger than of the one corresponding to your desired GUID.

It could then look like:

Guid startUser = //some guid
int startUserId = Common.DataContext.Friends.Single(u => u.FriendID == startUser).Id;
List<Friends> friends = Common.DataContext.Friends.Where(u => u.Id > startUserId);

Paul Sullivan · Answer 3 · 2013-01-13T16:46:12.903

-3

It doesn't make sense to apply greater than or less than to a Guid.

0f8fad5b-d9cb-469f-a165-70867728950e and 7c9e6679-7425-40de-944b-e07fc1f90ae7

Two Guids but which is greater? Do you drop the - and calculate the total of the HEX? or how about add the sums of the HEX between - ?

It's just simply not done (though it could be)

Apply logical == and != does however make sense though. The Guid struct does overload the == and != operators, so use them and you can also easily compare string values i.e.

var isEqual = guid.ToString().Equals(otherGuid.ToString());

edited Jan 13 '13 at 16:46

answered Jan 13 '13 at 16:14

Paul Sullivan

2,865
2
19
25

additionally... Guids tend to be system generated with no distinct sequence so unless you are generating sequential Guids for some reason whats the point in saying > or < ? What does it matter if one is larger or smaller than the other? – Paul Sullivan Jan 13 '13 at 16:18
The `Guid` struct does overload the `==` and `!=` operators, so use them. If you use `Equals(...)`, don't compare a string reference with a (boxed) `Guid` value; they will never equal. – Jeppe Stig Nielsen Jan 13 '13 at 16:30
modified to state otherGuid was a string (misleading) and thanks for clarifying the operator overloading is present on Guid @JeppeStigNielsen – Paul Sullivan Jan 13 '13 at 16:47
See my comment on the question for an example of when it makes sense to compare guids. – Jeff Walker Code Ranger Aug 26 '13 at 22:32
@JeffWalkerCodeRanger It still doesn't make sense. For example you state that you want to compare when one guid greater than the next BUT you do not (by default) get a Guid in numerical(?) order when creating them using Guid.NewGuid(). Now if the table that has the Guid as its index IS created in 'numeric' order then I can see your need... If that is the case then you will simpy overload the > operator, calculate the value of the guid (hex to dec or whatever other method you see fit) – Paul Sullivan Aug 27 '13 at 18:51
2

@PaulSullivan it doesn't matter what order they are generated in, I will read all rows for search index, I just need an efficient way to split them into pages. Your comment about overloading the > operator doesn't make sense. We are talking about Linq to SQL and NHibernate here. The query doesn't run in code, it runs in the DB. So it isn't about my method, it is about what the DB will do. Also, just because you can write some code in .NET doesn't mean Linq to SQL can figure out how to convert it to a SQL query. – Jeff Walker Code Ranger Aug 28 '13 at 15:40
2

Furthermore, @PaulSullivan I can't "simpy overload the > operator" because C# does not allow you to overload operators for existing types. If I write public static bool operator >=(Guid a, Guid b) {...} in some class it will complain "One of the parameters of a binary operator must be the containing type" – Jeff Walker Code Ranger Aug 28 '13 at 15:46
2

GUIDs are stored as 16 byte values in SQL Server. You can compare them in SQL Server. It does make sense if you want to have some kind of order. But as to how to compare them in Linq to EF, I don't know how to do that. – N73k Oct 25 '17 at 20:08

Guid type and ( '>' greater than comparison) in LINQ to SQL

3 Answers3

Linked