Return results from stored procedure not mapped to table

Guys,

I have the following stored procedure:

use mydatabase
if OBJECT_ID ( 'dbo.sp_get_stops_on_route', 'P' ) is not null 
    drop procedure dbo.sp_get_stops_on_route;
go

create procedure dbo.sp_get_stops_on_route 
    @routeId varchar(10)
as
    ;with cte  as (select distinct stopid from agentstopschedules where routeid=@routeId)  
    select *, stuff((select ',' + cast(arrival as varchar(5)) from agentstopschedules where stopid=cte.stopid group by arrival for xml path('')),1,1,'') as tag
    from cte
    order by stopid
go

When invoked using the following code:

  using (IUnitOfWork unitOfWork = _lbsContext.CreateUnitOfWork())
            {

                var cmd = unitOfWork.Context.DataProviderObjectFactory.CreateCommand();
                cmd.CommandText = "exec dbo.sp_get_stops_on_route @routeId ";

                cmd.Parameters.Add(new SqlParameter("routeId", routeId));
                schedules = unitOfWork.FindBySql<AgentStopSchedule>(cmd);
            }

Lightspeed returns an array with the correct number of rows but each element in the array is null, possibly because the return type is not mapped to a specific table. Is there a way around this?

The AgentStopSchedule type doesn't need to be mapped to a specific table, but the field names of AgentStopSchedule do need to match up with the columns returned from the procedure. For example, if the AgentStopSchedule has fields int LocationId and string Tag then the stored procedure must return columns named Id, LocationId and Tag.

From the fact that you are getting nulls I would guess that you are not returning an Id (primary key) from your sproc, and indeed looking at your sproc I think it returns only StopId and Tag columns. If the StopId is meant to be the primary key then you can either rename it to Id within the sproc (select stopid as id) or map the AgentStopSchedule ID field to the StopId column in LightSpeed (using Identity Column Name in the designer or the TableAttribute in code).