JOIN with LinqtoSql, only select TOP (x) on the concatenated table?
I looked at StackOverlow but could not find a definitive answer to this question.
Below I have a piece of code of what I have and I will explain what I am trying to achieve.
Table<Gallery> galleries = pdc.GetTable<Gallery>();
Table<GalleryImage> images = pdc.GetTable<GalleryImage>();
Table<Comment> comments = pdc.GetTable<Comment>();
var query = from gallery in galleries
join image in images on gallery.id equals image.galleryid into joinedimages
join comment in comments on gallery.id equals comment.galleryid into joinedcomments
select gallery;
gallst.DataSource = query;
gallst.DataBind();
From the above, I have the following repeater:
<asp:Repeater ID="gallst" runat="server" EnableViewState="false">
<HeaderTemplate>
<div id="gallery">
</HeaderTemplate>
<ItemTemplate>
<div class="item">
<h2><%# DataBinder.Eval(Container.DataItem, "name") %> @ <%# DataBinder.Eval(Container.DataItem, "wheretaken") %></h2>
<ul class="images">
<asp:Repeater ID="galimgs" runat="server" EnableViewState="false" DataSource='<%# Eval("GalleryImages") %>'>
<ItemTemplate>
<li><a href="<%# DataBinder.Eval(Container.DataItem, "image") %>.jpg" title="<%# DataBinder.Eval(((System.Web.UI.WebControls.RepeaterItem)Container.Parent.Parent).DataItem, "name") %>" rel="prettyPhoto[<%# DataBinder.Eval(Container.DataItem, "galleryid")%>]" class="thickbox"><img src="<%# DataBinder.Eval(Container.DataItem, "image") %>_thumb.jpg" /></a></li>
</ItemTemplate>
</asp:Repeater>
</ul>
<div class="comments">
<asp:Repeater ID="galcomments" runat="server" EnableViewState="false" DataSource='<%# Eval("Comments") %>'>
<HeaderTemplate>
<ul>
</HeaderTemplate>
<ItemTemplate>
<li><%# GetUserName(new Guid(Eval("userid").ToString())) %> said: <%#DataBinder.Eval(Container.DataItem, "comment1") %> (<%# DataBinder.Eval(Container.DataItem, "date", "{0:dddd MM, yyyy hh:mm tt}") %>)</li>
</ItemTemplate>
<FooterTemplate>
</ul>
</FooterTemplate>
</asp:Repeater>
<uc:makecomment ID="mcomment" runat="server" PhotoID='<%# DataBinder.Eval(Container.DataItem, "id") %>'></uc:makecomment>
</div>
</div>
</ItemTemplate>
<FooterTemplate>
</div>
</FooterTemplate>
</asp:Repeater>
What I want to do (ideally) is only the first 3 comments for each gallery.
I've tried the following LINQ query with no luck:
var query = from gallery in galleries
join image in images on gallery.id equals image.galleryid into joinedimages
join comment in comments.Take(3) on gallery.id equals comment.galleryid into joinedcomments
select gallery;
Does anyone have any suggestions on how I can achieve this?
a source to share
I managed to get it to work with:
Table<Gallery> galleries = pdc.GetTable<Gallery>();
Table<GalleryImage> images = pdc.GetTable<GalleryImage>();
Table<Comment> comments = pdc.GetTable<Comment>();
var query = from gallery in galleries
join image in images on gallery.id equals image.galleryid into joinedimages
join comment in comments on gallery.id equals comment.galleryid into joinedcomments
select new
{
name = gallery.name,
wheretaken = gallery.wheretaken,
id = gallery.id,
GalleryImages = joinedimages,
Comments = joinedcomments.Take(3)
};
gallst.DataSource = query;
gallst.DataBind();
With a choice of acceptance. Thanks for your help everyone. Any suggestions on how to write this "better" would be appreciated.
a source to share
It looks like this might be the setting you need. This is from a very helpful figurative LINQ site .
This sample prints the customer ID, order ID, and order date for the first three orders from customers in Washington. The example uses Take to constrain the sequence generated by the query expression to the first three orders.
public void Linq21() {
List<Customer> customers = GetCustomerList();
var first3WAOrders = (
from c in customers
from o in c.Orders
where c.Region == "WA"
select new {c.CustomerID, o.OrderID, o.OrderDate} )
.Take(3);
Console.WriteLine("First 3 orders in WA:");
foreach (var order in first3WAOrders) {
ObjectDumper.Write(order);
}
}
Result
The first 3 orders in WA:
CustomerID=LAZYK OrderID=10482 OrderDate=3/21/1997
CustomerID=LAZYK OrderID=10545 OrderDate=5/22/1997
CustomerID=TRAIH OrderID=10574 OrderDate=6/19/1997
a source to share