c# - LINQ .FromSQL 错误 InvalidOperationException : Sequence contains more than one matching element

Question

我在使用 Entity Framework 和 linq 从存储过程中请求数据时引发了这个异常。

An unhandled exception occurred while processing the request.

InvalidOperationException: Sequence contains more than one matching element
System.Linq.Enumerable.SingleOrDefault<TSource>(IEnumerable<TSource> source, 
Func<TSource, bool> predicate)

堆栈的相关部分在这里(抱歉，它分两行，但堆栈溢出编辑器不会让我在不离开代码块格式的情况下破坏它):

System.Linq.Enumerable.SingleOrDefault<TSource>(IEnumerable<TSource> source, 
Func<TSource, bool> predicate) Microsoft.EntityFrameworkCore.Query.Sql.Internal.FromSqlNonComposedQuerySqlGenerator.CreateValueBufferFactory(IRelationalValueBufferFactoryFactory relationalValueBufferFactoryFactory, DbDataReader dataReader) Microsoft.EntityFrameworkCore.Internal.NonCapturingLazyInitializer.EnsureInitialized<TParam, TValue>(ref TValue target, TParam param, Func<TParam, TValue> valueFactory) Microsoft.EntityFrameworkCore.Query.Internal.ShaperCommandContext.NotifyReaderCreated(DbDataReader dataReader) Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable+AsyncEnumerator+<BufferlessMoveNext>d__9.MoveNext() System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

错误源自使用 .FromSql 作为 linq 查询的一部分的数据库调用。我像这样调用数据 - 即使它有一个 .ToListAsync() 堆栈跟踪表明 linq 语句仍然在返回的每个行项目上调用 .SingleOrDefault() - 我假设这是有意的:

[HttpPost, Route("GetOneWeekRosters")]
    public async Task<List<RosterLineView>> GetOneWeekRosters([FromBody] RosterSearch mysearch)
    {
        var dataentry = await _edb.RosterLineViews.FromSql("GetOneWeekRosters @Date, @DepartmentID, @RosterID, @RosterWeekID, @Active", new SqlParameter("@Date", mysearch.DateSelected), new SqlParameter("@DepartmentID", mysearch.DepartmentID), new SqlParameter("@RosterID", mysearch.RosterID), new SqlParameter("@RosterWeekID", mysearch.RosterWeekID), new SqlParameter("@Active", "true")).ToListAsync();
        return dataentry;
    }

返回的数据集是一个平面数组，由这个 View 表示:

 public class RosterLineView
{

    public int RosterLineID { get; set; }
    public int RosterID { get; set; }
    public int EmployeeID { get; set; }
    public int RosterShiftID { get; set; }
    public string RosterShiftName { get; set; }
    public string Notes { get; set; }
    public int RosterAreaID { get; set; }
    public string RosterAreaName { get; set; }
    public DateTime DateEntry { get; set; }
    public int RosterWeekID { get; set; }
    public DateTime RosterShiftStart { get; set; }
    public DateTime RosterShiftFinish { get; set; }
    public int ActivityTypeID { get; set; }
    public string ActivityTypeShort { get; set; }
    public decimal RosterHourValue { get; set; }
    public int PositionID { get; set; }
    public int DepartmentID { get; set; }
    public int InstitutionID { get; set; }
    public string FirstName { get; set; }
    public string Surname { get; set; }
    public decimal ContractedHours { get; set; }

}

这个 View 有一个通过覆盖编码的复合键，如下所示:

modelBuilder.Entity<RosterLineView>()
            .HasKey(c => new { c.RosterLineID, c.DateEntry, c.EmployeeID, c.PositionID });

我已经使用调用中提供的示例数据检查了存储过程结果，并且可以使用这些键确认数据中没有重复项，如果有人认为存在问题，我很乐意 PM 示例数据集，但我已经尝试过了对程序也有明确的要求。

