predicate builder with two tables

Question

enter image description here

A Party can have one or more Contact objects.

I want to select all Parties who's streetname contains a specific keyword.
If I just want to search in Party I can use the code below. But how do I extend it to also search in Contact?

public IQueryable<Party> SearchParties(List<string> keywords)
    {
        var predicate = PredicateBuilder.False<Party>();

        foreach (string word in keywords)
        {
            var keyword = word;
            predicate = predicate.Or(p => p.surname.Contains(keyword));
            predicate = predicate.Or(p => p.lastname.Contains(keyword));
            predicate = predicate.Or(p => p.number.Contains(keyword));
        }
        return db.Parties.Where(predicate);
    }

Is there anything else you need to know?

EDIT
I guess I could create another predicate and then join them afterwards. Something like:

var predicate2 = PredicateBuilder.False<Contact>();

...and in the foreach:

predicate2 = predicate2.Or(p => p.streetname.Contains(keyword));

But how would I join predicate and predicate2 before returning?

EDIT2
Or, join the Party and Contact before doing the predicate Builder?

EDIT3
Here are parts of the generated classes:

[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.Contact")]
public partial class Contact : INotifyPropertyChanging, INotifyPropertyChanged
{
    private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);

    private int _id;  
    // ...more properties  
    private EntityRef<Party> _Party;

    public Contact()
    {
    this._Party = default(EntityRef<Party>);
    OnCreated();
    }
}

score 5 · Accepted Answer · answered Jun 28 '11 at 12:19

5

The point is that you have multiple Contacts to a single Party, hence you should decide if you want to have a Party if "any of the Contacts match the street name" or "all of the Contacts match the street name".

For the first case it would be:

predicate = predicate.Or(p => p.Contacts.Any(c => c.streetname.Contains(keyword))));

answered Jun 28 '11 at 12:19

Tz_

2,949
18
13

Thanks! You just waited until I posted a bounty on it, didn't you =) – Niklas Jun 28 '11 at 13:15
I didn't see your question before. I guess the bounty brought it up to the top of the stack. ;) – Tz_ Jun 28 '11 at 13:38
what if there's no explicit relationship between the tables? i.e. no foreign key? – drzaus Oct 15 '13 at 15:13

score 1 · Answer 2 · edited Jun 28 '11 at 13:43

1

This will work if your model has Contact as an associated object on the party object (and if it doesn't have a one-to-many relation)

predicate = predicate.Or(p => p.Contact.streetname.Contains(keyword));

edited Jun 28 '11 at 13:43

Niklas

13,005
23
79
119

answered Jun 13 '11 at 08:49

Richard Forrest

3,567
2
23
32

Tried that but I guess I don't =/. How do I add it as an associated object? – Niklas Jun 13 '11 at 08:51
What ORM are you using e.g. Linq to sql, EF – Richard Forrest Jun 13 '11 at 08:58
Linq to SQL, I started of with the NerdDinner. – Niklas Jun 13 '11 at 09:04
When you generate your datacontext for linq to sql from the database if the party and contact table have a foreign key relationship it should associate it for you. You could try refreshing the context from the database. – Richard Forrest Jun 13 '11 at 09:09
Didn't work. I do have `public EntitySet Contacts` that I guess maps to Party with the foreign key. So I thought I'd search that set instead: `predicate = predicate.Or(p => p.Contacts.Where(c => c.streetname.Contains(keyword))));` But it says it cannot implicitly convert to bool. – Niklas Jun 13 '11 at 09:22
Could you post the Contact and Party Classes – Richard Forrest Jun 13 '11 at 09:36
The whole generated model is 2.2k rows. The classes are too big to post I think. But if I have an EntitySet Contacts instead of a single Object Contact. Shouldn't it be possible to specify the streetname anyway? – Niklas Jun 13 '11 at 09:53
why don't you pass lambda expression instead of list ? – Stacker Jun 16 '11 at 19:14
@Stacker - What do you mean and will that help my question? – Niklas Jun 28 '11 at 12:14

predicate builder with two tables

2 Answers2

Linked