CODEWITHSUNDEEP
c#linq
AmirA
AmirA

Reputation: 103

Select all data for duplicate objects

I have a list of companies named Companies which has duplicate companies inside. Another list relationRepo which keeps all relations between companies and persons. I need all relations of companies inside Companies. 

var relations = relationRepo.Where(x => Companies.Any(y => x.QId == y.QId));

returns relations for companies but not for duplicates. In other words, for duplicate companies this query returns relations once. For example, Company A exists in Companies twice and there is 3 relations for Company A in relationRepo. After running query above, there is 3 relations in relations for Company A but I need 6 relations. Any help?

Upvotes: 0

Views: 69

Answers (2)

Harald Coppoolse
Harald Coppoolse

Reputation: 30454

So you have Companies, and every Company has zero or more Relations. Every Relation belongs to exactly one Company, using a foreign key CompanyId.

Your requirement is a bit unclear:

I need all relations of companies inside Companies.

It is a bit unclear what you want.

Company (1) with Name "Acme" has Relations A, B, C 
Company (2) with Name "Acme" has Relations C, X, Y
Company (3) with Name "Other" has Relations D, E,
Company (4) with Name "Other" has Relations D, F,

All Relations of all Companies inside your sequence of Companies is A B C D E X Y. Or in other words: all Relations that are used by any of the companies. According to your example, this is not what you want.

You also wrote:

I have a sequence of Companies, which has duplicate companies inside.

Apparently you have a method to decide whether two Companies are in fact the same Company. This might be by name, or by tax number, or whatever. How you decide whether two Companies are the same is irrelevant of this question. Let's assume you have an object of a class that implements IEqualityComparer<Company>:

IEqualityComparer<Company> comparer = ...;

Back to your requirement.

I think you meant to say that you wanted Companies with their Relations. If two companies are in fact the same company you want them returned as one "Company with its relations"

So in the above example, you want the following result

  • Company with Name "Acme" with Relations A B C X Y
  • Company with name "Other" with Relations D E F

The problem with this requirement is, if you consider two companies to be equal (for instance, because they have the same name) what do you want with the Company properties that are different? For example: What Id should I use for the Company with Name "Acme"?

Several solutions:

  • This is not a problem, two Companies are only equal if all properties are equal
  • This is not a problem, I don't want any of the Company Properties
  • This is not a problem, I only want the Company properties that are equal. For example, I want the Name, but not the Id
  • I don't care which of the equal Companies you take

Because of this incomplete requirement the answer must be very generic. All answers will do the following:

  • Find all "Companies with their Relations"
  • Group them by Equal Company
  • Select the Company parts and the Relation parts you want.

The difference in the four possible requirements described above is what Company parts you select at the end.

I'll give the solution of the most likely requirement:

  • Two companies are equal if they have the same name.
  • Give me the names of the companies with all their relations

.

// "I have a List of companies and a List of their Relations"
IEnumerable<Company> companies = ...
IEnumerable<Relation> relations = ...

// I want all Companies with their Relations
var result = companies.GroupJoin(relations,  // GroupJoin Companies and Relations
    company => company.Id,                   // from each Company take the primary key
    relation => relation.CompanyId,          // from each Relation take the foreign key

    // and all Relations with matching foreign key
    (company, relationsOfThisCompany) => new
    {
        Company = company,
        Relations = relationsOfThisCompany,
    })
    // Result: a sequence of companies, each with their Relations

    .GroupBy(
       // KeySelector: make groups of companies with same Name
       joinResult => joinResult.Company.Name,


    // ResultSelector: take the Key (which is the common Name)
    // with all joinResults of companies with name equal equal to the Key to create the output
    (companyName, companiesWithThisName => new
    {
        Name = key.Name,
        Relations = companiesWithThisName.Select(company => company.Relations),
    },

    // Comparer: when are two company names equal?
    StringComparer.CurrentCultureIgnoreCase);

Because I had a fairly simple definition of Company Equality, I could use an already existing equality comparer to decide whether two companies are equal.

The advantage of a separate comparer for company equality would be that if in future you decide that you need other properties to decide whether two companies are equal, for instance their Tax Number, instead of their name, this code won't change. It's up to you to decide whether you'll use the concept of "company equality" often enough to decide whether or not to make a special equality comparer

Upvotes: 0

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62488

You need a join query to get what you are looking for. The following should work:

var relations = (from relation in relationRepo
            join company in Companies on relation.QId equals company.QId
            select relation).ToList();

This will now provide the items matching on QId in both. What the above query does is match row in Companies for for QId so in your case if the Companies have 6 items with QId matching in relationRepo, it will give you 6 rows for in relations.

Upvotes: 4

Related Questions