I think this is very basic, but either I don't understand or I'm using it the wrong way. But how can I get a resultset back from a join query. So the resultset contains fields of both joined table. I use a left join so the joined table doesn't need to have data.

Do I use create Resultfields for this. I think so, but how can I fill these fields (returning a datatable)

this code return sqlexecptions (Multiplartidentifier cannot be bound)

[colorvalue="008822"]ResultsetFields fields = new ResultsetFields(8);
            fields.DefineField(SystemSettingFields.SystemSettingId, 0, SystemSettingFields.SystemSettingId.Name, "S");
            fields.DefineField(SystemSettingFields.Definition, 1, SystemSettingFields.Definition.Name, "S");
            fields.DefineField(SystemSettingFields.IsActive, 2, SystemSettingFields.IsActive.Name, "S");
            fields.DefineField(SystemSettingFields.SettingValueType, 3, SystemSettingFields.SettingValueType.Name, "S");
            fields.DefineField(SystemSettingFields.CanOverrideByUnitSetting, 4, SystemSettingFields.CanOverrideByUnitSetting.Name, "S");
            fields.DefineField(SystemSettingFields.DefaultSystemValue, 5, SystemSettingFields.DefaultSystemValue.Name, "S");
            fields.DefineField(SystemSettingOrganizationFields.SystemValue, 6,"OrganizationValue", "O");
            fields.DefineField(SystemSettingOrganizationFields.SystemValue, 7, "UnitValue", "U");

            IRelationPredicateBucket filter = new RelationPredicateBucket();

            filter.Relations.Add(SystemSettingEntity.Relations.SystemSettingOrganizationEntityUsingSystemSettingId, JoinHint.Left);
            filter.Relations.Add(SystemSettingEntity.Relations.SystemSettingUnitEntityUsingSystemSettingId, JoinHint.Left);

            filter.PredicateExpression.Add(new FieldCompareValuePredicate(SystemSettingFields.IsActive, null, ComparisonOperator.Equal, true));
            filter.PredicateExpression.AddWithAnd(new FieldCompareValuePredicate(SystemSettingFields.CanOverrideByUnitSetting, null, ComparisonOperator.Equal, true));
            filter.PredicateExpression.AddWithAnd(new FieldCompareValuePredicate(SystemSettingOrganizationFields.OrganizationId, null, ComparisonOperator.Equal, oId));

            DataTable results = new DataTable();

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchTypedList(fields, results, filter, true);
            }
            return results;[/color]

Any help is appreciated. Thx

I'm also intrested if this is is also possible to do using LINQ

This looks almost right to me. I see two problems:

1. You are aliasing the same table with two different names ("O" and "U").
2. You are not using the aliases when you create the relation. There are overloads in filter.Relations.Add for table/entity aliases.

From what you have here, it doesn't look like table/entity aliases are needed at all, since you are not using the same table for more than one relation. So each DefineField call can look like:

//before
fields.DefineField(SystemSettingOrganizationFields.SystemValue, 6,"OrganizationValue", "O");

//after
fields.DefineField(SystemSettingOrganizationFields.SystemValue, 6,"OrganizationValue");

I'm only guessing at the version you are using, but that should work in 2.x at least.

HTH,

Phil

Hi Phil,

thanks for the reply. You are right, I finally got it to work:

 public DataTable LoadAllUnitSystemSettingsDataTable(int uId, int oId)
        {

            ResultsetFields fields = new ResultsetFields(8);
            fields.DefineField(SystemSettingFields.SystemSettingId, 0, SystemSettingFields.SystemSettingId.Name);
            fields.DefineField(SystemSettingFields.Definition, 1, SystemSettingFields.Definition.Name);
            fields.DefineField(SystemSettingFields.IsActive, 2, SystemSettingFields.IsActive.Name);
            fields.DefineField(SystemSettingFields.SettingValueType, 3, SystemSettingFields.SettingValueType.Name);
            fields.DefineField(SystemSettingFields.CanOverrideByUnitSetting, 4, SystemSettingFields.CanOverrideByUnitSetting.Name);
            fields.DefineField(SystemSettingFields.DefaultSystemValue, 5, SystemSettingFields.DefaultSystemValue.Name);
            fields.DefineField(SystemSettingOrganizationFields.SystemValue, 6,"OrganizationValue");
            fields.DefineField(SystemSettingUnitFields.SystemValue, 7, "UnitValue");

            IRelationPredicateBucket filter = new RelationPredicateBucket();

            filter.Relations.Add(SystemSettingEntity.Relations.SystemSettingOrganizationEntityUsingSystemSettingId, JoinHint.Left);
            filter.Relations.Add(SystemSettingEntity.Relations.SystemSettingUnitEntityUsingSystemSettingId, JoinHint.Left);

            filter.PredicateExpression.Add(new FieldCompareValuePredicate(SystemSettingFields.IsActive, null, ComparisonOperator.Equal, true));
            filter.PredicateExpression.AddWithAnd(new FieldCompareValuePredicate(SystemSettingFields.CanOverrideByUnitSetting, null, ComparisonOperator.Equal, true));
            filter.PredicateExpression.AddWithAnd(new FieldCompareValuePredicate(SystemSettingOrganizationFields.OrganizationId, null, ComparisonOperator.Equal, oId));

            DataTable results = new DataTable();

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchTypedList(fields, results, filter, true);
            }
            return results;
        }

But I'm still intrested in how this is done in LINQ. So if anyone has an idea. Please let me know.

TIA

This is an approximate code using LINQ. If the related fields are nullable, LLBL2LINQ will build he appropiate LEFT/RIGHT joins:

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);

    var query = from ss in metaData.SystemSetting

        where ss.IsActive == true
           && ss.CanOverrideByUnitSetting == true
           && ss.SystemSettingOrganization.OrganizationId == oId
        
                select new
                {
                    SystemSettingId = ss.SystemSettingId,
            Definition = ss.Definition,
            IsActive = ss.IsActive,
            SettingValueType = ss.SettingValueType,
            CanOverrideByUnitSetting = ss.CanOverrideByUnitSetting,
            DefaultSystemValue = ss.DefaultSystemValue,
            OrganizationSystemValue = ss.SystemSettingOrganization.SystemValue,
            UnitSystemValue = ss.SystemSettingUnit.SystemValue
                };      
}

For more info, read the Linq2LLBLgen documentation in the manual and the examples in your [LLBLGen Pro v2.6 Installation Folder]\Sourcecode\LinqUnitTests folder.

Thanx for the info quys.