What approach (best practices) is recommended in the following scenario:

Table 1 = Service - ServiceId - ServiceName

Table 2 = ServiceConfig - ServiceConfigId - ServiceId - ServiceParamName - ServiceParamValue

Table 2 will have many name/value pairs for each service. How can we display this with Table1 rows, where the param name = column name, and the param value = the column value such that we will have a horizontal record merged with Table 1?

i.e. a row like:

ServiceName, ParamValue1, ParamValue2, ParamValue3, etc.

Using Adapter.

Thanks!

1st you need to figure out how many columns you need. determine this with a query like

select top 1 count(ServiceConfigId) 
from ServiceConfig 
Group By ServiceId 
Order By count(ServiceConfigId) desc

IEntityFieldCore field = ServiceConfigFields.ServiceConfigId.SetAggregateFunction(Aggregate.Count);

ISortExpression sort = new SortExpression(field | SortOperator.Descending);

IGroupByCollection group = new GroupByCollection();
group.Add(ServiceConfigFields.ServiceId);

ScalarQueryExpression scalar = new ScalarQueryExpresssion(field, null, null, sort, group)

int NumberOfParams = 0;
using(IDataAccessAdatper adapter = new DataAccessAdapter)
{
     object result = adapter.GetScalar(scalar, Aggregate.None);
     if (result != DBNull.Value)
     {
         NumberOfParams = (int)result;
     }
}

this will allow you to build a DataTable

DataTable t = new DataTable();
t.Columns.Add("ServiceName", typeof(string));
for (int i = 0; i <= NumberOfParams; i++)
{
    t.Columns.Add("ParamValue" + i.ToString(), typeof(string));
}

now you need to get the data from database and compile this into the custom table. getting this data could be done with a typedlist, dynamic list or entitycollection. for simplicity I'll go with entity collection.

EntityCollection<ServiceEntity> services = new EntityCollection<ServiceEntity>();

IRelationPredicateBucket bucket = ServiceEntity.GetRelationBucketForServiceConfig();

ISortExpression sort = new SortExpression();
sort.Add(ServiceFields.ServiceName | SortOperator.Ascending);
sort.Add(ServiceConfigFields.ParamValue | SortOperator.Ascending);

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ServiceEntity);
prefetchPath.Add(ServiceEntity.PrefetchPathServiceConfig);

using(IDataAccessAdatper adapter = new DataAccessAdapter)
{
     adapter.FetchCollection(services, bucket, o, sort, prefetchPath);
}

now we cycle through the collection filling the data table

DataRow row;
foreach(ServiceEntity service in services)
{
    row = t.NewRow();
    row[0] = service.ServiceName;

    int columnIndex = 1; //2nd column
    foreach(ServiceConfigEntity config in serivce.SerivceConfig)
    {
          row[columnIndex] = config.ParameterValue;
    }

    t.Rows.Add(row);
}

now you can use DataTable t however you need to.