LINQ query with nested list using Groupby and First

Question

I have a stored procedure returning results in a FlaggedCarDTO object:

public class FlaggedCarDTO
{
    public int CarId { get; set; }
    public int FlagUserId { get; set; }
    public int Year { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }
    public int ImageId { get; set; }
    public string ImageName { get; set; }
}

Here is some sample data:

var query = new List<FlaggedCarDTO>()
{
    new FlaggedCarDTO() { CarId = 1, FlagUserId = 22, Year = 2020, Make = "Toyota", Model = "Camry", ImageId = 1, ImageName = "01.jpg" },
    new FlaggedCarDTO() { CarId = 1, FlagUserId = 22, Year = 2020, Make = "Toyota", Model = "Camry", ImageId = 2, ImageName = "02.jpg" },
    new FlaggedCarDTO() { CarId = 1, FlagUserId = 46, Year = 2020, Make = "Toyota", Model = "Camry", ImageId = 1, ImageName = "01.jpg" },
    new FlaggedCarDTO() { CarId = 1, FlagUserId = 46, Year = 2020, Make = "Toyota", Model = "Camry", ImageId = 2, ImageName = "02.jpg" },
    new FlaggedCarDTO() { CarId = 6, FlagUserId = 67, Year = 2016, Make = "Honda", Model = "Civic", ImageId = 18, ImageName = "54.jpg" },
    new FlaggedCarDTO() { CarId = 6, FlagUserId = 67, Year = 2016, Make = "Honda", Model = "Civic", ImageId = 19, ImageName = "55.jpg" }
};

I want to condense this data to display only the first record for each CarId into this objects:

public class FlaggedCar
{
    public int Id { get; set; }
    public int FlagUserId { get; set; }
    public int Year { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }
    public List<Image> Images { get; set; }
}

public class Image
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Here is my LINQ query:

var result = query.GroupBy(x => x.CarId)
.Select(grp => grp.Select(c => new FlaggedCar()
{
    Id = c.CarId,
    FlagUserId = c.FlagUserId,
    Year = c.Year,
    Make = c.Make,
    Model = c.Model,
    Images = grp.Select(i => new Image()
    {
        Id = i.ImageId,
        Name = i.ImageName,
    }).ToList(),
}).First()).ToList();

Here is the result (serialized for readability):

[
    {
        "Id": 1,
        "FlagUserId": 22,
        "Year": 2020,
        "Make": "Toyota",
        "Model": "Camry",
        "Images": [
            {
                "Id": 1,
                "Name": "01.jpg"
            },
            {
                "Id": 2,
                "Name": "02.jpg"
            },
            {
                "Id": 1,
                "Name": "01.jpg"
            },
            {
                "Id": 2,
                "Name": "02.jpg"
            }
        ]
    },
    {
        "Id": 6,
        "FlagUserId": 67,
        "Year": 2016,
        "Make": "Honda",
        "Model": "Civic",
        "Images": [
            {
                "Id": 18,
                "Name": "54.jpg"
            },
            {
                "Id": 19,
                "Name": "55.jpg"
            }
        ]
    }
]

This is almost what I need, but the Images are not selecting only from the first record. Note that the Camry, the images include from both records. I think a .First() is needed on the nested Images list but I'm not sure where to add it?

Yong Shun · Accepted Answer · 2025-05-31 01:53:21Z

1

For your Images, you should have another .GroupBy() to group by (image) Id and get the first entry via .First().

Images = grp.Select(i => new Image()
    {
        Id = i.ImageId,
        Name = i.ImageName,
    })
    .GroupBy(img => img.Id)
    .Select(imgGroup => imgGroup.First())
    .ToList()

If you are using .NET 6 or above, you can work with .DistinctBy().

Images = grp.Select(i => new Image()
    {
        Id = i.ImageId,
        Name = i.ImageName,
    })
    .DistinctBy(x => x.Id)
    .ToList()

Demo @ .NET Fiddle

answered May 31 at 1:53

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PixelPaul May 31 at 12:33

.DistinctBy() worked perfectly for me, thanks!

Trupesh Kachhiya · Accepted Answer · 2025-05-31 04:33:32Z

0

var result = query
    .GroupBy(x => x.CarId)
    .Select(grp =>
    {
        var first = grp.First(); // First record per CarId
        var images = grp
            .Where(x => x.FlagUserId == first.FlagUserId) // Filter images by same FlagUserId
            .Select(x => new Image
            {
                Id = x.ImageId,
                Name = x.ImageName
            })
            .ToList();

        return new FlaggedCar
        {
            Id = first.CarId,
            FlagUserId = first.FlagUserId,
            Year = first.Year,
            Make = first.Make,
            Model = first.Model,
            Images = images
        };
    })
    .ToList();

answered May 31 at 4:33

Trupesh Kachhiya

11 bronze badge

1 Comment

Community May 31 at 8:51

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

LINQ query with nested list using Groupby and First

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related