-1

I have the following query expecting to retrieve only unique values from the entity according the selected School:

var semesters = (from cou in db.Courses
    join sem in db.Semesters on cou.SemesterID equals sem.ID
    where cou.FacultyID == id 
    select new SemestersModel {
        SemesterID = sem.ID,
        SemesterText = sem.Semester })
    .Distinct()
    .ToList();

However it returns all semesters contained in the entity Courses

  • Semester 1, Semester 1, Semester 1, Semester 2, Semester 2 ...etc. etc.

Please help me to grab only distinct semester names e.g.

  • Semester 1, Semester 2, etc.

P.S. my issue is different than this one Distinct in Linq based on only one field of the table

Community
  • 1
  • 1
Elizabeth Dimova
  • 255
  • 1
  • 3
  • 19

4 Answers4

3

You are using Distinct after you've created SemestersModel, if that type doesn't override Equals + GetHashCode only references are compared, they are different in this case.

So either override these methods, provide a custom IEqualityComparer<SemestersModel>(not supported in Linq-To-Entities or Linq-To-Sql) for Distinct or simply use Distinct before on an anonymous type:

var semesters = from cou in db.Courses
                join sem in db.Semesters 
                on cou.SemesterID equals sem.ID
                where cou.FacultyID == id 
                select new { SemesterID = sem.ID, SemesterText = sem.Semester };
List<SemestersModel> uniqueSemesterList = semesters
    .Distinct()   
    .Select(x => new SemestersModel {
        SemesterID = x.SemesterID, 
        SemesterText = x.SemesterText })
    .ToList(); // due to deferred execution only now the query above will be executed together with Distinct

This query can be translated to valid SQL(omitting the join and where):

SELECT DISTINCT SemesterID, SemesterText FROM dbo.Semesters

The query will be executed and the unique SemestersModel instances will be created in memory to fill the list.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Tim, so is projecting to a named type the equivalent of `AsEnumerable`? – spender Jul 06 '16 at 10:25
  • So, given that this all happens in SQL, is a projected named type treated differently from an anonymous type? – spender Jul 06 '16 at 10:33
  • @spender: the anonymous type can be translated to sql whereas the distinct after the type initialization will compare references. In sql: `SELECT DISTINCT SemesterID, SemesterText FROM dbo.TableName`. But i must admit that i'm not 100% sure how Linq-To-Entities will treat the `Distinct` after the object initialization. I guess it's ignored in sql and will really be applied in memory. – Tim Schmelter Jul 06 '16 at 10:44
  • I'm glad you understood my slightly oblique line of questioning. Might do some experiments later. You've pinpointed something I don't quite understand here. – spender Jul 06 '16 at 10:52
2

If you group your semesters by the field you want to distinct by, then select the first of the group, now you can project the result into SemestersModel instances.

(from cou in db.Courses
    join sem in db.Semesters on cou.SemesterID equals sem.ID
    where cou.FacultyID == id 
    select sem)
    .GroupBy(sem => sem.Semester)
    .Select(g => g.FirstOrDefault())
    .Select(sem => new SemestersModel {
        SemesterID = sem.ID,
        SemesterText = sem.Semester 
    })
    .ToList();
spender
  • 117,338
  • 33
  • 229
  • 351
  • Additional information: The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead. – Elizabeth Dimova Jul 06 '16 at 10:21
  • 1
    @ElizabethDimova That change should be fine. I updated my answer. In this case `FirstOrDefault` won't be returning `null` though, so we can forgo any null check. – spender Jul 06 '16 at 10:22
  • Yep. It worked like a charm this time. Thank you – Elizabeth Dimova Jul 06 '16 at 10:50
1

Distinct method use IEqualityComparer to compare the objects. So if you are filtering the records using Distinct() you can create a comparer implementing IEqualityComparer and pass it to Distinct()

Here is a example.

class Employee
{
    public int Id { get; set; }
    public String Name { get; set; }
}

class EmpEqualityComparer : IEqualityComparer<Employee>
{
    public bool Equals(Employee x, Employee y)
    {
        if (x.Id == y.Id && x.Name == y.Name)
            return true;
        else
            return false;
    }

    public int GetHashCode(Employee obj)
    {
        int hCode = obj.Id;
        return hCode.GetHashCode();
    }
}

Now you can test it.

List<Employee> emp = new List<Employee>();
        emp.Add(new Employee() { Id = 123, Name = "Saket"});
        emp.Add(new Employee() { Id = 123, Name = "Saket" });
        emp.Add(new Employee() { Id = 123, Name = "Saket" });
        emp.Add(new Employee() { Id = 123, Name = "Saket" });
        emp.Add(new Employee() { Id = 123, Name = "Saket" });
        emp.Add(new Employee() { Id = 123, Name = "Saket" });
        emp.Add(new Employee() { Id = 123, Name = "Saket" });

        EmpEqualityComparer eqi = new EmpEqualityComparer();

        var employees = (from e in emp
                         select new Employee { Id = e.Id, Name = e.Name }).Distinct(eqi).ToList();

It returns single record. enter image description here

Hope it helps.

Saket Choubey
  • 916
  • 6
  • 11
0

It is impossible...
You need to have the class SemesterModel inherit IEquatable. And override two function Equals and GetHashCode.

public class SemestersModel  : IEquatable<SemestersModel >{
public string SemesterID  { get; set; }
public string SemesterText  { get; set; }
public bool Equals(SemestersModel comp)
{
    if (SemesterID == comp.SemesterID && SemesterText == comp.SemesterText)
        return true;
    return false;
}
public override int GetHashCode()
{
    int hSemesterID = SemesterID == null ? 0 : SemesterID.GetHashCode();
    int hSemesterText = SemesterText == null ? 0 : SemesterText.GetHashCode();

    return hSemesterID ^ hSemesterText;
}}
Kevin Nguyen
  • 14
  • 2