How to build custom PLINQO query by multiple id's?

Question

Here's my table structure

Places

PlaceId PK
Name
...

PlaceCategories

CatId PK
Name
...

PlaceCats

PlaceId PK
CatId PK

Here's my query that pulls Places based on category id (table join)

public static IQueryable<Places> ByPlaceCat(this Table<Places> table, Expression<Func<PlaceCats, bool>> predicate) {
    var db = (DataContext)table.Context;
    var innerBizBase = db.PlaceCats.Where(predicate);
    return db.Places.Join(innerBizBase, a => a.PlaceId, ab => ab.PlaceId, (a, ab) => a);
}

I use it like this:

places = Db.Places.ByPlaceCat(a => a.CatId == 5);

But I want to be able to pull based on a List<int> of category id's. Looking through the generated PLINQO code, a query that pulls by multiple PlaceId's (but not using a joined table) looks like this:

public static IQueryable<Places> ByPlaceId(this IQueryable<Places> queryable, IEnumerable<long> values)
{
    return queryable.Where(p => values.Contains(p.PlaceId));
}

How could I essentially merge those two queries, to let me pass in a List<int> of CatId's to query by? This LINQ/PLINQO query is melting my brain. Thanks in advance!

score 3 · Accepted Answer · answered Mar 23 '11 at 18:04

You would need to write a extension method like this:

    public static IQueryable<Places> ByPlaceCats(this Table<Places> table, IEnumerable<int> catIds)
    {
        var db = (TestDataContext)table.Context;
        var places = (from placeCat in db.PlaceCats
                      join place in db.Places on placeCat.PlaceId equals place.PlaceId
                      where catIds.Contains(placeCat.CatId)
                      select place);
        return places;
    }

Please note that the PlaceCats table could be made into a ManyToMany relationship by adding two foreign keys to the proper tables. Once this change has been made than PLINQO will automatically generate the correct code and will create a link between the two tables skipping the intermediary table. So you could get a collection of PlaceCategories associated to the current Places entity by accessing a property on the Places entity.

Please remember to contact us if you have any questions and be sure to check out the community forums located here and PLINQO forums here.

Thanks -Blake Niemyjski (CodeSmith Support)

Aye, PlaceCats is the join table. I downloaded the latest PLINQO templates and regenerated the code. The many-to-many relations are there. I asked this for this scenario: I need to list places that are in every sub-category of a parent category, assuming the PlaceCats table does not have an entry joining parent categories to places. — Chaddeus, Mar 24 '11 at 00:24
I've since changed my design, I'll simply assign parent categories to places in the PlaceCats table too... no need for the query. But I REALLY appreciate the quick response, and I'll still put this query into my code for later use. Much appreciated. — Chaddeus, Mar 24 '11 at 00:25

How to build custom PLINQO query by multiple id's?

1 Answers1