Hi, I have two tables that have a field in common. I want to get each record in one table that has a corresponding record in the other table. How do i do this? In the designer, I have already set up the relationship. In SQL, I would do a join. How do I do this using the adapter thing?

Here is some of my code:

var filter = new PredicateExpression();
            filter.Add(ScriptTimeSlotFields.StartDateTime >= startDate);
            filter.Add(ScriptTimeSlotFields.StartDateTime < endDate.AddDays(1));

            
            if (producer != "") {
                var likePredicate = new FieldLikePredicate(ScriptFields.Producers, null, "%" + producer.ToUpper() + "%");
                likePredicate.CaseSensitiveCollation = true;
                filter.Add(likePredicate);
            }

            if (GetRole() == "Producer") {
                filter.Add(ScriptFields.ScriptType == TVSNConst.ScriptType_Live_Description);
                filter.Add(ScriptTimeSlotFields.StudioId == TVSNCountryConst.AUStudioId);
            }
            var sorter = new SortExpression(ScriptTimeSlotFields.StartDateTime | SortOperator.Ascending);
            sorter.Add(ScriptTimeSlotFields.StudioId | SortOperator.Ascending);
            using (var adapter = new DataAccessAdapter(OrYxWebAccessFunctions.GetDefaultConnectionString())) {
                adapter.FetchTypedList(e.ContainedTypedList, filter, 0, sorter, true);

}

Did you setup the relation between entities or You did added the join in the TypedList in the LLBLGen designer? If so, What type of join was in your TypedList (left, right, inner)?

Another option is to obtain the relationPredicateBucket and add the additional filters and relations there. Example:

var bucket = e.ContainedTypedList.GetRelationInfo();

bucket.PredicateExpression.Add(ScriptTimeSlotFields.StartDateTime >= startDate);
bucket.PredicateExpression.Add(ScriptTimeSlotFields.StartDateTime < endDate.AddDays(1));
...
bucket.Realtions.Add(... you relation ...);
....

 adapter.FetchTypedList(e.ContainedTypedList.GetFieldsInfo(), e.ContainedTypedList, bucket, 0, sorter, true);

daelmo wrote:

Did you setup the relation between entities or You did added the join in the TypedList in the LLBLGen designer? If so, What type of join was in your TypedList (left, right, inner)?

Another option is to obtain the relationPredicateBucket and add the additional filters and relations there. Example:

var bucket = e.ContainedTypedList.GetRelationInfo();

bucket.PredicateExpression.Add(ScriptTimeSlotFields.StartDateTime >= startDate);
bucket.PredicateExpression.Add(ScriptTimeSlotFields.StartDateTime < endDate.AddDays(1));
...
bucket.Realtions.Add(... you relation ...);
....

 adapter.FetchTypedList(e.ContainedTypedList.GetFieldsInfo(), e.ContainedTypedList, bucket, 0, sorter, true);

hi, it's set up as a left join. How can i make it be an inner join (via an option that the user selects at runtime)?

You can set the Joint hint in the Designer for TypedLists, but if you want this to be dynamically set by the use, then maybe you'd better use a DynamicList where you do every thing in code, not in the designer, so the JointHint can be set accordingly when you set the relation used in the fetch method.

Also here is how you can do it in QuerySpec.

Walaa wrote:

You can set the Joint hint in the Designer for TypedLists, but if you want this to be dynamically set by the use, then maybe you'd better use a DynamicList where you do every thing in code, not in the designer, so the JointHint can be set accordingly when you set the relation used in the fetch method.

Also here is how you can do it in QuerySpec.

hi i tried using query spec once, but it didn't work at all. I think I had to regenerate the source code, and do a whole lot of other stuff. So i can't do it using a typed list, period?

joshg wrote:

hi i tried using query spec once, but it didn't work at all. I think I had to regenerate the source code, and do a whole lot of other stuff.

You don't have to regenerate code in order to user QuerySpec. If you show us your code we can try to help you on that.

joshg wrote:

So i can't do it using a typed list, period?

Yes, you can. The thing is: what relation do you want to change and how to identify that relation you want to change. Here is a little example. I have a relation on my typedList: Order->Shipper. In the Designer the join hint is LEFT. Here I change it to INNER:

[TestMethod]
public void ChangeJoinHintOfTypedList()
{
    var typedList = new SalesTypedList();

    // filters, etc
    var bucket = typedList.GetRelationInfo();
    bucket.PredicateExpression.Add(OrderFields.OrderDate <= DateTime.Now);

    // replace the join hint
    foreach (IRelation rel in bucket.Relations)
    {
        // find the appropriate relation
        if ((rel.HintTargetNameLeftOperand == EntityType.OrderEntity.ToString() && rel.HintTargetNameRightOperand == EntityType.ShipperEntity.ToString())
                || (rel.HintTargetNameLeftOperand == EntityType.ShipperEntity.ToString() && rel.HintTargetNameRightOperand == EntityType.OrderEntity.ToString()))
        {
            ((EntityRelation)rel).HintForJoins = JoinHint.Inner;
        }
    }

    // fetch
    using (var adapter = new DataAccessAdapter())
    {
        adapter.FetchTypedList(typedList.GetFieldsInfo(), typedList, bucket, 0, null, true);
    }
}

Note that above I find the relation by comparing it's start entity type to the end entity type. This won't work, possibly, if you have more than one relation with the same ends types.