leo226
我這邊幫dog830228大的作法做一些補充

優點:

• 強型別LINQ方便維護
• 串接字串在AP端可以減輕資料庫Server CPU運算的壓力

注意:

假如D大的作法相當於以下三個SQL查詢,因為LINQ SQL不支援STRING_AGG,所以他會使用全欄位查詢後,再由LINQ Object來組合
資料量大的時候不建議這樣查詢,因為全部資料撈到AP Server會吃很大的記憶體空間

SELECT [t0].[PaperId], [t0].[MemberId], [t0].[Status]
FROM [Paper] AS [t0]
INNER JOIN [PaperAuthor] AS [t1] ON [t0].[PaperId] = [t1].[PaperId]
GROUP BY [t0].[PaperId], [t0].[MemberId], [t0].[Status]
GO

-- Region Parameters
DECLARE @x1 Int = 1
DECLARE @x2 Int = 10
DECLARE @x3 VarChar(1000) = 'A'
-- EndRegion
SELECT [t1].[Name]
FROM [Paper] AS [t0]
INNER JOIN [PaperAuthor] AS [t1] ON [t0].[PaperId] = [t1].[PaperId]
WHERE (((@x1 IS NULL) AND ([t0].[PaperId] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[PaperId] IS NOT NULL) AND (((@x1 IS NULL) AND ([t0].[PaperId] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[PaperId] IS NOT NULL) AND (@x1 = [t0].[PaperId]))))) AND (((@x2 IS NULL) AND ([t0].[MemberId] IS NULL)) OR ((@x2 IS NOT NULL) AND ([t0].[MemberId] IS NOT NULL) AND (((@x2 IS NULL) AND ([t0].[MemberId] IS NULL)) OR ((@x2 IS NOT NULL) AND ([t0].[MemberId] IS NOT NULL) AND (@x2 = [t0].[MemberId]))))) AND (((@x3 IS NULL) AND ([t0].[Status] IS NULL)) OR ((@x3 IS NOT NULL) AND ([t0].[Status] IS NOT NULL) AND (((@x3 IS NULL) AND ([t0].[Status] IS NULL)) OR ((@x3 IS NOT NULL) AND ([t0].[Status] IS NOT NULL) AND (@x3 = [t0].[Status])))))
GO

-- Region Parameters
DECLARE @x1 Int = 2
DECLARE @x2 Int = 11
DECLARE @x3 VarChar(1000) = 'B'
-- EndRegion
SELECT [t1].[Name]
FROM [Paper] AS [t0]
INNER JOIN [PaperAuthor] AS [t1] ON [t0].[PaperId] = [t1].[PaperId]
WHERE (((@x1 IS NULL) AND ([t0].[PaperId] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[PaperId] IS NOT NULL) AND (((@x1 IS NULL) AND ([t0].[PaperId] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[PaperId] IS NOT NULL) AND (@x1 = [t0].[PaperId]))))) AND (((@x2 IS NULL) AND ([t0].[MemberId] IS NULL)) OR ((@x2 IS NOT NULL) AND ([t0].[MemberId] IS NOT NULL) AND (((@x2 IS NULL) AND ([t0].[MemberId] IS NULL)) OR ((@x2 IS NOT NULL) AND ([t0].[MemberId] IS NOT NULL) AND (@x2 = [t0].[MemberId]))))) AND (((@x3 IS NULL) AND ([t0].[Status] IS NULL)) OR ((@x3 IS NOT NULL) AND ([t0].[Status] IS NOT NULL) AND (((@x3 IS NULL) AND ([t0].[Status] IS NULL)) OR ((@x3 IS NOT NULL) AND ([t0].[Status] IS NOT NULL) AND (@x3 = [t0].[Status])))))

總結:

以你的需求來看,有加where條件篩選並且資料量滿小的,建議使用D大的作法,因為大多數情況強型別維護性比性能好來的重要。

假如符合需求建議樓主可以幫D大設為最佳解答方便後人參考

謝謝暐翰大 詳細補充 :)

