0

This is the Linq To SQL query I am trying to execute. However, Int64.Parse fails as there is no Hexadecimal to Decimal equivalent in SQL.

Basically, I have 2 columns Guid1 and Guid2 expecting to get a result where for example 1 -> 2 and 2 -> 1 are grouped together.

SomeTable.GroupBy(x => new { 
    Max = Math.Max(Int64.Parse(x.Guid1.Replace('-','0').Substring(0,15),16),
                Int64.Parse(x.Guid2.Replace('-','0').Substring(0,15),16) ),
    Min = Math.Min(Int64.Parse(x.Guid1.Replace('-','0').Substring(0,15),16),
                Int64.Parse(x.Guid2.Replace('-','0').Substring(0,15),16))
})

EDIT: To Add More Context, please refer to Linq distinct based on two columns The only difference here is except for number columns, we have GUID columns.

Community
  • 1
  • 1
Shyamal Parikh
  • 2,988
  • 4
  • 37
  • 78
  • Can you give an example of what the values in the table look like? – StingyJack May 12 '16 at 15:09
  • Guid: "18639423-01e3-4bc6-8940-12c950702949" – Shyamal Parikh May 12 '16 at 15:10
  • 1
    And why would you expect to be able to convert these in the first place? A GUID is not an integral value or a representation of one - its a Globally Unique IDentifier, its own data type. – StingyJack May 12 '16 at 15:11
  • @StingyJack In Asp.Net Identity Users are identified by GUID. So consider a chat table: `User1 -> User2` and `User2 -> User1` needs to be grouped for further manipulation. – Shyamal Parikh May 12 '16 at 15:12
  • I think you should describe exactly why you need to do this – Alex K. May 12 '16 at 15:13
  • But the guid is an identifier, like a person's first or last name. Its not something to do calculations upon like a birth date. I dont think I understand why you arent just ok with SomeTable .GroupBy(x => x.Guid1). – StingyJack May 12 '16 at 15:15
  • Why would you expect to be able to parse a 128bit value into a 64bit value? – Matthew Whited May 12 '16 at 15:20
  • @MatthewWhited That is what `SubString(0,15)` is for! – Shyamal Parikh May 12 '16 at 15:22
  • http://stackoverflow.com/questions/74148/how-to-convert-numbers-between-hexadecimal-and-decimal-in-c - maybe try replacing your ",16" with System.Globalization.NumberStyles.HexNumber or use the ",16" with a convert ? – Chris May 12 '16 at 15:24
  • @Chris Tried didn't work – Shyamal Parikh May 12 '16 at 15:25
  • use ulong after AsEnumerable on the set first – Matthew Whited May 12 '16 at 15:26
  • @MatthewWhited This is what I understood from your comment .fetch the data from Database and do the conversion and comparsion in C# itself. If that is the point, wouldn't that be costly? Given there would be many chat messages? – Shyamal Parikh May 12 '16 at 16:03
  • Yes, but their is nothing mapped in linq to sql to do this conversion. your other option is to to the work on the server side. Or better still fix your data. – Matthew Whited May 12 '16 at 16:06
  • @MatthewWhited Considering changing PK to Int64 now. Can't imagine why no one has faced this issue earlier. – Shyamal Parikh May 12 '16 at 17:03
  • 1
    The first 15 characters of a Guid are not guaranteed to be unique anyway. Using `Substring(0, 15)` might result in incorrect grouping. In addition, a Guid is a bad choice for a PK anyway. It will result in high index fragmentation in a short time. Which is extra bad on a clustered index. – Chris Dunaway May 12 '16 at 18:30

0 Answers0