CODEWITHSUNDEEP
c#linq
Guruprasad J Rao
Guruprasad J Rao

Reputation: 29683

Get one image from each album using linq expression

I have 2 tables Album and Images. Below is their structure.

Album

AlbumId      int      
AlbumName    nvarchar

Images

ImageId      int
ImageUrl     nvarchar
AlbumId      int [Foreign Key]
ImageDesc    nvarchar

I am trying to fetch one single image [Any image] from each album, to display it as cover photo for album, using LINQ. I am really not aware of fetching only one image based on each album as it needs some join operation [not sure though] and I have very basic knowledge in LINQ and SQL Queries. I am aware of how to fetch all the images from the table using LINQ which might be irrelavant here.

I have below model class for Images table to map with.

ImageDateModel Class

public class ImageDataModel
{
    public string ImageUrl { get; set; }
    public string ImageDescription { get; set; }
    public string AlbumId { get; set; }
}

Can anyone please help me to achieve this?

UPDATE

To retrieve all the images I just do

var images = (from img in db.tblImages select img).ToList();

Upvotes: 1

Views: 276

Answers (2)

Ed T
Ed T

Reputation: 420

var results = images.GroupBy(i => i.AlbumId).Select(albumGroup => new { AlbumId = albumGroup.Key, Image = albumGroup.First() })

Note that you can chain this to your first query without the call to ToList and depending on the db provider it may translate it into the db's query language so you don't have to pull the entire list into memory before doing the grouping.

Upvotes: 1

James Johnson
James Johnson

Reputation: 46067

I think you would want to group the images by album and then select the first item in each group as shown below:

class AlbumImage
{
    public string AlbumId
    {
        get;
        set;
    }

    public string ImageUrl
    {
        get;
        set;
    }
}

[TestMethod]
public void TestGetImages()
{
    var results = new List<AlbumImage>
    {
        new AlbumImage { AlbumId = "1", ImageUrl = "123.png" },
        new AlbumImage { AlbumId = "1", ImageUrl = "456.png" },
        new AlbumImage { AlbumId = "1", ImageUrl = "789.png" },
        new AlbumImage { AlbumId = "2", ImageUrl = "321.png" },
        new AlbumImage { AlbumId = "2", ImageUrl = "654.png" },
        new AlbumImage { AlbumId = "2", ImageUrl = "987.png" }
    };

    var imageResults = results.GroupBy(g => g.AlbumId).Select(grp => grp.First()).ToList();
}

The above example will group the images by album, and then select the first image for each album.

Upvotes: 1

Related Questions