@暐翰dog830228感謝各位先進提供許多好方法，實作上又遇到一個錯誤，不知道輸出類型轉換該如何調整，再請教一下各位先進幫忙看看提供建議。（是因為我在最後加上ToList()所造成的錯誤嗎？或是我PaperAuthors資料表的主鍵是設定為PaperId＋AuthorId雙主鍵）
［抱歉，另外補充一下，上面樣本資料的MemberId應該是ConferenceId，但架構不變，概念是一樣的，然後PaperAuthors資料表的主鍵對應原文的圖是設定為PaperId＋AuthorId雙主鍵的架構，謝謝］

［Controller］
public ActionResult GetPaperList2(int cId)
{
    int MemberId = Convert.ToInt32(Session["MemberId"].ToString());
    PaperAuthorViewModel pVM = new PaperAuthorViewModel();
    pVM.paVMDataList = paperSrv.GetPaperList(cId, MemberId);
    return View(pVM);
}
［Service］
public List<PaperAuthorViewModel> GetPaperList(int cId, int MemberId)
{
    var result = db.Papers.Join(db.PaperAuthors,
        x => x.PaperId,
        y => y.PaperId,
        (x, y) => new {
            x.ConferenceId,
            x.PaperId,
            x.Title,
            x.Status,
            y.Name
        }).GroupBy(g => new {
            g.ConferenceId,
            g.PaperId,
            g.Title,
            g.Status
        })
        .Select(g => new PaperAuthorViewModel()
        {
            ConferenceId = g.Key.ConferenceId,
            PaperId = g.Key.PaperId,
            Title = g.Select(i => i.Title).ToString(),
            Status = Convert.ToInt32(g.Select(i => i.Status)),
            Name = string.Join(",", g.Select(i => i.Name))
        }).ToList();
    return result;
}
［ViewModel］
public class PaperAuthorViewModel
{
public Guid id { get; set; }
public PaperAuthor Data { get; set; }
public List<PaperAuthor> DataList { get; set; }
public List<PaperAuthorViewModel> paVMDataList { get; set; }
public int ConferenceId { get; set; }
public int PaperId { get; set; }
[Required]
[Display(Name = "作者姓名")]
public string Name { get; set; }
public string Title { get; set; }
public int Status { get; set; }
public DateTime SubmittedDate { get; set; }
}

［錯誤訊息］

'/' 應用程式中發生伺服器錯誤。
無法將類型 'collection[Edm.String(Nullable=True,DefaultValue=,MaxLength=50,Unicode=True,FixedLength=False)]' 的值轉換成字串。
描述: 在執行目前 Web 要求的過程中發生未處理的例外狀況。請檢閱堆疊追蹤以取得錯誤的詳細資訊，以及在程式碼中產生的位置。 

例外狀況詳細資訊: System.NotSupportedException: 無法將類型 'collection[Edm.String(Nullable=True,DefaultValue=,MaxLength=50,Unicode=True,FixedLength=False)]' 的值轉換成字串。

原始程式錯誤: 


行 69:         public List<PaperAuthorViewModel> GetPaperList(int cId, int MemberId)
行 70:         {
行 71:             var result = db.Papers.Join(db.PaperAuthors,
行 72:                 x => x.PaperId,
行 73:                 y => y.PaperId,

VisualStudio錯誤訊息，主要是指NullReferenceException，是指PaperAuthors資料表雙主鍵沒有給AuthorId值所造成的錯誤嗎？（是不是設為主鍵的就一定要給參考值？單給一個PaperId是不行的？）

'System.NullReferenceException' 類型的例外狀況發生於 EntityFramework.dll，但使用者程式碼未加以處理

其他資訊: 並未將物件參考設定為物件的執行個體。

'db.PaperAuthors.Sql' 擲回 'System.NullReferenceException' 類型的例外狀況	string {System.NullReferenceException}
'db.Papers.Sql' 擲回 'System.NullReferenceException' 類型的例外狀況	string {System.NullReferenceException}