LINQ self refenced table filter relation

Question

I have 2 tables Categories and Images categories is self referenced with ParentId as Nullable Foreign Key

Code first Classes

public class Category {

    public int Id { get; set; }
    public string Name{ get; set; }
    public int? ParentId { get; set; }
    public bool IsDeleted { get; set; }
    public byte[] Timestamp { get; set; }

    public virtual Category Parent { get; set; }
    public virtual ICollection<Category> Parents { get; set; }

    public virtual ICollection<Image> Images { get; set; }
}


Public class Image {

    public int Id { get; set; }
    public int? CategoryId { get; set; }
    public string Source { get; set; }
    public string Description { get; set; }
    public bool IsDeleted { get; set; }
    public byte[] Timestamp { get; set; }

    // Foreign keys
    public virtual Category Category { get; set; }
}


public class CategoryMap : EntityTypeConfiguration<Category> {
    public CategoryMap() {
        // Primary Key
        HasKey(t => t.Id);

        // Properties
        Property(t => t.Name).IsRequired().HasMaxLength(250);
        Property(t => t.Timestamp).IsRequired().IsFixedLength().HasMaxLength(8).IsRowVersion();

        // Table & Column Mappings
        ToTable("Category");
        Property(t => t.Id).HasColumnName("Id");
        Property(t => t.ParentId).HasColumnName("ParentId");
        Property(t => t.Name).HasColumnName("Category");
        Property(t => t.IsDeleted).HasColumnName("IsDeleted");
        Property(t => t.Timestamp).HasColumnName("Timestamp");

        // Relationships
        HasOptional(t => t.Parent).WithMany(t => t.Parents)
                                  .HasForeignKey(d => d.ParentId)
                                  .WillCascadeOnDelete(false);
}

public class ProfileImageMap : EntityTypeConfiguration<ProfileImage> {

    public ProfileImageMap() {
        // Primary Key
        HasKey(t => t.Id);

        // Properties
        Property(t => t.Source).IsRequired().HasMaxLength(255);
        Property(t => t.Description).HasMaxLength(255);
        Property(t => t.Timestamp).IsRequired().IsFixedLength().HasMaxLength(8).IsRowVersion();

        // Table & Column Mappings
        ToTable("Images");
        Property(t => t.Id).HasColumnName("Id");
        Property(t => t.CategoryId ).HasColumnName("CategoryId ");
        Property(t => t.Source).HasColumnName("Source");
        Property(t => t.Description).HasColumnName("Description");
        Property(t => t.IsDeleted).HasColumnName("IsDeleted");
        Property(t => t.Timestamp).HasColumnName("Timestamp");

        // Relationships
        HasOptional(t => t.Category).WithMany(t => t.Images)
                                    .HasForeignKey(d => d.CategoryId);
    }
}

** the Context**

    public DbSet<Category> Categories { get; set; }
    public DbSet<Image> Images { get; set; }

the question is how do I build a LINQ statement to return a category based on Id with parents Categories with all Images excluding the images Marked as IsDeleted = true

is it possible to do it in LINQ or LinqKit

Answer 1

This will return you all the details you need with the filter applied. The only 'complex' part is the Where clause applied to the Images property (and I also added the null check otherwise you may get a NullReferenceException):

var categoryID = 2; //The ID you are searching for

var category = from c in categories
               where c.Id == categoryID 
               select new Category
               {
                   Id = c.Id,
                   Name = c.Name,
                   ParentId = c.ParentId,
                   IsDeleted = c.IsDeleted,
                   Timestamp = c.Timestamp,
                   Parent = c.Parent,
                   Images = c.Images == null ? 
                       (ICollection<Image>)new List<Image>() : 
                       (ICollection<Image>)c.Images.Where(i => i.IsDeleted = false).ToList()
               }.Single();

If you want to return all parent categories (i.e. traverse the hierarchy), then one method is to have a function like this:

public IEnumerable<Category> GetParents(IEnumerable<Category> categories, Category child) 
{
    List<Category> parents = new List<Category>();
    var current = child.Parent;
    while (current != null)
    {
        parents.Add(parent);
        parent = parent.Parent;
    } 

    return parents;

}

Now you can say:

category.Parents = GetParents(categories, category);