Hello All,

i am using self servicing

i have emailcommunication table , it have following fields EmailCommunicationID ->PK CoachID MemberID CoachToMember Subject Body

i have member Table, it have following fields MemberID ProgramTypeID UserID

i have coach table, it have following fields CoachID UserID

i have user table with following fields UserID FirstName lastName Email

so how can i make relationship, so i can search data by first name & Last name from emailcommunication table

Please write the exact SQL query that you want to produce, defining which fields you want to retrieve and how do you want to make the filter. (all in SQL).

Walaa wrote:

Please write the exact SQL query that you want to produce, defining which fields you want to retrieve and how do you want to make the filter. (all in SQL).

hi walaa,

i want that,

i want to search firstname, lastname from emailcommunication table based on coach id, in that table(i.e. emailcommunication ) there is coachid, now i can get userid from coach table, and from user table i can get first name & last name.

so how can i achieve this?

Assuming you're using the adapter, the following sample code should give you a good idea how to start. And I'm sure it would be very easy to port this to SelfServicing, in case you are using it.

var collection = new EntityCollection<EmailCommunicationEntity>();

var filter = new RelationPredicateBucket();

filter.PredicateExpression.Add(UserFields.FirstName % "%John%");
filter.PredicateExpression.AddWithOr(UserFields.LastName % "%John%");

filter.Relations.Add(EmailCommunicationEntity.Relations.Coach...);
filter.Relations.Add(CoachEntity.Relations.User...);

using (var adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(collection, filter);
}

Walaa wrote:

Assuming you're using the adapter, the following sample code should give you a good idea how to start. And I'm sure it would be very easy to port this to SelfServicing, in case you are using it.

var collection = new EntityCollection<EmailCommunicationEntity>();

var filter = new RelationPredicateBucket();

filter.PredicateExpression.Add(UserFields.FirstName % "%John%");
filter.PredicateExpression.AddWithOr(UserFields.LastName % "%John%");

filter.Relations.Add(EmailCommunicationEntity.Relations.Coach...);
filter.Relations.Add(CoachEntity.Relations.User...);

using (var adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(collection, filter);
}

hi walaa,

i am using self servicing.

sorry i have not specified, but can u please provide some sample for this, in self servicing...

Very similar:

// the collection to retrieve
var collection = new EntityCollection<EmailCommunicationEntity>();

// the filter
var filter = new PredicateExpression();
filter.Add(UserFields.FirstName % "%John%");
filter.AddWithOr(UserFields.LastName % "%John%");

// the involved relations
RelationCollection rels = new RelationCollection();
rels.Add(EmailCommunicationEntity.Relations.Coach...);
rels.Add(CoachEntity.Relations.User...);

// fetch
collection.GetMulti(filter, 0, null, rels);

For more info, read Multi-entity filters.

daelmo wrote:

Very similar:

// the collection to retrieve
var collection = new EntityCollection<EmailCommunicationEntity>();

// the filter
var filter = new PredicateExpression();
filter.Add(UserFields.FirstName % "%John%");
filter.AddWithOr(UserFields.LastName % "%John%");

// the involved relations
RelationCollection rels = new RelationCollection();
rels.Add(EmailCommunicationEntity.Relations.Coach...);
rels.Add(CoachEntity.Relations.User...);

// fetch
collection.GetMulti(filter, 0, null, rels);

For more info, read Multi-entity filters.

Thanks daelmo,