CODEWITHSUNDEEP
entity-frameworkentity-framework-4ef-code-first
Goran
Goran

Reputation: 6518

Sorting data in self referencing table

I have a table where I store some products.

ProductA
ProductB
ProductC

One of the requests is that one product can belong to another

ProductA
ProductD -> ProductA
ProductE -> ProductA
ProductB
ProductF -> ProductB
ProductC

As you can see, the products that belong to another product must be positioned right bellow it. All data must belong to one list (no nested collections), since I need to display data in one grid only.

if I introduce a new property ReferenceProductId, that is pointing to another product, then I solve the issue of "belonging", but I am unable to find a way how to sort them. The easiset way is if I could say that ProductA belongs to ProductA, but that is not possible, if I am not mistaken. Also, when I am assigning one product to another, I cannot do this:

product.ReferenceProductId = anotherProduct.Id

I need to assign a reference itself, since I am working with identity primary keys, so Id will be 0 for new records.

product.ReferenceProduct = anotherProduct;

WHat are your thoughts here? I can make it save data correctly, but I cannot make it load them in above mentioned sort order.

Upvotes: 0

Views: 181

Answers (1)

Mark Oreta
Mark Oreta

Reputation: 10416

You could create a custom comparer to order your list by. This is just an example, but it's using comparing Id's, and reference Id's which allowed me to achieve the results you're wanting above, assuming referenceId is null when there's not a product reference. You can alter the code if the FK hasn't been updated by calling product.Reference.Id, but for simplicity's sake I ignored this.

My product class:

    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int? ReferenceId { get; set; }
    }

The comparer:

public class ProductComparer : IComparer<Product>
{
    public int Compare(Product product, Product other)
    {
        if (product.ReferenceId == null && other.ReferenceId == null)
            return product.Id.CompareTo(other.Id);

        if (product.ReferenceId == null && other.ReferenceId != null)
            return product.Id.CompareTo(other.ReferenceId);

        if (product.ReferenceId != null && other.ReferenceId == null)
            return ((int) product.ReferenceId).CompareTo(other.Id);

        if (product.ReferenceId == other.ReferenceId)
            return product.Id.CompareTo(other.Id);

        return ((int) product.ReferenceId).CompareTo((int) other.ReferenceId);
    }
}

Then you would call your collection with something like this:

products.OrderBy(p => p, new ProductComparer());

Upvotes: 2

Related Questions