How to add a PredicateBuilder to my left outer join

Question

I have the following left outer join-based LINQ query between the tables Units, GeoLocations, and UnitStatuses:

var currUnitResults = (from unit in pacificRepo.Units
                        join loc in pacificRepo.GeoLocations on new { unit.geo_location.Latitude, unit.geo_location.Longitude } equals new { loc.geo_location.Latitude, loc.geo_location.Longitude }
                        join st in pacificRepo.UnitStatuses on unit.c_number equals st.c_number into stj
                        from sublist in stj.DefaultIfEmpty()
                        select new
                        {
                            index = "0",
                            unit.c_number,
                            unit.serial_number,
                            unused = unit.location_at_address,
                            unit.ip_address,
                            unit.build_version,
                            status = (sublist.status == Pacific.Domain.Entities.UnitStatus.Statuses.COMPLETE) ? "<font color=\"green\">" + sublist.stage + "</font>" : "<font color=\"red\">" + sublist.stage + "</font>",
                            location = (loc == null) ? "Unknown" : loc.city + ", " + loc.state_abbrv,
                            unit.location_at_address,
                            latitude = unit.geo_location.Latitude,
                            longitude = unit.geo_location.Longitude
                        }).OrderBy(p => p.c_number).Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList()
                        .Select(x => new object[] {
                            x.index, x.c_number, x.serial_number, x.unused, x.ip_address, x.build_version, x.status, x.location, x.location_at_address, x.latitude, x.longitude
                        }).ToList();

I want to add be able to dynamically add predicates to return items conditionally. I found PredicateBuilder to be suggested in various places on StackOverflow, but I am a little bit confused about two things.

Given my above statement, how do I actually add the following predicate to it:
```
var searchPredicate = PredicateBuilder.True<Unit>();
searchPredicate = searchPredicate.And(u => u.geo_location.Latitude >= swLat);
```
I thought I could add it like var currentUnitResults = (from unit....).Where(searchPredicate), but that does not work because it has already evaluated the leading statement to an IQueryable<AnonymousType>.
Given my join, how do I properly add a predicate on one of the joined tables so that I could also write a statement like:
```
searchPredicate = searchPredicate.And(loc => loc.city.Contains("Pittsburgh"));
```

A sincere thanks for any help/guidance on this.

score 0 · Answer 1 · answered May 09 '14 at 18:42

Because your predicate is expected to be used on Units, maybe you could try something like this:

var currUnitResults = pacificRepo.Units
    .Include(u => u.UnitStatuses)
    .Join(pacificRepo.GeoLocations,
        u => new { u.geo_location.Latitude, u.geo_location.Longitude },
        g => new { g.geo_location.Latitude, g.geo_location.Longitude },
        (u, g) => new { u, g })
    .Where(searchPredicate)
    .OrderBy(...

How to add a PredicateBuilder to my left outer join

1 Answers1