关于为什么它仍然可能给出此错误的任何想法，这使得它听起来像是返回了重复的数据？虽然我假设这是给出异常的原因，但它是否可能与数据集无关，而是与 View 声明和复合键规范或其他什么有关？我最初确实在我删除的 View 中有一个错误的 [Key] 注释，是否有可能即使它被删除也会以某种方式缓存？尝试调试 4 小时后的一些随机想法。谢谢大家，感谢您的帮助。在接下来的几个小时内远离 PC，但我会在之后回复任何评论。

编辑 - 这是请求的存储过程

ALTER PROCEDURE [dbo].[GetOneWeekRosters] 
    -- Add the parameters for the stored procedure here
    @Date varchar(20), 
    @DepartmentID int,
    @RosterID int,
    @RosterWeekID int,
    @Active bit
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT distinct n.EmployeeID, n.FirstName, n.Surname, n.DepartmentID, n.InstitutionID, 
    n.ContractedHours, n.ReportingOrder, n.RoleName, 
    ISNULL(RosterLines.Notes, 'no notes') as Notes, ISNULL(RosterLines.ActivityTypeID, 8) as ActivityTypeID, 
    ISNULL(ActivityTypes.ActivityTypeShort, 'NW') as ActivityTypeShort, 
    ISNULL(RosterLines.RosterHourValue,0) as RosterHourValue, 
    ISNULL(RosterLines.RosterLineID, 0) as RosterLineID, ISNULL(RosterLines.PositionID, 1) as PositionID, 
    ISNULL(RosterLines.RosterShiftStart, m.DateDim) as ShiftStart, 
    RosterAreas.RosterAreaName, RosterShifts.RosterShiftName, 
    @RosterWeekID as RosterWeekID, @RosterID as RosterID,
    ISNULL(RosterLines.RosterShiftFinish, m.DateDim) as ShiftFinish, ISNULL(RosterLines.RosterShiftID, 0) as RosterShiftID, 
    ISNULL(RosterLines.RosterLineID, 0) as RosterLineID, RosterLines.RosterAreaID, 
    m.DateDim as DateEntry from 
    (select DateDimension.DateDim, DateDimension.DayOfWeek_ShortName, DateDimension.Calendar_DayOfMonth 
    from DateDimension where DateDimension.DateDim between Convert(datetime,@Date,103) and (Convert(datetime,@Date,103) + 6)) m 
    cross join 
    (select Employees.EmployeeID, Employees.FirstName, Employees.Surname, Employees.EmploymentTypeID, Employees.ContractedHours, 
    Employees.RoleName, Employees.Active, EmploymentTypes.EmploymentTypeName, EmploymentTypes.ReportingOrder, 
    EmpToDepts.DepartmentID, EmpToDepts.InstitutionID
    from Employees left join EmploymentTypes on Employees.EmploymentTypeID = EmploymentTypes.EmploymentTypeID 
    left join EmpToDepts on Employees.EmployeeID = EmpToDepts.EmployeeID 
    where EmpToDepts.DepartmentID = @DepartmentID and Employees.Active = @Active) n 
    left join RosterLines on n.EmployeeID = RosterLines.EmployeeID and n.DepartmentID = RosterLines.DepartmentID 
    and m.DateDim = RosterLines.DateEntry left join ActivityTypes on RosterLines.ActivityTypeID = ActivityTypes.ActivityTypeID 
    left join RosterAreas on RosterLines.RosterAreaID = RosterAreas.RosterAreaID 
    left join RosterShifts on RosterLines.RosterShiftID = RosterShifts.RosterShiftID
    order by n.DepartmentID, n.ReportingOrder, n.Surname, n.FirstName
END

Answer 1

有同样的问题，我注意到我对多个列使用了相同的列名。我检查了你的程序，似乎 RosterLineID 是重复的，这不知何故混淆了 EF。每当出现此错误时，请务必检查列名。希望这有助于...