如何在C#中通过SqlContext从SQL Server获取指定EventId的对象列表

问题背景 #

我维护一个C#后端系统，需要从SQL Server中获取IdEvent列匹配指定ID的所有EventDate对象列表，系统全程使用SqlContext操作，但现有示例方法仅返回单条数据，不确定如何修改以实现需求。

现有示例方法 #

public async Task<EventDate> GetById(int id)
{
  return await _sqlContext.EventDate
    .Include(x => x.Hours)
    .Include(x => x.Event)
    .Where(x => x.Id == id)
    .AsNoTracking()
    .FirstOrDefaultAsync();
}

EventDate表创建脚本 #

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[EventDate](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [IdEvent] [int] NOT NULL,
    [IdZoomAccount] [int] NULL,
    [Date] [datetime2](7) NOT NULL,
    [Hour] [time](7) NULL,
    [Type] [int] NOT NULL,
    [Url] [nvarchar](max) NULL,
    [MeetingType] [int] NOT NULL,
    [DurationHours] [nvarchar](max) NULL,
    [DurationMinutes] [nvarchar](max) NULL,
    [IdRoom] [int] NULL,
    [IdLocation] [int] NULL,
    [EventType] [int] NOT NULL,
 CONSTRAINT [PK_EventDate] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[EventDate] ADD  DEFAULT ((0)) FOR [MeetingType]
GO

ALTER TABLE [dbo].[EventDate] ADD  DEFAULT ((0)) FOR [IdLocation]
GO

ALTER TABLE [dbo].[EventDate] ADD  DEFAULT ((0)) FOR [EventType]
GO

ALTER TABLE [dbo].[EventDate]  WITH CHECK ADD  CONSTRAINT [FK_EventDate_MeetingLocation_IdLocation] FOREIGN KEY([IdLocation])
REFERENCES [dbo].[MeetingLocation] ([Id])
GO

ALTER TABLE [dbo].[EventDate] CHECK CONSTRAINT [FK_EventDate_MeetingLocation_IdLocation]
GO

ALTER TABLE [dbo].[EventDate]  WITH CHECK ADD  CONSTRAINT [FK_EventDate_Event_IdEvent] FOREIGN KEY([IdEvent])
REFERENCES [dbo].[Event] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[EventDate] CHECK CONSTRAINT [FK_EventDate_Event_IdEvent]
GO

ALTER TABLE [dbo].[EventDate]  WITH CHECK ADD  CONSTRAINT [FK_EventDate_Rooms_IdRoom] FOREIGN KEY([IdRoom])
REFERENCES [dbo].[Rooms] ([Id])
GO

ALTER TABLE [dbo].[EventDate] CHECK CONSTRAINT [FK_EventDate_Rooms_IdRoom]
GO

ALTER TABLE [dbo].[EventDate]  WITH CHECK ADD  CONSTRAINT [FK_EventDate_ZoomAccount_IdZoomAccount] FOREIGN KEY([IdZoomAccount])
REFERENCES [dbo].[ZoomAccount] ([Id])
GO

ALTER TABLE [dbo].[EventDate] CHECK CONSTRAINT [FK_EventDate_ZoomAccount_IdZoomAccount]
GO

实现方案与解释 #

1. 核心实现代码 #

将原方法修改为以下形式，即可获取指定EventId对应的所有EventDate记录：

public async Task<List<EventDate>> GetByEventId(int eventId)
{
    return await _sqlContext.EventDate
        .Include(x => x.Hours)
        .Include(x => x.Event)
        .Where(x => x.IdEvent == eventId) // 匹配需求中的EventId列（即表中的IdEvent字段）
        .AsNoTracking()
        .ToListAsync();
}

2. 关键修改点说明 #

• 返回类型调整：从Task<EventDate>改为Task<List<EventDate>>，适配多结果返回场景
• 过滤条件修正：原方法匹配的是主键Id，现在改为匹配外键IdEvent，对应需求中"EventId列匹配指定ID"的要求
• 终止方法替换：FirstOrDefaultAsync()仅返回第一条匹配记录或null，ToListAsync()会返回所有符合条件的记录集合
• 保留关联加载：继续使用Include加载Hours和Event导航属性，确保返回数据的完整性
• 保留无跟踪查询：AsNoTracking()避免EF Core缓存查询结果，提升只读查询的性能

3. 可选优化：分页查询 #

如果预期结果集较大，建议添加分页逻辑避免一次性加载过多数据：

public async Task<List<EventDate>> GetByEventId(int eventId, int pageIndex, int pageSize)
{
    return await _sqlContext.EventDate
        .Include(x => x.Hours)
        .Include(x => x.Event)
        .Where(x => x.IdEvent == eventId)
        .AsNoTracking()
        .Skip(pageIndex * pageSize)
        .Take(pageSize)
        .ToListAsync();
}

4. 参数校验建议 #

可以添加参数合法性检查，避免无效输入：

public async Task<List<EventDate>> GetByEventId(int eventId)
{
    if (eventId <= 0)
    {
        return new List<EventDate>();
        // 或者抛出异常：throw new ArgumentOutOfRangeException(nameof(eventId), "EventId必须大于0");
    }
    
    return await _sqlContext.EventDate
        .Include(x => x.Hours)
        .Include(x => x.Event)
        .Where(x => x.IdEvent == eventId)
        .AsNoTracking()
        .ToListAsync();
}

内容的提问来源于stack exchange，提问作者H4mAdAr4