LINQ .Distinct()

Hi,

I'm having a problem using .Distinct() in Linq. As part of my troubleshooting I have enabled logging using the TraceLogger. To demonstrate the problem I have some code snippets and the sql they generate:

SNIPPET 1 - C#

        Db.Ctx.Logger = new Mindscape.LightSpeed.Logging.TraceLogger();

        var computers = (from d in uow.Data_ItilAssetLabel_Entries
                         where d.Config_ItilAssetLabelId == aggregationConfigId
                             && d.DisplayData.ToUpper() == aggregationDisplayData.ToUpper()
                         select new
                         {
                             ComputerName = d.ComputerName,
                             ActiveDirectoryId = d.Config_ActiveDirectoryId
                         }).Distinct();

        details.TotalAssets = computers.Count();       // <---- this line causes the query to be executed

SNIPPET 1 - SQL

SELECT
  COUNT(*)
FROM
  Data_ItilAssetLabel_Entry
WHERE
  (Data_ItilAssetLabel_Entry.Config_ItilAssetLabelId = 7 AND UPPER(Data_ItilAssetLabel_Entry.DisplayData) = 'CLINICAL SPECIALTY SYSTEMS')

This seems odd as the resulting SQL query does not use the DISTINCT keyword. By changing the line that causes the query to be executed to include .AsEnumerable() before the .Count() the resulting SQL query changes to include the DISTINCT keyword, see SNIPPET 2.

SNIPPET 2 - C#

        Db.Ctx.Logger = new Mindscape.LightSpeed.Logging.TraceLogger();

        var computers = (from d in uow.Data_ItilAssetLabel_Entries
                         where d.Config_ItilAssetLabelId == aggregationConfigId
                             && d.DisplayData.ToUpper() == aggregationDisplayData.ToUpper()
                         select new
                         {
                             ComputerName = d.ComputerName,
                             ActiveDirectoryId = d.Config_ActiveDirectoryId
                         }).Distinct();

        details.TotalAssets = computers.AsEnumerable().Count();  // <------ this line has had .AsEnumerable() added to it

SNIPPET 2 - SQL

SELECT DISTINCT
  Data_ItilAssetLabel_Entry.ComputerName AS [Data_ItilAssetLabel_Entry.ComputerName],
  Data_ItilAssetLabel_Entry.Config_ActiveDirectoryId AS [Data_ItilAssetLabel_Entry.Config_ActiveDirectoryId]
FROM
  Data_ItilAssetLabel_Entry
WHERE
  (Data_ItilAssetLabel_Entry.Config_ItilAssetLabelId = 7 AND UPPER(Data_ItilAssetLabel_Entry.DisplayData) = 'CLINICAL SPECIALTY SYSTEMS')

My LightSpeed version is 5 Professional 2014 07 23 (nightly build).

Is there something I'm missing in my code or is this a LS bug? Any help you can offer is greatly appreciated!

Thanks, Alex

No you are not missing anything this is a quirk of how we translate LINQ queries as we need to map them to our underlying querying API. Because you are executing a Count query and there is only a single table involved we don't consider the projection(and therefore the distinct aspect). As you have noted the workaround here is to force enumeration and count the resulting projected objects which will have the distinct clause filtering them.

Although this is certainly not optimal in terms of the translation we are not intending to address this for now.

Jeremy

Hi Jeremy,

Thanks for letting me know what's going on here.

Yeah, I can certainly make-do with the workaround, although I do hope that one day this quirk is addressed (or even just an exception thrown to alert the developer to the fact that their statement will not produce the expected results).

Cheers, Alex