This is surprisingly difficult, but there’s a relatively robust way to do it:
Create your stored procedure
Create a view that mimics the return signature of the stored procedure
In the model (.dbml), import both the stored procedure and view
Set the “Return Type” property of the stored procedure Data Function to the view type
In code, use the .ToList() method of the stored procedure function of the model and cast it to an IEnumerable of the view type, for example:
DBDataContext db = new DBDataContext(); IEnumerable<vwUspEventCalendarResultSet> CalendarResults = db.uspEventCalendar(CustomerId, StartDate, EndDate, DataStartDate, DataEndDate).ToList();
One thing to be careful of is if your stored procedure might return null values in integer key columns you may have to ISNULL them to 0 or some other meaningful value (in the stored procedure) since LINQ to SQL does not pick up a nullable int type from the view signature.
Here are the code bits to back this up…
View:
SELECT dbo.AllDates.TheDate, dbo.Events.Id, dbo.Events.Name, dbo.Events.Description, dbo.Events.Url, dbo.Events.CustomerId, dbo.Events.TeamId, dbo.Events.ShowCityId, dbo.Events.EventStartDate, dbo.Events.EventEndDate, dbo.Events.EventMoveInDate, dbo.Events.EventMoveOutDate, dbo.Events.EventAddressId, dbo.Events.InventoryPullDate, dbo.Events.InventoryShipDate, dbo.Events.InventoryReturnDate, dbo.Events.InventoryAvailableDate, dbo.Events.EventShippingInfoId, dbo.Events.EventReturnAddressId, dbo.Events.EventAttendance, dbo.Events.BoothNumber, dbo.Events.BoothSize, dbo.Events.CeilingHeight, dbo.Events.BoothAttendance, dbo.Events.LeadsCaptured, dbo.Events.CreatedBy, dbo.Events.CreatedTime, dbo.Events.ModifiedBy, dbo.Events.ModifiedTime, dbo.Events.Deleted, dbo.Events.Active, dbo.Events.Notes FROM dbo.Events INNER JOIN dbo.AllDates ON dbo.Events.EventStartDate = dbo.AllDates.TheDate
Stored Procedure:
ALTER PROCEDURE [dbo].[uspEventCalendar] -- Add the parameters for the stored procedure here @CustomerId int = 0, @StartDate datetime = null, @EndDate datetime = null, @DataStartDate datetime = null, @DataEndDate datetime = null AS BEGIN -- default to "this month" if @StartDate is null SET @StartDate = cast(cast(DatePart(month,getdate()) as varchar) + '/1/' + cast (DatePart(year,getdate()) as varchar) as datetime) if @EndDate is null SET @EndDate = dateadd(day,-1,dateadd(month,1,@StartDate)) if @DataStartDate is null SET @DataStartDate = @StartDate if @DataEndDate is null SET @DataEndDate = @StartDate -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. --SET NOCOUNT ON; --SELECT @StartDate,@EndDate -- Insert statements for procedure here SELECT CalendarDates.TheDate, ISNULL(EventCalendarEntries.Id,0) as Id, EventCalendarEntries.Name, EventCalendarEntries.Description, EventCalendarEntries.Url, EventCalendarEntries.CustomerId, EventCalendarEntries.TeamId, EventCalendarEntries.ShowCityId, EventCalendarEntries.EventStartDate, EventCalendarEntries.EventEndDate, EventCalendarEntries.EventMoveInDate, EventCalendarEntries.EventMoveOutDate, EventCalendarEntries.EventAddressId, EventCalendarEntries.InventoryPullDate, EventCalendarEntries.InventoryShipDate, EventCalendarEntries.InventoryReturnDate, EventCalendarEntries.InventoryAvailableDate, EventCalendarEntries.EventShippingInfoId, EventCalendarEntries.EventReturnAddressId, EventCalendarEntries.EventAttendance, EventCalendarEntries.BoothNumber, EventCalendarEntries.BoothSize, EventCalendarEntries.CeilingHeight, EventCalendarEntries.BoothAttendance, EventCalendarEntries.LeadsCaptured, EventCalendarEntries.CreatedBy, EventCalendarEntries.CreatedTime, EventCalendarEntries.ModifiedBy, EventCalendarEntries.ModifiedTime, EventCalendarEntries.Deleted, EventCalendarEntries.Active, EventCalendarEntries.Notes FROM (SELECT thedate from alldates where thedate BETWEEN @StartDate AND @EndDate) as CalendarDates LEFT OUTER JOIN (SELECT EventDates.TheDate, EventsInRange.Id, EventsInRange.Name, EventsInRange.Description, EventsInRange.Url, EventsInRange.CustomerId, EventsInRange.TeamId, EventsInRange.ShowCityId, EventsInRange.EventStartDate, EventsInRange.EventEndDate, EventsInRange.EventMoveInDate, EventsInRange.EventMoveOutDate, EventsInRange.EventAddressId, EventsInRange.InventoryPullDate, EventsInRange.InventoryShipDate, EventsInRange.InventoryReturnDate, EventsInRange.InventoryAvailableDate, EventsInRange.EventShippingInfoId, EventsInRange.EventReturnAddressId, EventsInRange.EventAttendance, EventsInRange.BoothNumber, EventsInRange.BoothSize, EventsInRange.CeilingHeight, EventsInRange.BoothAttendance, EventsInRange.LeadsCaptured, EventsInRange.CreatedBy, EventsInRange.CreatedTime, EventsInRange.ModifiedBy, EventsInRange.ModifiedTime, EventsInRange.Deleted, EventsInRange.Active, EventsInRange.Notes FROM (SELECT Id, Name, Description, Url, CustomerId, TeamId, ShowCityId, EventStartDate, EventEndDate, EventMoveInDate, EventMoveOutDate, EventAddressId, InventoryPullDate, InventoryShipDate, InventoryReturnDate, InventoryAvailableDate, EventShippingInfoId, EventReturnAddressId, EventAttendance, BoothNumber, BoothSize, CeilingHeight, BoothAttendance, LeadsCaptured, CreatedBy, CreatedTime, ModifiedBy, ModifiedTime, Deleted, Active, Notes FROM Events WHERE (EventStartDate < = @DataEndDate) AND (EventEndDate >= @DataStartDate)) AS EventsInRange CROSS JOIN (SELECT TheDate FROM AllDates WHERE (TheDate BETWEEN @StartDate AND @EndDate)) AS EventDates WHERE (EventDates.TheDate BETWEEN EventsInRange.EventStartDate AND EventsInRange.EventEndDate)) EventCalendarEntries ON EventCalendarEntries.thedate = CalendarDates.thedate END