SQL to Linq translation
-
Hi, I'm new to linq and am having trouble translating the following SQL (2008) code to Linq (VB .Net) any help would be greatly appreciated. To create the sample data (the actual table has lots of other columns):
CREATE TABLE [dbo].[Schedule](
[ScheduleGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ScheduleStart] [smalldatetime] NOT NULL,
[ScheduleEnd] [smalldatetime] NOT NULL
)
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-07 09:00:00','2009-09-07 10:00:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-08 09:00:00','2009-09-08 10:00:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-09 09:30:00','2009-09-09 11:30:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-07 09:00:00','2009-09-07 10:00:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-10 12:30:00','2009-09-10 14:30:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-08 09:00:00','2009-09-08 10:00:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-10 09:30:00','2009-09-10 11:30:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-09 09:30:00','2009-09-09 11:30:00')-- This SQL code produces the required result
SELECT
[1] As Sunday
,[2] AS Monday
,[3] As Tuesday
,[4] AS Wednesday
,[5] AS Thursday
,[6] As Friday
,[7] As Saturday
FROM
(
SELECT
DATEPART(weekday, ScheduleStart) AS SWeekday
,ROW_NUMBER()
OVER (PARTITION BY DATEADD(d, DATEDIFF(d, 0, ScheduleStart), 0) ORDER BY ScheduleStart, ScheduleGUID) AS RowNum
,CONVERT(varchar(5), ScheduleStart, 108) + ' to ' + CONVERT(varchar(5), ScheduleEnd, 108) AS Period
FROM Schedule
) D
PIVOT
(
MIN(Period)
FOR SWeekDay IN ([1], [2], [3], [4], [5], [6], [7])
)
AS P--************************************* So the problem is how to translate the SQL code into VB LINQ code :confused: The idea is that the data rows are flattened so as I get a compacted list of schdules for each day. (There can be multiple identical schedules per day) Thanks in advance G
-
Hi, I'm new to linq and am having trouble translating the following SQL (2008) code to Linq (VB .Net) any help would be greatly appreciated. To create the sample data (the actual table has lots of other columns):
CREATE TABLE [dbo].[Schedule](
[ScheduleGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ScheduleStart] [smalldatetime] NOT NULL,
[ScheduleEnd] [smalldatetime] NOT NULL
)
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-07 09:00:00','2009-09-07 10:00:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-08 09:00:00','2009-09-08 10:00:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-09 09:30:00','2009-09-09 11:30:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-07 09:00:00','2009-09-07 10:00:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-10 12:30:00','2009-09-10 14:30:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-08 09:00:00','2009-09-08 10:00:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-10 09:30:00','2009-09-10 11:30:00')
Insert Schedule (ScheduleStart, ScheduleEnd) Values('2009-09-09 09:30:00','2009-09-09 11:30:00')-- This SQL code produces the required result
SELECT
[1] As Sunday
,[2] AS Monday
,[3] As Tuesday
,[4] AS Wednesday
,[5] AS Thursday
,[6] As Friday
,[7] As Saturday
FROM
(
SELECT
DATEPART(weekday, ScheduleStart) AS SWeekday
,ROW_NUMBER()
OVER (PARTITION BY DATEADD(d, DATEDIFF(d, 0, ScheduleStart), 0) ORDER BY ScheduleStart, ScheduleGUID) AS RowNum
,CONVERT(varchar(5), ScheduleStart, 108) + ' to ' + CONVERT(varchar(5), ScheduleEnd, 108) AS Period
FROM Schedule
) D
PIVOT
(
MIN(Period)
FOR SWeekDay IN ([1], [2], [3], [4], [5], [6], [7])
)
AS P--************************************* So the problem is how to translate the SQL code into VB LINQ code :confused: The idea is that the data rows are flattened so as I get a compacted list of schdules for each day. (There can be multiple identical schedules per day) Thanks in advance G
If you already have a stored proc that does what is required then call it from your Linq class. AFAIK there is no Linq syntax for doing thing like PIVOT. You can construct an anonymous object from Linq statements, but that may not be sufficient for your usage.
I know the language. I've read a book. - _Madmatt
-
If you already have a stored proc that does what is required then call it from your Linq class. AFAIK there is no Linq syntax for doing thing like PIVOT. You can construct an anonymous object from Linq statements, but that may not be sufficient for your usage.
I know the language. I've read a book. - _Madmatt
Thanks for the reply I can't use the stored procedure since the application can use an online Db, or may be used (with limited functionality) as an 'offline' application via SQL CE... so I have to find some way of duplicating the functionality.