0

I have following query:

var query = from product in SH_Products
        from product_group in SH_ProductGroups_Products.Where(c=>c.ProductID == product.ID)
        from manufacturer in SH_Manufacturers.Where(c=>c.ID == product.ManufactureID)   
        from attributeOption_product in SH_AttributeOptions_Products.Where(c=>c.ProductID == product.ID).DefaultIfEmpty()
        where !product.IsDeleted
        && !product_group.IsDeleted
        && !manufacturer.IsDeleted
        && product.Status != 0  
        select new
        {
            product.ID,   
            ProductGroupID = product_group.ProductGroupID,
            AttributeOptionID = (int?)attributeOption_product.AttributeOptionsID,
            product.ManufacturerID
        };
query.Distinct().Dump();

Below is the sample output:

ID    ProductGroupID    AttributeOptionID    ManufacturerID
1     1                 75                   1
1     1                 76                   1
2     3                 17                   2
3     2                 3                    1
4     1                 NULL                 1

As you see, we have 2 record with ID = 1, and I don't wanna this, how I can remove this one?

I wrote this query for filtering section of my project. I have filtering by ProductGroupID, AttributeOptionID, ManufacturerID, all of them are multi-select-able !

thanks !

  • Not an answer, but you you should use join syntax rather than id=id. Better still, use navigation properties. If you don't want duplicates, you should remove `AttributeOptionID` from the results. – Gert Arnold Dec 04 '14 at 07:07
  • thanks, I know if I remove 'AttributeOptionID', the 'Distinct' will work, but I need that ! –  Dec 04 '14 at 07:21
  • 1
    You can use the other overload of Distinct extension method which asks for an EqualityComparer – serdar Dec 04 '14 at 07:36
  • If you need `AttributeOptionID` *and* distinct product ids you need a different shape of the output. What do you propose? – Gert Arnold Dec 04 '14 at 08:35

1 Answers1

0

As I said in the comments, you can use the other overload of Distinct extension method which asks for an EqualityComparer.

Or another way you can use

query.GroupBy(item => item.ID).Select(grp => grp.First())...

instead of

query.Distinct()...

Please do not forget that the output will not contain the second record in your sample output.

If the key of your records is not only ID but ID and ProductGroupID instead, usage can be

query.GroupBy(item => new {item.ID, item.ProductGroupId} ).Select(grp => grp.First())...
serdar
  • 1,564
  • 1
  • 20
  • 30
  • That way is totally true, I don't know that ability in LINQ, thanks! but as you said, I have no access to some of records in output and I don't wanna lose any record. I found a solution with creating view and using `FULL JOIN` in `SQL`. –  Dec 04 '14 at 08:22
  • @MehdiDehghani Then omitting `.Select(grp => grp.First())` part you have groups. There are four groups with keys 1,2,3 and 4. First group has 2 records and the others one each. – serdar Dec 04 '14 at 08:27