Skip to content

Instantly share code, notes, and snippets.

@lancscoder

lancscoder/Dapper.sql

Created March 18, 2012 11:41
Show Gist options
  • Save lancscoder/2070777 to your computer and use it in GitHub Desktop.
Save lancscoder/2070777 to your computer and use it in GitHub Desktop.
Download ZIP
DapperDinner The sql
Raw
Dapper.sql
DECLARE @finish int = 8,
@start int = 1,
@EventDate datetime = '2012-03-13T20:07:25'
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY RsvpCount desc
) AS RowNumber FROM (
SELECT d.*, COUNT(r.DinnerID) AS RsvpCount
FROM Dinners d LEFT OUTER JOIN RSVP r ON d.DinnerID = r.DinnerID
WHERE EventDate >= @EventDate
GROUP BY d.DinnerID, d.Title, d.EventDate, d.Description, d.HostedById, d.HostedBy, d.ContactPhone, d.Address, d.Country, d.Latitude, d.Longitude
) as X ) as Y
WHERE RowNumber BETWEEN @start AND @finish
Raw
Entity Framework.sql
SELECT
[Project2].[DinnerID] AS [DinnerID],
[Project2].[Title] AS [Title],
[Project2].[EventDate] AS [EventDate],
[Project2].[Description] AS [Description],
[Project2].[HostedBy] AS [HostedBy],
[Project2].[ContactPhone] AS [ContactPhone],
[Project2].[Address] AS [Address],
[Project2].[Country] AS [Country],
[Project2].[Latitude] AS [Latitude],
[Project2].[Longitude] AS [Longitude],
[Project2].[HostedById] AS [HostedById],
[Project2].[C1] AS [C1],
[Project2].[RsvpID] AS [RsvpID],
[Project2].[DinnerID1] AS [DinnerID1],
[Project2].[AttendeeName] AS [AttendeeName],
[Project2].[AttendeeNameId] AS [AttendeeNameId]
FROM ( SELECT
[Limit1].[DinnerID] AS [DinnerID],
[Limit1].[Title] AS [Title],
[Limit1].[EventDate] AS [EventDate],
[Limit1].[Description] AS [Description],
[Limit1].[HostedBy] AS [HostedBy],
[Limit1].[ContactPhone] AS [ContactPhone],
[Limit1].[Address] AS [Address],
[Limit1].[Country] AS [Country],
[Limit1].[Latitude] AS [Latitude],
[Limit1].[Longitude] AS [Longitude],
[Limit1].[HostedById] AS [HostedById],
[Extent3].[RsvpID] AS [RsvpID],
[Extent3].[DinnerID] AS [DinnerID1],
[Extent3].[AttendeeName] AS [AttendeeName],
[Extent3].[AttendeeNameId] AS [AttendeeNameId],
CASE WHEN ([Extent3].[RsvpID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
[Limit1].[C1] AS [C2]
FROM (SELECT TOP (8) [Project1].[DinnerID] AS [DinnerID], [Project1].[Title] AS [Title], [Project1].[EventDate] AS [EventDate], [Project1].[Description] AS [Description], [Project1].[HostedBy] AS [HostedBy], [Project1].[ContactPhone] AS [ContactPhone], [Project1].[Address] AS [Address], [Project1].[Country] AS [Country], [Project1].[Latitude] AS [Latitude], [Project1].[Longitude] AS [Longitude], [Project1].[HostedById] AS [HostedById], [Project1].[C1] AS [C1]
FROM ( SELECT
[Extent1].[DinnerID] AS [DinnerID],
[Extent1].[Title] AS [Title],
[Extent1].[EventDate] AS [EventDate],
[Extent1].[Description] AS [Description],
[Extent1].[HostedBy] AS [HostedBy],
[Extent1].[ContactPhone] AS [ContactPhone],
[Extent1].[Address] AS [Address],
[Extent1].[Country] AS [Country],
[Extent1].[Latitude] AS [Latitude],
[Extent1].[Longitude] AS [Longitude],
[Extent1].[HostedById] AS [HostedById],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[RSVP] AS [Extent2]
WHERE [Extent1].[DinnerID] = [Extent2].[DinnerID]) AS [C1]
FROM [dbo].[Dinners] AS [Extent1]
WHERE [Extent1].[EventDate] >= (SysDateTime())
) AS [Project1]
ORDER BY [Project1].[C1] DESC ) AS [Limit1]
LEFT OUTER JOIN [dbo].[RSVP] AS [Extent3] ON [Limit1].[DinnerID] = [Extent3].[DinnerID]
) AS [Project2]
ORDER BY [Project2].[C2] DESC, [Project2].[DinnerID] ASC, [Project2].[C1] ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment