Selecting distinct objects from collection of objects using lambda expressions

Question

We have a project using Fluent NHibernate. There is an object called BluePart with a property of Oem of type Oem.

public class BluePart : DomainEntity
{
    ...
    public virtual Oem Oem { get; set; }
}

The Oem object has several properties including OemCode and OemDescription.

public class Oem : DomainEntity
{
    ...
    public virtual string OemCode { get; set; }
    public virtual string OemDescription { get; set; }
}

I am trying to build a linq query using lambda expressions that will get all distinct Oems from a list of BlueParts (2.7million records). Ideally it should produce the following sql (which runs in <1sec):

select distinct o.OemCode, o.OemDescription 
From BluePart b inner join Oem o on o.OemId = b.Oem_id

Below is the query I built which returns all Oems, regardless of distinctness.

var oem = repository.Query<BluePart>().Select(x => new Oem { OemCode =
x.Oem.OemCode, OemDescription = x.Oem.OemDescription}).ToList();

I thought this query would be easy to build but it's not turning out to be that way. When running a GroupBy (.GroupBy(z => z.OemCode)), I keep getting an error saying the property I try to GroupBy is not a property of Bluepart (which it shouldn't be because I'm grouping on a property of Oem)

Answer 1

How about:

var oem = repository.Query<BluePart>()
                    .Select(x => new { OemCode = x.Oem.OemCode, 
                                       OemDescription = x.Oem.OemDescription})
                    .Distinct()
                    .ToList();

This will only get you the distinct code/description pairs, but it looks like that's all you're interested in.

Alternatively, using grouping:

var oem = repository.Query<BluePart>()
                    .GroupBy(x => new { OemCode = x.Oem.OemCode, 
                                        OemDescription = x.Oem.OemDescription})
                    .ToList();

This will get you all the BluePart entities grouped by OEM code/description.