Linq query with aggregates

Question

I want to write an elegant linq query to handle the following SAMPLE object model:

    class Category
    {
        public string Name { get; set; }        
        public IList<Product> Products { get; set;}        
    }

    class Product
    {
        public string Title { get; set; }
        public IList<Photo> Photos { get; set; }
    }

    class Photo
    {
        public int Id { get; set; }
    }

I constructed the following query to get the Photo Id:

    var query = from category in Factory.GetCategories()
                where category.Name == "Cameras"
                select (from product in category.Products
                        where product.Title == "Sony"
                        select (from photo in product.Photos
                                select photo.Id)
                               );

    var v = query.ToList();

At the moment the query does not project correctly i have to add a FirstOrDefault() to each of the Sub Selects!:

var query = from category in Factory.GetCategories()
                where category.Name == "Cameras"
                select (from product in category.Products
                        where product.Title == "Sony"
                        select (from photo in product.Photos
                                select photo.Id).FirstOrDefault()
                               ).FirstOrDefault();

var v = query.ToList();

Is there a better way to do this? Ignoring the fact that we are not dealing with a database and PK's/FK's are not in play.

I really want to avoid writing a big for loop when i could do the same thing in a linq query:

foreach (var category in Factory.GetCategories())
            {
                if (category.Name == "Camera")
                {
                    foreach (var product in category.Products)
                    {
                        if (product.Title == "Sony")
                        {
                            foreach (var photo in product.Photos)
                            {
                                //get data
                                int id = photo.Id;
                            }
                        }
                    }
                }                
            }

The actual implementation is more complex than this simple object model. I want to get the basic idea from this simple sample so i apply it to my real object model.

Cheers!

Answer 1

Do you just want the flattened ids?

        var query = from category in Factory.GetCategories()
                    where category.Name == "Cameras"
                    from product in category.Products
                    where product.Title == "Sony"
                    from photo in product.Photos
                    select photo.Id;

Answer 2

Well yes - you have to add a FirstOrDefault because there's no such thing as "the" photo ID. There are potentially many photos per camera - which do you want?

If you only care about the first photo, that's fine - although multiple "from" clauses would make your life easier:

var query = from category in Factory.GetCategories()
            where category.Name == "Cameras"
            from product in category.Products
            where product.Title == "Sony"
            select product.Photos.Select(photo => photo.Id)
                                 .FirstOrDefault();

Note that that will return 0 for a product with no photos. Is that what you want?

If you could be clearer in your requirements, we'll be better equipped to help you.

EDIT: If you only want the very first ID of any Sony Camera, then use:

var query = from category in Factory.GetCategories()
            where category.Name == "Cameras"
            from product in category.Products
            where product.Title == "Sony"
            from photo in product.Photos
            select photo.Id;
var firstId = query.FirstOrDefault();

Answer 3

  var ids = from category in Factory.Categories
            where category.Name == "Cameras"
            from product in category.Products
            where product.Title == "Sony"
            from photo in product.Photos
            select photo.Id;

Returns all ids of photos, the enumerable is empty when no photos. If you want the first one, or default to 0 :

var result = ids.FirstOrDefault();

Answer 4

Same answer as everyone else, but with extension method calls instead of query comprehension. SelectMany allows you to "unpack" a child collection and continue querying at that level.

var query = Factory.GetCategories()
  .Where(category => category.Name == "Cameras")
  .SelectMany(category => category.Products)
  .Where(product => product.Title == "Sony")
  .SelectMany(product => product.Photos)
  .Select(photo => photo.Id);