LINQ .FromSQL error InvalidOperationException: Sequence contains more than one matching element

Question

I have this exception being raised when requesting data from a stored procedure using Entity Framework and 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)

the relevant part of the stack is here (apologies its on two lines but the stack overflow editor won't let me break it without it leaving the code block formatting):

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()

The error is originating with a database call using .FromSql as part of a linq query. I'm calling the data like so - and even though it has a .ToListAsync() the stack trace indicates the linq statement still calls .SingleOrDefault() on each line item returned - I'm assuming this is intended:

[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;
    }

The dataset being returned is a flat array of lines represented by this 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; }

}

This view has a composite key coded through overrides as below:

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

I've examined the stored procedure results using sample data provided in the call and can confirm there's no duplicates in the data using these keys, happy to PM the sample dataset if anyone thinks the issue is there, but I've tried with a distinct call on the procedure too.

Any ideas as to why it still might be giving this error which makes it sounds like there's duplicate data being returned?? While I'm assuming that's the reason given the exception, is it possible its not related to the dataset but the view declaration and composite key specification or something? I initially did have an erroneous [Key] annotation in the view that I removed, is it possible this is cached somehow even though its removed? Just some random ideas after trying to debug for 4 hours. Thanks all, appreciate your help. Away from PC for next couple of hours but I'll get back to any comments after.

EDIT - here's the stored procedure as requested

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

Had the same problem, I noticed that I was using the same column name for more than one column. I checked your procedure and seems like RosterLineID is duplicated, which somehow confuses EF. Always check column names whenever you get this error. Hope this helps...

Answer 2

Had the same problem; was running EF Core 2.1 calling a stored procedure. The problem for me was in the stored procedure. I had SELECT * with an INNER JOIN, which was returning the results of two tables. I changed my stored procedure to only return the table that I needed and it resolved the issue.

Answer 3

Check the result columns for any duplicate. In my case that was the reason. Stored procedure will work if you select same column name twice, but FromSql will throw this exception.