iT邦幫忙

0

ASP.NET MVC 1對多資料表搜尋

  • 分享至 

  • xImage

各位先進好,上來請教一下1對多資料表搜尋與View呈的問題,如下圖所示

單獨列出Paper資料表裡的資料絕對沒問題,但要加上PaperAuthor的資料小弟就卡住了~
如上圖表1是目前想要輸出的樣子,但不知道該用什麼方法實作,上來求救一下~

public ActionResult GetPaperList2(int cId)
{
    int MemberId = Convert.ToInt32(Session["MemberId"].ToString());
    PaperViewModel pVM = new PaperViewModel();
    pVM.DataList = paperSrv.GetPaperList(cId, MemberId);
    //pVM.DataList.ForEach(x =>
    //{
    //    pVM.PaperAuthorList.Add(new PaperAuthor { paperSrv.GetPaperAuthor(x.PaperId) });
    //});
    return View(pVM);
}
public List<Paper> GetPaperList(int cId, int MemberId)
{
    List<Paper> result = db.Papers.Where(p => p.ConferenceId == cId && p.MemberId == MemberId).ToList();
    return result;
}    
(VIEW程式)
<tbody>
    @if (!Model.DataList.Count.Equals(0))
    {
        int count = 0;
        foreach (var item in Model.DataList)
        {
            <tr>
                <td>
                    @item.Title
                </td>
                <td>
                    @item.MemberId
                </td>
                <td>
                    //Authors
                </td>
                <td>
                    @item.Status
                </td>
            </tr>
            count++;
        }
    }
</tbody>
石頭 iT邦高手 1 級 ‧ 2019-06-28 16:56:37 檢舉
請問可以提供一下 樣本資料 來描述你的一對多 ?
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
4
石頭
iT邦高手 1 級 ‧ 2019-06-28 22:13:39
最佳解答

首先你要先建立一個class 給你當你的viewModel簽章

public class ViewModel{
	 public int PaperId{get;set;}
	 public	int MemberId{get;set;}
	 public string Status{get;set;}
     public string Author{get;set;}
	
}

在來你可以使用lambda JOIN 關聯兩張表

GroupBystring.Join 完成 group by concat

var result = db.Papers.Join(db.PaperAuthor,
               x=>x.PaperId,
			   y=>y.PaperId,
			   (x,y)=> new {
				   x.PaperId,
				   x.MemberId,
				   x.Status,
				   y.Name
			   }).GroupBy(g => new{
				   g.PaperId,
				   g.MemberId,
				   g.Status})
				 .Select(g=>new ViewModel(){
					 PaperId = g.Key.PaperId,
				     MemberId = g.Key.MemberId,
				     Status = g.Key.Status,
					 Author = string.Join(",", g.Select(i => i.Name))
				 });
看更多先前的回應...收起先前的回應...
小魚 iT邦大師 1 級 ‧ 2019-06-28 23:51:10 檢舉

哇哇,
我都是用SQL解決,
倒是沒去研究過複雜的Linq.

fillano iT邦超人 1 級 ‧ 2019-06-29 20:27:01 檢舉

更簡潔的話可以用GroupJoin,意思是一樣的就是了。

暐翰 iT邦大師 1 級 ‧ 2019-06-30 00:52:01 檢舉

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大設為最佳解答方便後人參考

20190630005526.png

石頭 iT邦高手 1 級 ‧ 2019-06-30 12:05:10 檢舉

謝謝暐翰大 詳細補充 :)

leo226 iT邦新手 4 級 ‧ 2019-07-01 14:30:46 檢舉

@暐翰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}
0
小魚
iT邦大師 1 級 ‧ 2019-06-28 15:46:52

直接用SQL語法可以用GROUP BY,
不過看起來你是用Entity?

3
暐翰
iT邦大師 1 級 ‧ 2019-06-28 16:59:54
  • 用EF SqlQuery 直接解決,這個用SQL LINQ我記得不支援,要從資料庫讀取資料到AP端才能處理,假如怕弱類型難維護,可以用nameof來取得表格、欄位名稱
  • 建立ViewModel並在SqlQuery帶入泛型參數
  • 這是T-SQL SqlServer舊版本For Xml Group相接字串寫法,新版本可以使用STRING_AGG
  • 另外你沒有提供ConferenceId的結構,所以我沒有加入篩選
public List<PaperAuthorViewModel> GetPaperList(int cId, int MemberId)
{
	var sql = @"
		with cte as (
			select T1.PaperId,T1.MemberId,T1.Status,T2.Name from Paper T1
			left join PaperAuthor T2 on T1.PaperId = T2.PaperId
            where T1.MemberId = @p0
		)
		SELECT 
		  PaperId, MemberId, Status,
		  STUFF((
		    SELECT ', ' + Name 
		    FROM cte
		    WHERE (PaperId = Results.PaperId) 
		    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
		  ,1,2,'') AS Author
		FROM cte Results
		GROUP BY PaperId, MemberId, Status	
	
	";
    return db.Database.SqlQuery<PaperAuthorViewModel>(sql,MemberId).ToList();
}
    
public class PaperAuthorViewModel
{
	public int PaperId { get; set; }
	public int MemberId { get; set; }
	public string Status { get; set; }
	public string Author { get; set; }
	
}
    

20190628165742.png


測試DDL SQL

CREATE TABLE Paper(PaperId int, MemberId int, Status varchar(1));
	
INSERT INTO Paper(PaperId, MemberId, Status)
VALUES
	(1, 10, 'A'),
	(2, 11, 'B'),
	(3, 12, 'C')
;

CREATE TABLE PaperAuthor(PaperId int, AuthorId int, Name varchar(7));
	
INSERT INTO PaperAuthor
	(PaperId, AuthorId, Name)
VALUES
	(1, 1, 'Author1'),
	(1, 2, 'Author2'),
	(1, 3, 'Author3'),
	(2, 1, 'Author1')
;

我要發表回答

立即登入回答