11

I'm trying to get distinct string values out of an Ax repository, but I'm getting a lot of identical strings out (strings only contains numbers)

var ret = context.XInventTransBackOrder
    .Where(i => i.BatchRouteId != "")
    .Select(i => i.BatchRouteId)
    .Distinct()
    .ToList();

Where am I going wrong?

jessehouwing
  • 106,458
  • 22
  • 256
  • 341
nk2003dec
  • 534
  • 1
  • 4
  • 10
  • 1
    What is the type of BatchRouteId ? – AlexH Nov 26 '12 at 14:05
  • 5
    A Sample of the data would be nice. – Mark Kram Nov 26 '12 at 14:05
  • Yes BatchRouteId is a string – nk2003dec Nov 26 '12 at 14:07
  • 2
    Maybe need trim? var ret = context.XInventTransBackOrder.Where(i => i.BatchRouteId != "").Select(i => i.BatchRouteId.Trim()).Distinct().ToList(); – movax Nov 26 '12 at 14:09
  • 3
    I tried this out and it worked for me so you got something funky going on. If BatchRouteId is a string and the strings in question are actually identical, then Distinct() would remove them. So either a) BatchRouteId isn't really a string, or b) these identical strings aren't actually identical. – Rob Nov 26 '12 at 14:12
  • It looks like you're using Linq-to-AX, which version are you using? Distinct seems to be supported in the AX 2012 version according to Microsoft's documentation, but your question doesn't specify exactly which LINQ library you're using here... (see also: http://msdn.microsoft.com/EN-US/library/jj677293.aspx) – jessehouwing Nov 26 '12 at 18:42

3 Answers3

7

Have you tried

var ret = context.XInventTransBackOrder
    .Where(i => i.BatchRouteId != "")
    .Select(i => i.BatchRouteId)
    .ToList();
ret = ret
    .Distinct()
    .ToList();
Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
Jan Lund Madsen
  • 126
  • 1
  • 4
  • 5
    The one hundred dollar question is: Why? – Olivier Jacot-Descombes Nov 26 '12 at 14:33
  • Be careful with this, this can be very expensive, depending on the number of items returned the original list. This first fetches everything to your system and then creates distinct values in memory. What type is your context? Is it converting to some type of AX query or webservice call? Does that API understand the concept of Distinct? – jessehouwing Nov 26 '12 at 18:31
  • My context is a **LinqToDynamicsAx** context. I get 10 distinct strings. Otherwise I get 116 strings. Not sure if API understands distinct. It changes the C# code to X++ – nk2003dec Nov 27 '12 at 07:53
4

If the BatchRouteId was a XElement, for instance, then probably an object reference comparison would be performed. In that case change the code to

var ret = context.XInventTransBackOrder
    .Where(i => i.BatchRouteId != null && !String.IsNullOrEmpty(i.BatchRouteId.Value))
    .Select(i => i.BatchRouteId.Value)
    .Distinct()
    .ToList();

UPDATE #1

Note that some types implement implicit conversions making you think they were another type. You can pass a string to a XName parameter without explicit casting, and the string will automatically be converted to XName.


UPDATE #2

According to a comment of nk2003dec the context is LinqToDynamicsAx. I don't know this interface but probably it does not implement Distinct. What you can to in such a case, is to change the context form a XY-LINQ to Object-LINQ by using the System.Linq.Enumerable.AsEnumerable<TSource> extension method

var ret = context.XInventTransBackOrder
    .Select(i => i.BatchRouteId)
    .Where(id => id != "")
    .AsEnumerable()
    .Distinct()
    .ToList();

I also inverted Select and Where as this simplifies the access to BatchRouteId

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
2

X++ does not have a distinct operator. The deferred execution will try to execute on ToList() and will fail because of this.

Demogog
  • 41
  • 2