How merge two sequences into one?

Question

I have some working code that retrieves data from data base. It is interesting for me to get some better code for my solution. Are there some ways to combine two queries into one or something like this?

Dim customerTitlesAndIDs = contex.CustomerTable.Select(Function(row) New
                           With {.ID = row.ID, .CustomerTitle = row.Title}).ToList()

Dim cutomerIdPayment = contex.CustomerPayments.Select(Function(table) New 
                       With
                       {
                           .ID = table.CustomerID,
                           .Range = table.PaymentsRange,
                           .Values = table.Values
                       }).ToList()

Dim customerInfos As New List(Of SCustomerInfo)

For Each customer In customerTitlesAndIDs

    Dim cID As Integer = customer.ID
    customerInfo.Add(New SCustomerInfo(CreateCustomerTable(), cID, customer.CustomerTitle))

    For Each cutomerPayments In cutomerIdPayment

        If cutomerPayments.ID = cID Then
            Dim rangeValue(1) As Object
            rangeValue(0) = cutomerPayments.Range
            rangeValue(1) = cutomerPayments.Values
            Dim dtRow As DataRow = customerInfos.Last().PaymentTable.NewRow()
            dtRow.ItemArray = rangeValue
            customerInfos.Last().PaymentTable.Rows.Add(dtRow)
        End If
    Next
Next

Return customerInfos

Same code with C# (hope no syntax errors occurred):

var customerTitlesAndIDs = contex.CustomerTable.Select(row => new
                           { .ID = row.ID, .CustomerTitle = row.Title }).ToList();

var cutomerIdPayment = contex.CustomerPayments.Select(table => new
                       {
                           .ID = table.CustomerID,
                           .Range = table.PaymentsRange,
                           .Values = table.Values
                       }).ToList();

List<SCustomerInfo> customerInfos = new List<SCustomerInfo>;

foreach (var customer in customerTitlesAndIDs)
{
    int cID = customer.ID;
    customerInfos.Add(new SCustomerInfo(CreateCustomerTable(), cID, customer.CustomerTitle));

    foreach (var cutomerPayments in cutomerIdPayment)
    {
        if (cutomerPayments.ID = cID)
        {
            object[] rangeValue = new object[1] {cutomerPayments.Range, cutomerPayments.Values};
            DataRow dtRow = customerInfos.Last().PaymentTable.NewRow();
            dtRow.ItemArray = rangeValue;

            customerInfos.Last().PaymentTable.Rows.Add(dtRow);
        }
    }
}

SCustomerInfo represented by folowing Structure (code is simplified):

Public Structure SWindAltitude
    Public PaymentTableAs DataTable
    Public Title As String
    Public ID As Integer
End Structure

Both C# and VB.NET solutions will be helpful.

Answer 1

Try something like this, utilizing navigation properties (you'll probably have to massage it as I don't know the exact makeup of your data structures):

var customerQuery = context.CustomerTable.Select( ct => 
    new { 
        ct.ID, 
        ct.CustomerTitle, 
        // use nav property to get customer payments
        CustomerPayments = ct.CustomerPayments.Select( cp => 
            new { 
                Range = cp.Range, 
                Values = cp.Values } ) } );

return customerQuery.ToArray()
    .Select( cq => 
        {
            var retVal = new SCustomerInfo( CreateCustomerTable(), cq.ID, cq.CustomerTitle ); 

            foreach( var customerPayment in cq.CustomerPayments )
            {
                var dtRow = cq.PaymentTable.NewRow();

                dtRow.ItemArray = new object[] { customerPayment.Range, customerPayment.Values };

                retVal.PaymentTable.Rows.Add( dtRow );
            }

            return retVal;
        } );