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.