Creating dynamic queries with Entity Framework across multiple tables?

Question

I'm trying to create search functionality across a couple of tables, following the pattern in Creating dynamic queries with entity framework

I have 3 tables:

   People:
        pk ID
        varchar FirstName
        varchar LastName
        fk AddressMap_ID

   AddressMap:
        pk ID

   Address:
        pk ID
        varchar StreetName
        varchar StreeNumber
        fk AddressMap_ID

Multiple people can live at one address. I pass in a Search model, and populate the results property:

public class Search
{
   public string streetname { get; set; }
   public string streetnumber { get; set; }
   public string fname { get; set; }
   public string lname { get; set; }
   public IEnumerable<Results> results { get; set; }
}
public class Results
{
   public int AddressID { get; set; }
   public string StreetNumber { get; set; }
   public string StreetName { get; set; }
   public IEnumerable<PeopleResults> people { get; set; }
}
public class PeopleResults
{
   public int personID { get; set; }
   public string First { get; set; }
   public string Last { get; set; }
}

This works if I filter on an address, or name + address:

public void GetResults(Search model)
{
  Entities _context;
  _context = new Entities();

  var addr = from a in _context.Addresses
             select a;
  addr = addr.Where(filter => filter.StreetNumber == model.streetnumber);
  addr = addr.Where(filter => filter.StreetName == model.streetname);
  addr = from a in addr
         group a by a.AddressMap_ID into addrs
         select addrs.FirstOrDefault();

  var ppl = from p in addr.SelectMany(p => p.AddressMap.People) select p;
  ppl = ppl.Where(filter => filter.FirstName.StartsWith(model.fname));
  ppl = ppl.Where(filter => filter.LastName.StartsWith(model.lname));

  model.results = from a in addr
                  select new Results
                  {
                     AddressID = a.ID,
                     StreetName = a.StreetName,
                     StreetNumber = a.StreetNumber,
                     people = from p in ppl
                              select new PeopleResults
                              {
                                 First = p.FirstName,
                                 Last = p.LastName
                              }
                  };
}

But if I just try to filter on a name, it returns a cartesian join - every single address with all of the people that matched.

There are 3 ways to search: filtering on address only, filter on address + name, or filter on name only.

So if someone search for "123 Main", the results should be

123 Main St  SticksVille   Joe Smith
                           Jane Smith
                           Mary Smith

123 Main St  Bedrock       Fred Flintstone
                           Wilma Flintstone

A search for "J Smith 123 Main" should return just:

123 Main St  SticksVille   Joe Smith
                           Jane Smith

And a search for just "J Smith" should return:

123 Main St  SticksVille   Joe Smith
                           Jane Smith

456 Another St Sometown    Jerry Smith

Answer 1

It seems like an approach like this would probably work:

IQueryable<Person> ppl = _context.People;
ppl = addr.Where(filter=>filter.First.StartsWith(model.fname));
ppl = addr.Where(filter=>filter.Last.StartsWith(model.lname));
var pplIds = ppl.Select(p => p.PersonId);

model.results = from a in addr
                where a.AddressMap.People.Any(p => pplIds.Contains(p.PersonId))
                select new Results {
                          AddressID = a.ID,
                          StreetName = a.StreetName,
                          StreetNumber = a.StreetNumber,
                          people = from p in a.People
                                   select new PeopleResults {
                                         First = p.FirstName,
                                         Last = p.LastName
                                   }
                };

Rather than basing the people property on the matching people, you want to base the entire address set on the matching people.

Answer 2

Your query looks "symmetric" to me with respect to people and addresses, it only gets "asymmetric" in the final projected result. So, my idea is to express this symmetry in the query as far as possible:

• Get a set (IQueryable<Address>, not executed at once) of addresses filtered by street name and street number

• Get a set (IQueryable<Person>, not executed at once) of people filtered by the beginning of first name and last name

• Join the two sets by AddressMap_ID. The resulting set of people and addresses contains only those pairs that fulfill the filter criteria for addresses and people. If one of the filter criteria for person or address is not supplied (the first and the third of your examples at the bottom of the question) the join happens on the unfiltered set of all people/addresses, i.e. the joined pairs contain always all people of the filtered address (or all addresses of the filtered people)

• Group the joined pairs of people and addresses by Address.ID

• Project the groups into your Results collection. The group key is the AddressID. StreetName and StreetNumber can be fetched from the first address in each group and the people are projected from the people in each group.

• Execute the query

The following code doesn't cover the case specifically that none of the four filter criteria is supplied. It works in that case but would just load all addresses with all people of those addresses. Maybe you want to throw an exception in that case. Or return nothing (model.Results = null or so), then just jump out of the method.

public void GetResults(Search model)
{
    using (var _context = new Entities())
    {
        // "All" addresses
        IQueryable<Address> addresses = _context.Addresses;
        // "All" people
        IQueryable<Person> people = _context.People;

        // Build a Queryable with filtered Addresses
        if (!string.IsNullOrEmpty(model.streetname))
            addresses = addresses.Where(a => a.StreetName
                                              .StartsWith(model.streetname));
        if (!string.IsNullOrEmpty(model.streetnumber))
            addresses = addresses.Where(a => a.StreetNumber
                                              .StartsWith(model.streetnumber));

        // Build a Queryable with filtered People
        if (!string.IsNullOrEmpty(model.fname))
            people = people.Where(p => p.FirstName == model.fname);
        if (!string.IsNullOrEmpty(model.lname))
            people = people.Where(p => p.LastName == model.lname);

        // Join the two Queryables with AddressMap_ID
        // and build group by Address.ID containing pairs of address and person
        // and project the groups into the Results collection
        var resultQuery = from a in addresses
                          join p in people
                            on a.AddressMap_ID equals p.AddressMap_ID
                          group new { a, p } by a.ID into g
                          select new Results
                          {
                              AddressID = g.Key,
                              StreetName = g.Select(ap => ap.a.StreetName)
                                            .FirstOrDefault(),
                              StreetNumber = g.Select(ap => ap.a.StreetNumber)
                                              .FirstOrDefault(),
                              people = g.Select(ap => new PeopleResults
                              {
                                  First = ap.p.FirstName,
                                  Last = ap.p.LastName
                              })
                          };

        // Execute query (the whole code performs one single query)
        model.results = resultQuery.ToList();
    }
}

I am unsure if I interpret the AddressMap table correctly as a kind of join table for a many-to-many relationship (Address can have many people, Person can have many addresses), but the code above yields the three results of the three queries in your example as expected if the tables are filled like so:

The AddressMap table isn't actually used in the query because Addresses and People table are joined directly via the AddressMap_ID columns.