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