需求說明:我想要搜尋相關專長的人物資訊A集合的資料排在列表上的最上方,再搜尋其它人物資訊B集合的資料清單,插入在A集合清單的下方。
問題:A集合裡可能會有重複的人物(因為一個人有多個專長,keyword為專長關鍵字),B集合裡會有A集合裡重複的人物資訊
所以想要在撈出的集合清單裡移除重複的資訊,google參考以下連結資訊做法
https://www.cnblogs.com/chenwolong/p/list.html
先測試使用了比較簡單的方法一,和網頁最下方的方法套用,都沒有如預期的排除重複資料
程式碼如下,使用了兩組SQL撈取不同條件下的人物資訊,再將資訊全部放入至EMV清單裡,最後再排除重複資訊,這樣應該就能達到我想要的清單資訊排序功能。
List<EditorMemberViewModel> EMV = new List<EditorMemberViewModel>();
string getkeyword = GetPaperKeywords(PaperId);
if (getkeyword != "" && getkeyword != null)
{
string[] keywords = getkeyword.Split(';');
foreach (var item in keywords)
{
string sql2 = @"SELECT *
FROM MemberRole AS mr
LEFT JOIN Members AS m on mr.MemberId = m.MemberId
LEFT JOIN MemberProfessionalField AS mp on mp.MemberId = m.MemberId
WHERE mr.Role = @Role AND mp.ProfessionalField LIKE '%" + item + @"% '
AND m.MemberId NOT IN(
SELECT ReviewerId
FROM ReviewAssignments AS ra
WHERE ra.PaperId = @PaperId
)";
var v2 = db.Database.SqlQuery<EditorMemberViewModel>(sql2,
new SqlParameter("PaperId", PaperId),
new SqlParameter("ProfessionalField", item),
new SqlParameter("Role", Role)).ToList();
v2.ForEach(o => EMV.Add(new EditorMemberViewModel
{
PaperId = PaperId,
Name = o.Name,
Account = o.Account,
RoleName = GetRole(o.Role),
MemberId = o.MemberId,
MemberTitle = o.MemberTitle,
School = o.School,
Department = o.Department,
ProfessionalFieldName = o.ProfessionalFieldName,
AssignedNum = GetReviewerAssignedNums(o.MemberId, ConferenceId)
}));
}
}//end if (getkeyword != "" && getkeyword != null)
string sql3 = @"SELECT *
FROM MemberRole AS mr
JOIN Members AS m ON mr.MemberId = m.MemberId
WHERE mr.Role = @Role
AND m.MemberId NOT IN(
SELECT ReviewerId
FROM ReviewAssignments AS ra
WHERE ra.PaperId = @PaperId
)";
var v3 = db.Database.SqlQuery<EditorMemberViewModel>(sql3,
new SqlParameter("PaperId", PaperId),
new SqlParameter("Role", Role)).ToList();
v3.ForEach(o => EMV.Add(new EditorMemberViewModel
{
PaperId = PaperId,
Name = o.Name,
Account = o.Account,
RoleName = GetRole(o.Role),
MemberId = o.MemberId,
MemberTitle = o.MemberTitle,
School = o.School,
Department = o.Department,
ProfessionalFieldName = o.ProfessionalFieldName,
AssignedNum = GetReviewerAssignedNums(o.MemberId, ConferenceId)
}));
var distinctItems = EMV.Distinct().ToList();
HashSet<EditorMemberViewModel> hs = new HashSet<EditorMemberViewModel>(EMV);
var ReviewerList = hs.ToList();
//** 注意搜尋條件必需排序,否則會報錯
return ReviewerList.ToPagedList(pageNumber, pageSize);
想請問這些移除重複的方法是否有使用上錯誤或誤解的地方呢?
先謝謝前輩指教了~
你要比較的是你定義的兩個物件, 需要實作自己的IEqualityComparer
邏輯
internal class EditorComparer : IEqualityComparer<EditorMember>
{
public bool Equals(EditorMember x, EditorMember y)
{
return x.Role == y.Role;
}
public int GetHashCode(EditorMember obj)
{
return obj.Role.GetHashCode();
}
}
internal class EditorMember
{
public string Name { get; set; }
public RoleType Role { get; set; }
....
}
感謝指教,有成功囉,想請問實作自己的介面通常是寫在那一個目錄架構下呢?目前是把它寫在同一個Service底下~
謝謝~
我可能會寫成通用的Extensions吧, 不同的Model只需要傳入比較條件(上方實作的EditorComparer),用法就跟LINQ Distinct一樣
從Repository取出資料
var members = RepositoryFactory.GetEditorMember();
var result = members.CustomedDistinct(new EditorComparer());
靜態方法
public static IEnumerable<TSource> CustomedDistinct<TSource>(this IEnumerable<TSource> source,
IEqualityComparer<TSource> equalityComparer)
{
var enumerator = source.GetEnumerator();
var hashSet = new HashSet<TSource>(equalityComparer);
while (enumerator.MoveNext())
{
if (hashSet.Add(enumerator.Current))
yield return enumerator.Current;
}
}
只需要傳入不同的Comparer, 就可以做不同的比較
試看看(註)
原先「全部加入 LIST, 再移除重覆的部份」
v3.ForEach(o => EMV.Add(new EditorMemberViewModel
{
PaperId = PaperId,
Name = o.Name,
Account = o.Account,
RoleName = GetRole(o.Role),
MemberId = o.MemberId,
MemberTitle = o.MemberTitle,
School = o.School,
Department = o.Department,
ProfessionalFieldName = o.ProfessionalFieldName,
AssignedNum = GetReviewerAssignedNums(o.MemberId, ConferenceId)
}));
改成「先檢查是否重覆,沒重覆再加入 LIST」
foreach (var o in v3) {
if (EMV.Exists(x => x.Name == o.Name)) {
//已存在同名者,不加入
} else {
//加入 LIST
EMV.Add(new EditorMemberViewModel
{
PaperId = PaperId,
Name = o.Name,
Account = o.Account,
RoleName = GetRole(o.Role),
MemberId = o.MemberId,
MemberTitle = o.MemberTitle,
School = o.School,
Department = o.Department,
ProfessionalFieldName = o.ProfessionalFieldName,
AssignedNum = GetReviewerAssignedNums(o.MemberId, ConferenceId)
};
}
}
註:意思大概是這樣
語法我沒有執行過,不一定正確
或許換個角度思考較簡單....從 SQL 下手
Demo
SQL的部份卡在這部份,因為要跑keyword廻圈查詢多次符合keyword的人員出來,還要將它排序在清單的上方,所以不知道該如何用一個SQL來解決這問題=,=...
string getkeyword = GetPaperKeywords(PaperId);
if (getkeyword != "" && getkeyword != null)
{
string[] keywords = getkeyword.Split(';');
foreach (var item in keywords)
{
string sql2 = @"SELECT *
FROM MemberRole AS mr
LEFT JOIN Members AS m on mr.MemberId = m.MemberId
LEFT JOIN MemberProfessionalField AS mp on mp.MemberId = m.MemberId
WHERE mr.Role = @Role AND mp.ProfessionalField LIKE '%" + item + @"%'
AND m.MemberId NOT IN(
SELECT ReviewerId
FROM ReviewAssignments AS ra
WHERE ra.PaperId = @PaperId
)";
var v2 = db.Database.SqlQuery<EditorMemberViewModel>(sql2,
new SqlParameter("PaperId", PaperId),
new SqlParameter("ProfessionalField", item),
new SqlParameter("Role", Role)).ToList();
v2.ForEach(o => EMV.Add(new EditorMemberViewModel
{
PaperId = PaperId,
Name = o.Name,
Account = o.Account,
RoleName = GetRole(o.Role),
MemberId = o.MemberId,
MemberTitle = o.MemberTitle,
School = o.School,
Department = o.Department,
ProfessionalFieldName = o.ProfessionalFieldName,
AssignedNum = GetReviewerAssignedNums(o.MemberId, ConferenceId)
}));
}
}//end if (getkeyword != "" && getkeyword != null)
看了前輩的說明還是沒有太大的頭緒...><