LINQ to Entities query and Distinct on GUID

Question

Hi all when I use the LINQ to Entities and then a Distinct to get unique Guids I'm getting all GUIDs. DB-Entry 1: {b3f86f18-f661-4511-a8b5-f85aa8e41b29} DB-Entry 2: {b3f86f18-f661-4511-a8b5-f85aa8e41b29} DB-Entry 3: {7078d941-0906-4a1b-9a27-85cfc9cf382b} DB-Entry 4: {7387d57e-6ac5-4b3b-b7f5-833383530bb6} using (OrderTableEntities msgcontext = new OrderTableEntities()) { try { var Query = from c in msgcontext.tbl_Order select c.OrderGuid; var distinctGuids = Query.Distinct(); } catch { } } After Execution of this code-part all 4 rows are queried. Basically I would have expected to get just 3 Entries back. What could cause this issue that it is not possible to get the DISTINCT Guid? Thanks.

Vulpes · Answer

I've googled that error and it appears that the Entity Framework doesn't support the ToString() method because it can't convert it to SQL which is a pity :( The theory I was working on is that since Guid is a value type and IQueryable is a reference type the Guids were being boxed. As Distinct() uses the default equality comparer the references for all four Guids would have been different even though the values of two of them were the same which was why all four were getting returned. If this theory were correct then converting to strings would have circumvented the problem as the default equality comparison for strings is by value. The only other approach I can suggest is not to use Distinct() but to compare the unboxed Guids 'manually'. If there's a much larger number than four you could also load them into a HashSet which should remove duplicates silently (see http://msdn.microsoft.com/en-us/library/bb359438.aspx ).

Maik Brauer · Answer

After I have out .toString() after the select statement ( select c.OrderGuid. ToString() ;) the Variable value of contactsQuery is: base {System.SystemException} = {LINQ to Entities does not recognize the method 'System.String ToString()' method and this method cannot be translated into a store expression.}

Vulpes · Answer

First of all I assume that applying Distinct() to contactsQuery rather than Query is just a slip when preparing the code for posting. If you apply the ToString() method to the Guids so that the query returns an IQueryable rather than an IQueryable  do you still get all 4 guids returned? var contactsQuery = from c in msgcontext.tbl_Order select c.OrderGuid. ToString() ; var distinctGuids = contactsQuery.Distinct();