[.NET]如何用LINQ做到類似SQL的distinct(),排除重複的資料
下列的linq查詢結果,可以透過IEqualityComparer 的比較性質類別,來排除重複的資料
可以做到像是tsql一樣直接下distinct排除重複的資料的效果
var DataTableQuery = (from c in db.tblCase.AsEnumerable()
join h in db.mytable on c.CaseId equals h.CaseId
join de in db.mytableDtl on h.HitId equals de.HitId
select new MyDataModel
{
CreatedTime = h.CreateTime.ToString("yyyy-MM-dd"),
Idno = de.Idno,
}
).Distinct(new MyDataComparer());
public class MyDataModel
{
public string Idno { get; set; }
[Display(Name = "日期")]
public string CreatedTime { get; set; }
}
實做一個 IEqualityComparer 類別的細節如下
public class MyDataComparer : IEqualityComparer<MyDataModel>
{
public bool Equals(MyDataModel t1, MyDataModel t2)
{
return (t1.CreatedTime == t2.CreatedTime && t1.Idno == t2.Idno);
}
public int GetHashCode(MyDataModel t)
{
return t.ToString().GetHashCode();
}
}
補充方法二:
按照下面方式也可以達成distinct的效果,雖然不如上面的方法的高度客製化,但是在實務上是簡單快速方便使用
達成distinct的方式其實就是利用group by 語法
var DataTableQuery = from c in db.tblCase.AsEnumerable()
join h in db.mytable on c.CaseId equals h.CaseId
join de in db.mytableDtl on h.HitId equals de.HitId
select new MyDataModel
{
CreatedTime = h.CreateTime.ToString("yyyy-MM-dd"),
Idno = de.Idno,
};
DataTableQuery = DataTableQuery.GroupBy(g => new { g.CreatedTime, g.Idno })
.Select(p => new MyDataModel
{
CreatedTime = p.Key.CreatedTime,
Idno = p.Key.Idno,
});
public class MyDataModel
{
public string Idno { get; set; }
[Display(Name = "日期")]
public string CreatedTime { get; set; }
}
參考資料:
[C#]Linq在使用Distinct去除重複資料時如何指定所要依據的成員屬性
https://dotblogs.com.tw/larrynung/2012/09/18/74901
LINQ - DISTINCT的使用
https://blog.xuite.net/f8789/DCLoveEP/23587655-LINQ+-+DISTINCT%E7%9A%84%E4%BD%BF%E7%94%A8