LINQ Query modification

jsymons · October 2nd, 2009, 08:15 AM

Imar,

I was just wondering if there was a way to modify the LINQ query on page 440 to exclude reviews that haven't been authorized from showing up in the AllByGenre.aspx page.

I've already modified the query to exclude genres that have no reviews in which you taught me in a previous post as follows:

Code:

Dim allGenres = From genre In myDataContext.Genres _
                     Order By genre.Name _
                     Where genre.Reviews.Count > 0 _
                     Select New With {genre.Name, genre.Reviews}

I'm thinking, after the LINQ query executes that I would need to loop through the Reviews collection for each genre and delete it's unauthorized reviews.

Any thoughts or clarification would be greatly appreciated.

Thanks,

John

Imar · October 2nd, 2009, 04:23 PM

Hi John,

It looks like a simple question, but it's actually quite tricky. There's no easy way to filter the collection in this query. If you use Any (or All) you basically return the Genre if it has at least one authorized review. However, that applies to most genres and the query would still return the unauthorized reviews as well.

The trick here is to create a group like this:

Code:

Dim allGenres = From review In myDataContext.Reviews _
Where review.Authorized = True _
Group review By Genre = review.Genre Into Group _
Select Genre.Name, Reviews = Group

This creates a structure like this:

Code:

Genre 1
  Review 1
  Review 2
Genre 2
  Review 1
  Review 2

The first two lines are simple and probably look familiar. The third line basically says:

"group the Reviews by their Genre and give each unique genre a collection of reviews"

The keywords Group, By, and Into are all reserved words and are used for the grouping and to assign the selected Reviews to Group. In the final line of code you can query the name of the Genre and assign a name (Reviews in this case) to the returned group.

A bit confusing at first, but it does the trick.

You can find more Group examples here:

http://msdn.microsoft.com/en-us/vbasic/bb737926.aspx

More info:

http://www.hookedonlinq.com/GroupByOperator.ashx
http://msdn.microsoft.com/en-us/libr...e.groupby.aspx

Cheers,

Imar

jsymons · October 5th, 2009, 08:21 AM

Imar,

Thanks for the quick response. Although as you said "it's a bit confusing at first", it did the trick even know I don't fully understand how. I will be checking out those hyperlinks to educate myself further on creating groups in LINQ.

Thanks,

John

Imar · October 5th, 2009, 08:41 AM

Maybe I should have said it's confuing at first and at second? ;-)

Imar

bigyawn_hippo · October 11th, 2009, 08:58 AM

Hi Imar,

I was wondering if it is possible to make the review title that appears in the AllGenre.aspx into a Hyperlink, and allow the user to click through to the review.

Is this possible as the data is presented in a repeater?

Would I follow the same sort of method as using a data source and grid view?

Thanks in advance.

Hippo

Imar · October 11th, 2009, 09:05 AM

Yeah, that's not too hard.

You can make the BulletedList clickable and redirect at the server (http://msdn.microsoft.com/en-us/libr...letedlist.aspx) or, much easier, replace the BulletedList with a Repeater control. Provide <ul> and </ul> elements in the Header and Foorter Templates and add a <li>a href... /></li> to the item template. The link could look like this:

Code:

 
<li>
  <asp:HyperLink id="lnkReview" runat="server" Text='<%# Eval("Title") %>'
        NavigateUrl='<%# "SomePage.aspx?Id=" + Eval("Id").ToString() %>' />
</li>

I just typed the code in this text editor directly so it may not work directly, but I hope you get the idea,

Cheers,

Imar

bigyawn_hippo · October 11th, 2009, 09:46 AM

Hi Imar,

Thanks for you speedy response, do you ever rest or sleep ?

I understand your answer in theory but have a few more question.

Would I have to create a seperate Repeater to the one already used? i.e. a new repeather within a repeater.

Would the UL tags need any addtional code added to them or would I be able just to place the tags on the header and footer like you suggested.

Thanks in Advance.

Regards

Imar · October 11th, 2009, 09:52 AM

Sleep is for beginners.... ;-)

Anyway: yes, you need to create a nested Repeater. The outer Repeater displays the individual genres while the inner one lists the Reviews. This is similar to the current page, but the BulletedList is repalced with a Repeater. Here's the full code you need:

Code:

 
 <asp:Repeater ID="Repeater1" runat="server">
  <ItemTemplate>
   <h3>
    <asp:Literal ID="Literal1" runat="server" Text='<%# Eval("Name") %>'></asp:Literal></h3>
   <asp:Repeater ID="repReviews" runat="server" DataSource='<%# Eval("Reviews") %>'>
    <HeaderTemplate>
     <ul>
    </HeaderTemplate>
    <FooterTemplate>
     </ul>
    </FooterTemplate>
    <ItemTemplate>
     <li>
      <asp:HyperLink ID="lnkReview" runat="server" Text='<%# Eval("Title") %>' 
              NavigateUrl='<%# "ViewDetails.aspx?ReviewId=" + Eval("Id").ToString() %>' />
     </li>
    </ItemTemplate>
   </asp:Repeater>
  </ItemTemplate>
 </asp:Repeater>

Just drop this code inside the ContentPlaceHolder of AllByGenre.asp and it should work....

Cheers,

Imar

bigyawn_hippo · October 13th, 2009, 03:55 PM

Hi Imar,

Sorry, but more question. I hope there is no question limit per person as I must be reaching my limit

I have restructured my database so that there is a comment table and I ve added it to my database diagram etc.

By using the above post query string that fills a details view how would I also get it to populate a List view with data from the comments table.

The comment table has a foreign key called ReivewID that links to the Review table Id.

When I try a where statement that does ReviewID==ReviewID select.value and binds to a control i.e. details view I get an error message. I m guessing this is because there are many values to select from and I either need to extract the value form the control or tell the where statment exactly were the desired value is.

But as usual I am unsure how to do this :(

Thanks in advance

Hippo

Imar · October 13th, 2009, 04:01 PM

Hi there,

Not sure I understand what you're saying or asking. Can you provide more info? E.g. a table description, how the tables are linked in LINQ to SQL and maybe the relevant pieces of code?

Cheers,

Imar