LINQ to Entities query and Distinct on GUID

Maik Brauer

15y10kLINQ

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.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes15y ago
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 Brauer15y ago
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."}
Vulpes15y ago
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();