如何将其他表连接到我的LINQ语句并获取所选列?

本文关键字：获取 语句 LINQ 我的 其他 连接 | 更新日期: 2023-09-27 18:08:33

我创建了这个linq语句来获取AdminTestQuestions的列表:

var adminTests = await db.AdminTests
        .Include(t => t.AdminTestQuestions)
        .Where(t => t.ExamId == examId || examId == 0)
        .Where(t => t.TestStatusId == testStatusId || testStatusId == 0)
        .ToListAsync();
        return Ok(adminTests);

语句可以工作，但我需要在已有的列基础上再添加两列:

我想做的是也得到

  • Title的问题从问题表和
  • SubTopidId from problem table

谁能告诉我如何扩展我的linq语句来做到这一点?让我困惑的是我如何才能阻止linq获取所有的Question和Problem表列。

CREATE TABLE [dbo].[AdminTest] (
    [AdminTestId]  INT            IDENTITY (1, 1) NOT NULL,
    [Title]        NVARCHAR (100) NOT NULL,
CREATE TABLE [dbo].[AdminTestQuestion]
    [AdminTestQuestionId] INT              IDENTITY (1, 1) NOT NULL,
    [AdminTestId]         INT              NOT NULL,
    [QuestionUId]         UNIQUEIDENTIFIER NOT NULL,
CREATE TABLE [dbo].[Question] (
    [QuestionId]       INT              IDENTITY (1, 1) NOT NULL,
    [ProblemId]        INT              NOT NULL,
    [QuestionUId]      UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [Title]            NVARCHAR (100)   NULL,
    [Grade]            INT              NOT NULL,
CREATE TABLE [dbo].[Problem] (
    [ProblemId]       INT             IDENTITY (1, 1) NOT NULL,
    [SubTopicId]      INT             NOT NULL,
    [Title]           NVARCHAR(20)    NULL

如何将其他表连接到我的LINQ语句并获取所选列?

您可以采取的一种方法是在返回之前将AdminTests扁平化为DTO对象。这使您可以显式地控制数据的结构和哪些列是可见的。

首先你需要一个类:

public class AdminTestDto 
{
    public int AdminTestId { get; set; }
    public string Title { get; set; }
    public int AdminTestQuestionId { get; set; }
    public int QuestionUId { get; set; }
    public string QuestionTitle { get; set; }
    public int SubTopicId { get; set; }
    public AdminTestDto(AdminTest a)
    {
        this.AdminTestId = a.AdminTestId;
        this.Title = a.Title;
        this.AdminTestQuestionId = a.AdminTestQuestion.AdminTestQuestionId;
        this.QuestionUId = a.AdminTestQuestion.QuestionUId;
        this.QuestionTitle = a.AdminTestQuestion.Question.Title;
        this.SubTopicId = a.AdminTestQuestion.Question.Problem.SubTopicId;
    }
}

然后在你的LINQ中:

var adminTests = await db.AdminTests
    .Include(t => t.AdminTestQuestions)
    .Where(t => t.ExamId == examId || examId == 0)
    .Where(t => t.TestStatusId == testStatusId || testStatusId == 0)
    .Select(t => new AdminTestDto(t))
    .ToListAsync();
    return Ok(adminTests);
相关文章：