Grouping in Linq to SQL vs SQL

I wanted to put up a few examples of SQL vs Linq to SQL for my future reference, since we’re using it in one of our social media projects for artists. I’m pretty handy at SQL, but it doesn’t translate exactly to Linq to SQL.

Example 1

Assume you have a view called vev_bws_mediaVoteDownloadHistory that votes and downloads for different media by date. So it contains columns like mediaId, artistId, activityDate, votes, and downloads.

If we wanted to query this view to get the total number of votes and downloads for a given media, you could use SQL like this

SELECT
    votes = SUM(votes),
    downloads = SUM(downloads)
FROM vev_bws_mediaVoteDownloadHistory
WHERE mediaId = 12345

Or use Linq to SQL like this

veDataContext dc = new veDataContext();

var totals = (from v in dc.vev_bws_mediaVoteDownloadHistories
              where v.mediaId.Equals(12345)
              group v by v.mediaId into h
              select new
              {
                  votes = h.Sum(x => x.votes),
                  downloads = h.Sum(x => x.downloads)
              }).FirstOrDefault();

if (totals == null) then return; // no totals

Response.Write(totals.downloads);
Response.Write(totals.votes);

Example 2

So the first example was jsut getting a few simple sums. Here’s a bit more complex example.

Assume you have a view called vev_bws_userMediaHistory that logs votes for a given artist and his/her media. The view fields like votes, artistName, mediaName, userId (the user who cast the vote), and voteDate (a datetime column storing the date & time of a vote).

So since voteDate contains dates and times, I want to display all the votes grouped by media, artist, and date (not time). I could use SQL like this:

SELECT
    votesperday = SUM(votes),
    date = CAST(FLOOR(CAST(voteDate as FLOAT)) AS DATETIME),
    mediaName,
    artistName
FROM vev_bws_userMediaHistory
WHERE userId=1
GROUP BY
    CAST(FLOOR(CAST(voteDate as FLOAT)) AS DATETIME),
    mediaName,
    artistName
ORDER BY CAST(FLOOR(CAST(voteDate as FLOAT)) AS DATETIME) DESC

Note that the CAST/FLOOR/CAST trick is used to trim off the time from a datetime, leaving just the date portion. This allows us to sum up votes cast throughout the day into a “votes per day” value.

To do this in Linq to SQL I would use this:

veDataContext dc = new veDataContext();

var votes = (from v in dc.vev_bws_userMediaHistories
             where v.userId.Equals(1)
             group v by new
             {
                 v.voteDate.Date,
                 v.artistName,
                 v.mediaName
             }
             into h
             orderby h.Key.Date descending
             select new
             {
                 date = h.Key.Date,
                 artistName = h.Key.artistName,
                 mediaName = h.Key.mediaName,
                 votesperday = h.Sum(x => x.votes)
             });

The above Linq select statement allows me to reference the artistName in my ListView via <%# Eval(“artistName”) %>. If I had instead used this select statement

select new
{
    h.Key,
    votesperday = h.Sum(x => x.votes)
}

I would need to use something like <%# Eval(“Key.artistName”) %> instead.