In this post I'm going to show you how to implement a data bind of the top x objects of a database, based on their rating, using LINQ. In this particular context, we want to show the top 3 songs of the database.
Let's consider our 3 tables:
USER:
SONG:
VOTE:
Pre-Condition: Ajax Star Rating Logic implemented (if you want me to talk about this on another post, just email us, and I'll be glad to help out)
- - -
Ok, we're going to use an ASP.NET Repeater to bind the data:
<h2 class="title">Top 3 Songs:<asp:AjaxScriptManager ID="AjaxScriptManager1" runat="server">
</asp:AjaxScriptManager>
</h2><br />
<asp:Label ID="notificationLabel" runat="server" CssClass="titleField">asp:Label><br /><br /><br />
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="songDataSource">
<ItemTemplate>
<div class="titleMain">
<asp:ImageButton OnCommand="redirectToFriend_Click" CommandArgument='<%# Eval("ownerID") %>' ID="ownerPhoto" class="left" runat="server" ImageAlign="Middle" Height="40px" Width="40px" ImageUrl='<%# DataBinder.Eval (Container, "DataItem.photo", "~/photoUploads/{0}") %>' /><br />
<asp:Label ID="firstNameLabel" runat="server" Text='<%# Bind("firstName") %>' CssClass="titleField" /> <br /><br />
"<asp:Label ID="songNameLabel" runat="server" Text='<%# Bind("plainName") %>' CssClass="titleField" />"<br />
<asp:Label ID="Label2" runat="server" Text="Average Rating:" CssClass="titleField" />
<asp:Label ID="ratingLabel" runat="server" Text='<%# Bind("VotesAvg", "{0:#.##}") %>' CssClass="titleField" /><br />
<br />
<asp:MediaPlayer ID="MediaPlayer1" runat="server" Height="40px" Width="320px"
MediaSkinSource="~/AudioGray.xaml" MediaSource='<%# DataBinder.Eval (Container, "DataItem.songName", "~/mp3Uploads/{0}") %>' Volume="1">
</asp:MediaPlayer>
<br /><br /><br /><br />div>
ItemTemplate>
asp:Repeater>
<asp:LinqDataSource ID="songDataSource" runat="server"
ContextTypeName="Ogaite.OgaiteLINQtoSQLDataContext"
Select="new (songID, VotesAvg, plainName, songName, firstName, ownerID, photo)" TableName="Votes"
onselecting="songDataSource_Selecting1" >
asp:LinqDataSource>
Relevant details here, which go within the context of this post, are the following labels: "firstNameLabel" , "songNameLabel" and "ratingLabel" (most important). These are going to give us Who are the 3 amazing composers, which songs made it to the top, and what is their current average rating.
Take a look at the LinqDataSource, and the correspondent Select Statement. We have column names that don't belong to the table "Votes". We even have "VotesAvg" which doesn't belong anywhere..... yet. The FULCRAL part here is the "onselecting="songDataSource_Selecting1".
Here is the correspondent code behind:
protected void songDataSource_Selecting1(object sender, LinqDataSourceSelectEventArgs e)
{
TuganologyLINQtoSQLDataContext dbData = new TuganologyLINQtoSQLDataContext();
var ratings = (from v in dbData.Votes
group v by new { v.songID, v.Song.plainName, v.Song.songName, v.Song.User.firstName, v.Song.User.userID, v.Song.User.photo } into vgroup
select new
{
VotesAvg = vgroup.Average(avg => (double)avg.vote1),
plainName = vgroup.Key.plainName,
SongID = vgroup.Key.songID,
songName = vgroup.Key.songName,
firstName = vgroup.Key.firstName,
photo = vgroup.Key.photo,
ownerID = vgroup.Key.userID
});
var grouped = (from g in ratings
orderby g.VotesAvg descending
select g);
var top = (from t in grouped.Take(3)
orderby t.VotesAvg descending
select t);
e.Result = top;
}
=) Explaining:
In "var ratings" we're essentially getting the songID of the song; its name; songName (this is a guid, which I use to get the path in mediaplayer); firstName (first name of the composer); userID of the composer; and photo of the composer. We're using LINQ "group by", and grouping these columns into "vgroup" (it can be ANY name you want). Notice how we can use "v.Song.User.userID" ! This is very very handy, and we don't need to do any joins, as our Table "Song" has "userID" (foreign key), and by having this, we automatically have access to all columns within "User" Table. The rest follows the same logic. But what does "vgroup" contain?
This will give what "vgroup" will be comprised of:
select new
{
VotesAvg = vgroup.Average(avg => (double)avg.vote1),
plainName = vgroup.Key.plainName,
SongID = vgroup.Key.songID,
songName = vgroup.Key.songName,
firstName = vgroup.Key.firstName,
photo = vgroup.Key.photo,
ownerID = vgroup.Key.userID
});
We're defining "VotesAvg" to be the average of all the instances of "vote1" (LAMBDA Expression here). The rest is simple. Now go back and check our Repeater Select Statement. It makes more sense now right?
Ok, after this, we're declaring "var grouped" to order "ratings" by "VotesAvg" (descending). And then declaring "var top" to take the 3 top songs.
We have implemented all the logic.
Example Output:
Questions?
Thanks for reading!
TR
