CODEWITHSUNDEEP
entity-frameworklinq
Night Coder
Night Coder

Reputation: 53

C# search using a LINQ query with child records

I have a Person and PersonType object where a Person has one to many PersonTypes. A PersonType can be Accountant, Consultant etc.

Person table has the following columns: 'firstname' | 'lastname' | 'email' | 'phonenumber'

PersonType table has the following columns: 'name' | 'description'

I've created a search mechanism using LINQ in C# but the problem is that I get a wrong result due to an issue with a LINQ constraint. Suppose we have two Person records where the first record is a Consultant and the other record is Consultant and Accountant meaning it has to child objects.

Note. My search is case insensitive.

Suppose my search text is 'co'. Then I don't want the result to be 3 records. Now it returns the first person and two instances of the second person due to two matches of 'co' for Accountant and Consultant for the second Person.

Of course I can go through the result and filter out duplicates but it would be nice to have it one query.

Many thanks for your input.

Here is the Linq:

private IList<PersonViewModel> SearchAll(string searchCriteria)
{
   var result = System.Web.HttpContext.Current.Session["Persons"] as IList<PersonViewModel>;

    if (result != null)
    {
        var v = (from a in result
                 from b in a.PersonTypes
                    where
                            a.FirstName.CaseInsensitiveContains(searchCriteria) ||
                            a.LastName.CaseInsensitiveContains(searchCriteria) ||
                            a.Email.CaseInsensitiveContains(searchCriteria) ||
                            a.PhoneNumber.CaseInsensitiveContains(searchCriteria) ||
                            b.Name.CaseInsensitiveContains(searchCriteria)
                 select a);

        return v.ToList();
    }

    return result;
}

Upvotes: 1

Views: 266

Answers (3)

Gert Arnold
Gert Arnold

Reputation: 109252

One little strategy may be very helpful in problems with queries and search conditions:

Only query the entities you need and do nothing but where.

You need PersonViewModels. By joining in PersonTypes (from - from is a GroupJoin) also PersonTypes become accessible to the end result. So if you only query the entities you need, there should be one, and only one, from:

var v = from a in result

Now what should the where be? The conditions for PersonViewModel are clear, but what about PersonTypes? Loosely defined in plain language, where at least one of their PersonTypes contains "co". In LINQ that amounts to:

var v = from a in result
    where a.FirstName.CaseInsensitiveContains(searchCriteria)
       || a.LastName.CaseInsensitiveContains(searchCriteria)
       || a.Email.CaseInsensitiveContains(searchCriteria)
       || a.PhoneNumber.CaseInsensitiveContains(searchCriteria)
       || a.PersonTypes.Any(pt => pt.Name.CaseInsensitiveContains(searchCriteria));

Now you don't need Distinct because there's no join that duplicates results.

Upvotes: 1

Hamzeh.Ebrahimi
Hamzeh.Ebrahimi

Reputation: 305

you can get result as following

var v=result?.Where(p=>p.PersonTypes.Select(t=>t.Name).Contains(searchCriteria)    ||
                            p.FirstName.CaseInsensitiveContains(searchCriteria)   ||
                            p.LastName.CaseInsensitiveContains(searchCriteria)    ||
                            p.Email.CaseInsensitiveContains(searchCriteria)       ||
                            p.PhoneNumber.CaseInsensitiveContains(searchCriteria) ||)?.ToList();

Upvotes: 1

ZERROUKI Ali
ZERROUKI Ali

Reputation: 249

Salam i think you can do it like that 

v.GroupBy(person => person.id)
  .Select(g => g.First())
  .ToList();

Upvotes: 0

Related Questions