JOIN with LinqtoSql, only select TOP(x) on joined table?

Question

I've had a look around on StackOverlow but haven't been able to find a definitive answer on this.

Below I have a code snippet of what I currently 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 then 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 to only take 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?

Answer 1

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 the take taken place on the select. Thanks for your help everyone. Any suggestions on how to write this "better" would be appreciated.

Answer 2

This looks like it might be the tweak you need. It's from a very helpful LINQ sample site.

This sample prints the customer ID, order ID, and order date for the first three orders from customers in Washington. The sample uses Take to limit the sequence generated by the query expression to the first three of the 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

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