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

i have exception being raised when requesting data stored procedure using entity framework , linq.

an unhandled exception occurred while processing request.  invalidoperationexception: sequence contains more 1 matching element system.linq.enumerable.singleordefault<tsource>(ienumerable<tsource> source,  func<tsource, bool> predicate) 

the relevant part of stack here (apologies on 2 lines stack overflow editor won't let me break without leaving 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 originating database call using .fromsql part of linq query. i'm calling data - , though has .tolistasync() stack trace indicates linq statement still calls .singleordefault() on each line item returned - i'm assuming 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 flat array of lines represented 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 composite key coded through overrides below:

modelbuilder.entity<rosterlineview>()             .haskey(c => new { c.rosterlineid, c.dateentry, c.employeeid, c.positionid }); 

i've examined stored procedure results using sample data provided in call , can confirm there's no duplicates in data using these keys, happy pm sample dataset if thinks issue there, i've tried distinct call on procedure too.

any ideas why still might giving error makes sounds there's duplicate data being returned?? while i'm assuming that's reason given exception, possible not related dataset view declaration , composite key specification or something? did have erroneous [key] annotation in view removed, possible cached somehow though removed? random ideas after trying debug 4 hours. all, appreciate help. away pc next couple of hours i'll comments after.

edit - here's stored procedure requested

alter procedure [dbo].[getoneweekrosters]      -- add parameters stored procedure here     @date varchar(20),      @departmentid int,     @rosterid int,     @rosterweekid int,     @active bit begin     -- set nocount on added prevent result sets     -- interfering select statements.     set nocount on;      -- insert statements 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') notes, isnull(rosterlines.activitytypeid, 8) activitytypeid,      isnull(activitytypes.activitytypeshort, 'nw') activitytypeshort,      isnull(rosterlines.rosterhourvalue,0) rosterhourvalue,      isnull(rosterlines.rosterlineid, 0) rosterlineid, isnull(rosterlines.positionid, 1) positionid,      isnull(rosterlines.rostershiftstart, m.datedim) shiftstart,      rosterareas.rosterareaname, rostershifts.rostershiftname,      @rosterweekid rosterweekid, @rosterid rosterid,     isnull(rosterlines.rostershiftfinish, m.datedim) shiftfinish, isnull(rosterlines.rostershiftid, 0) rostershiftid,      isnull(rosterlines.rosterlineid, 0) rosterlineid, rosterlines.rosterareaid,      m.datedim dateentry      (select datedimension.datedim, datedimension.dayofweek_shortname, datedimension.calendar_dayofmonth      datedimension datedimension.datedim between convert(datetime,@date,103) , (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     employees left join employmenttypes on employees.employmenttypeid = employmenttypes.employmenttypeid      left join emptodepts on employees.employeeid = emptodepts.employeeid      emptodepts.departmentid = @departmentid , employees.active = @active) n      left join rosterlines on n.employeeid = rosterlines.employeeid , n.departmentid = rosterlines.departmentid      , 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 n.departmentid, n.reportingorder, n.surname, n.firstname end 

this not of answer, got dataset return using ado.net fetch stored procedure instead. sproc work, believe real answer sort of issue way .net core 1.1 returns datasets using linq, , possibly in way evaluates stored procedure results. haven't got time prove it, moment have data , good. takes 50 lines of code produce set manually using sqlconnection, sqldatareader , sqlcommand. created list , iteratively added data using sqldatareader in while loop.

here's pain in glory:

 sqlconnection sqlconn = new sqlconnection("xxxxxxxxx");         sqlconn.open();         sqlcommand sqlcomm = new sqlcommand("getoneweekrosters", sqlconn);         sqlparameter = new sqlparameter("@date", mysearch.dateselected);         sqlparameter b = new sqlparameter("@departmentid", mysearch.departmentid);         sqlparameter c = new sqlparameter("@rosterid", mysearch.rosterid);         sqlparameter d = new sqlparameter("@rosterweekid", mysearch.rosterweekid);         sqlparameter e = new sqlparameter("@active", true);         sqlcomm.parameters.add(a);         sqlcomm.parameters.add(b);         sqlcomm.parameters.add(c);         sqlcomm.parameters.add(d);         sqlcomm.parameters.add(e);         sqlcomm.commandtype = commandtype.storedprocedure;         var rdr = await sqlcomm.executereaderasync();         var mylist = new list<rosterlineview>();         while (rdr.read()) {             int rosterid = int.parse(rdr["rosterid"].tostring());             int rosterweekid = int.parse(rdr["rosterweekid"].tostring());             string firstname = rdr["firstname"].tostring();             string surname = rdr["surname"].tostring();             int employeeid = int.parse(rdr["employeeid"].tostring());             int departmentid = int.parse(rdr["departmentid"].tostring());             int institutionid = int.parse(rdr["institutionid"].tostring());             decimal contractedhours = decimal.parse(rdr["contractedhours"].tostring());             int reportingorder = int.parse(rdr["reportingorder"].tostring());             string notes = rdr["notes"].tostring();             int activitytypeid = int.parse(rdr["activitytypeid"].tostring());             string activitytypeshort = rdr["activitytypeshort"].tostring();             decimal rosterhourvalue = decimal.parse(rdr["rosterhourvalue"].tostring());             int rosterlineid = int.parse(rdr["rosterlineid"].tostring());             int positionid = int.parse(rdr["positionid"].tostring());             datetimeoffset shiftstart = datetimeoffset.parse(rdr["shiftstart"].tostring());             string rosterareaname = rdr["rosterareaname"].tostring();             string rostershiftname = rdr["rostershiftname"].tostring();             datetimeoffset shiftfinish = datetimeoffset.parse(rdr["shiftfinish"].tostring());             int rostershiftid = int.parse(rdr["rostershiftid"].tostring());             int rosterareaid = int.parse(rdr["rosterareaid"].tostring());             datetime datedim = datetime.parse(rdr["datedim"].tostring());              var myitem = new rosterlineview();             myitem.rosterid = rosterid;             myitem.rosterlineid = rosterlineid;             myitem.rostershiftfinish = shiftfinish;             myitem.rostershiftstart = shiftstart;             myitem.activitytypeid = activitytypeid;             myitem.activitytypeshort = activitytypeshort;             myitem.contractedhours = contractedhours;             myitem.datedim = datedim;             myitem.departmentid = departmentid;             myitem.institutionid = institutionid;             myitem.notes = notes;             myitem.positionid = positionid;             myitem.rosterareaid = rosterareaid;             myitem.rosterareaname = rosterareaname;             myitem.rostershiftid = rostershiftid;             myitem.rostershiftname = rostershiftname;             myitem.surname = surname;             myitem.firstname = firstname;             myitem.rosterhourvalue = rosterhourvalue;             myitem.rosterweekid = rosterweekid;             mylist.add(myitem);          }          sqlconn.close();         rdr.dispose();         return mylist;      } 

moving on.