Selecting Items with Linq

areed24 · April 1st, 2009, 11:45 AM

In my database, I have two tables, PhotoAlbum and Picture. I would like to create a gallery page that displays the of the album [PhotoAlbum.Name] along with the first picture [Picture.ImageUrl] from that album. I have tried several things but cannot get the results I want. I know that I want to loop through the Album table and get the name for every album and then loop through the Pictures table and find the first picture for each album, the problem is that I don't know how to code it. Please help.

Code:

 Using myDataContext As MyDataContext = New MyDataContext()
            Dim allAlbums = From r In myDataContext.PhotoAlbums _
                            Where r.Id > 0 _
                            Select New With {r.Name}
 
            Repeater1.DataSource = allAlbums
            Repeater1.DataBind()

areed24 · April 2nd, 2009, 07:51 AM

I have gotten this far, which displays the photo album with all the pictures, How can I delimit it to the picture with the lowest id?

Code:

Dim allAlbums = From albums In myDataContext.PhotoAlbums, pics In myDataContext.Pictures _
Where albums.Id = pics.PhotoAlbumId _
OrderBy albums.Id _
SelectNewWith {albums.Name, pics.ImageUrl}

Imar · April 2nd, 2009, 03:01 PM

Hi there,

There are a few ways to do it. The simplest way is probably to sort the list based on its Id (as you have now) and then use FirstOrDefault to get the first item from the result set:

Code:

 
Dim allAlbums = (From albums In myDataContext.PhotoAlbums, pics In myDataContext.Pictures _
Where albums.Id = pics.PhotoAlbumId _
Order By albums.Id _
Select New With {albums.Name, pics.ImageUrl}).FirstOrDefault()

Notice the extra parentheses around the entire query and the call to FirstOrDefault().

Hope this helps,

Imar

areed24 · April 2nd, 2009, 06:06 PM

I tried it with first, but not first or default. It is frustrating when you can think the logic, but can't code it. I tried to implement your suggestion, but now I am getting the following error:

Quote:

An invalid data source is being used for Repeater1. A valid data source must implement either IListSource or IEnumerable.

From what I read, the data source should implement IEnumerable. Any ideas?

Thank you very much for your help.

Imar · April 2nd, 2009, 06:11 PM

Maybe the Repeater isn't the right control for this? You only want a single item, so there's nothing to repeat.... ;-)

Maybe a DetailsView, a FormView or custom controls are better suited for this task?

Imar

areed24 · April 3rd, 2009, 06:43 AM

I do want it to repeat because I want to display each album name and for each album name, the picture in that album.

Imar · April 3rd, 2009, 07:12 AM

Yes, but the outer element is a singular object, isn't it?

E.g. you have a single album entity, with a collection of Pictures, right?

In that case, you should use singular controls for the Album (simple controls like a Label or a TextBox) and a repeating control (like a Repeater or a ListView) for the collection of Pictures.

Cheers,

Imar

areed24 · April 6th, 2009, 10:00 AM

I'm sorry, but I don't follow your explaination. I broke the code down to display exactly what I wanted it to, and this is what I have:

Code:

 Using myDatabaseContext As MyDataContext = New MyDataContext()
            Dim allAlbums = (From albums In myDatabaseContext.PhotoAlbums, pics In myDatabaseContext.Pictures _
                        Where albums.Id = pics.PhotoAlbumId _
                        Order By albums.Id _
                        Select New With {albums.Name, albums.Id, pics.ImageUrl}).FirstOrDefault()
 
            albumName.Text = allAlbums.Name.ToString()
            Image1.ImageUrl = allAlbums.ImageUrl
            HyperLink1.NavigateUrl = "~/Members/Photos/Default.aspx?PhotoAlbumId=" & allAlbums.Id

This code produces the first album and the first picture of that album which is turned into a hyperlink. All I need now is this code to repeat for all the albums in the database. Can I do this using a for loop or do I need to go back to the repeater?

Thank you.

Imar · April 6th, 2009, 02:24 PM

Can't you select the Albums and Pics in one fell swoop, bind the album to single fields and the Pics to a repeater? E.g. something like the following pseudo code:

Code:

 
var album = from a in Context
                  where ....
                  select a.Name, a.Pictures
 
myLabel.Text = album.Name;
myRepeater.DataSource = album.Pics
myRepeater.DataBind();

Hope this helps,

Imar

areed24 · April 7th, 2009, 12:21 PM

But that brings in the following error with using the repeater because I want to select the first picture as a link to the gallery.

Quote:

Sequence operators not supported for type 'System.String'

That is why I thought a for loop might work best, the problem is I don't know how to do that. What do you think?