Skip to content

Instantly share code, notes, and snippets.

@lancscoder

lancscoder/Dapper.sql

Created March 18, 2012 11:41
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save lancscoder/2070777 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save lancscoder/2070777 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. lancscoder created this gist Mar 18, 2012.
    13 changes: 13 additions & 0 deletions Dapper.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,13 @@
    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
    59 changes: 59 additions & 0 deletions Entity Framework.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,59 @@
    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