4

I am writing an L2E query to compare two GUID values.It simply doesn't allow direct comparison, and also .ToString() method is not allowed on L2E queries.. How can we achieve this?

Nirman
  • 6,715
  • 19
  • 72
  • 139
  • 1
    Try [this](http://stackoverflow.com/questions/1765423/problem-getting-guid-string-value-in-linq-to-entity-query) using combination of L2E and L2O – Tilak Jan 02 '13 at 04:57
  • I think doing the way it in that example will not work for me.. My query is comparing GUIDs in where condition. – Nirman Jan 02 '13 at 05:48
  • 1
    You need to get equivalent string representation (in same format), and then do string comparison. For GUID to string in EF, example will help. For GUID to string in program you can use `.ToString` or may be you need some alteration (depending upon internal guid format differences) – Tilak Jan 02 '13 at 05:50
  • try .Equals, http://stackoverflow.com/questions/5725044/how-do-i-perform-a-case-insensitive-compare-of-guids-with-linq – Brij Jan 02 '13 at 06:13

2 Answers2

15

I don't know if this applies to your case, but I found that I can use the Guid.CompareTo method in Linq, and it properly converts this to SQL.

documentQuery.Where(s => s.DocumentGuid.CompareTo(MyGuidVariable) > 0);

This produces the following SQL:

AND ([Extent1].[DocumentGuid] > @p__linq__1)
Eric
  • 2,120
  • 1
  • 17
  • 34
  • Thanks for saving the day here. I finally could make my query work after hours of frustrating debugging. You, sir, deserve a cookie. – ZipionLive Jan 09 '17 at 09:48
  • 2
    This is the only place on the Internet I could find this correct answer. – N73k Oct 25 '17 at 22:33
  • 1
    Why do you use > 0 and not == 0? > gives me the wrong result, and == the expected one... @Eric – G43beli Aug 05 '18 at 22:58
  • 1
    https://msdn.microsoft.com/en-us/library/swb03xd9(v=vs.110).aspx#code-snippet-1 according to the msdn documentation, 0 is returnes when the two Guid are equal – G43beli Aug 05 '18 at 23:06
  • Regrettably, I don't get proper SQL for this with Azure SQL Server 12.0: `WHERE (CASE WHEN [a].[Id] = @__greatestProcessedId_0 THEN 0 WHEN [a].[Id] > @__greatestProcessedId_0 THEN 1 WHEN [a].[Id] < @__greatestProcessedId_0 THEN -1 END > 0)` – Timo Jul 17 '20 at 09:51
  • @twoflower I did not manage to solve it, and I'm not satisfied! I'm of the persuasion that we should not use UUIDs as primary keys, and I'm working on a drop-in replacement (NuGet package) that uses the `decimal` type, avoiding such problems. But it doesn't help for those existing tables... – Timo Jul 23 '20 at 07:44
  • @Timo Actually, I was using `CompareTo(...) == 1` and this was indeed generating the SQL you posted. But after changing it to `CompareTo(...) > 0`, I now get the correct SQL (I am using Entity Framework). – twoflower Aug 03 '20 at 16:56
  • @twoflower Glad to hear that it works for you! I only managed to get the SQL I posted above, and I never depend on a comparison result returning `1` or `-1` - only `0`, more, or less. I don't recall if I was using SQL Server or SQLite at the time. – Timo Aug 04 '20 at 17:38
-1

you need to compare string represantation of both the GUID parameters

string var1=Convert.ToString(GuidParam1);
string var2=Convert.ToString(GuidParam2)

then compare those string values

   if(var1.CompareTo(var2)==0)
Cris
  • 12,799
  • 5
  • 35
  • 50