none
How to concate Master/Detail Columns RRS feed

  • Question

  • Hey

    I Need to put strings together from a Master/Detail Database query.

    Let's say, I have a Master/Detail table: Master: Customer -> table Detail: Articles

    So, each customer could have multiple articles.

    With Linq: I could do something like this:

    Dim results = From locCustomer In DataContext.Customer Join
    locArticles in DataContext.Articles on
    locCustomer.Id Equals locArticles.CustomerId
    Let Name = locCustomer.Name
    Detail = locArticles.ArticleName
    Select Name, Detail

    Then the result is not as expected, because only one ArticleName will come back and not all of them

    Means, this can handle only 1:1 joins but not 1:N joins.

    I would like to concat all ArticlesNames into the Detail property

    How to solve this Problem?

    THX a lot



    • Edited by Zero-G. _ Wednesday, January 9, 2019 12:15 PM
    Wednesday, January 9, 2019 12:13 PM

Answers

  • Here is another take using in this case Entity Framework in tangent with lambda. LazyLoadingEnabled means don't load navigation items e.g. orders is related to products for example and we don't want products and orders is related to customers and we (I assume) already have a customer record.

    Using context = New NorthWindEntities()
    	context.Configuration.LazyLoadingEnabled = False
    	Dim orders = context.Orders.Where(Function(ord) ord.CustomerIdentifier = 3).Select(Function(ord) New With {Key .OrderDate = ord.OrderDate.Value}).ToList()
    	Dim dates = orders.Select(Function(item) item).ToArray()
    End Using

    To load the customer without navigation items

    Using context = New NorthWindEntities()
    	context.Configuration.LazyLoadingEnabled = False
    	Dim cust = context.Customers.FirstOrDefault(Function(c) c.ContactIdentifier = 3)
    End Using

    The model this is from


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Zero-G. _ Wednesday, January 9, 2019 3:28 PM
    Wednesday, January 9, 2019 2:54 PM
    Moderator

All replies

  • Hello,

    In the following code sample I have read three tables, each are setup via a relationship which means no join is required. Each table is read into a DataTable and all three are housed in a DataSet which is setup with BindingSource components.

    To get orders (middle DataGridView) from the current Customer (top DataGridView)

    Private Sub ChildRowsToolStripButton1_Click(sender As Object, e As EventArgs) Handles ChildRowsToolStripButton1.Click
        If bsMaster.Current IsNot Nothing Then
            Dim results As DataRow() = CType(bsMaster.Current, DataRowView).Row.GetChildRows("CustomerOrders")
            If results.Count = 0 Then
                MessageBox.Show("No rows")
            Else
                Dim invoices = (From row In results Select New With {.Invoice = row.Field(Of String)("Invoice")}).ToArray
            End If
        End If
    End Sub

    The key here is having a relationship and working with GetChildRows of the customer table where orders is were we get the child rows. Results

    Anytime you are working with related tables that are in a container such as a DataSet (with the DataTables for the data) setup relationships.

    Here is a extension method for this.

    Public Module DataRelationsExtensions
        ''' <summary>
        ''' USed to create a one to many relationship for a master-detail in a DataSet.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="MasterTableName">master table</param>
        ''' <param name="ChildTableName">child table of master table</param>
        ''' <param name="MasterKeyColumn">master table primary key</param>
        ''' <param name="ChildKeyColumn">child table of master's primary key</param>
        <DebuggerStepThrough()>
        <Runtime.CompilerServices.Extension()>
        Public Sub SetRelation(
            sender As DataSet,
            MasterTableName As String,
            ChildTableName As String,
            MasterKeyColumn As String,
            ChildKeyColumn As String)
    
            sender.Relations.Add(
             New DataRelation(String.Concat(MasterTableName, ChildTableName),
                sender.Tables(MasterTableName).Columns(MasterKeyColumn),
                sender.Tables(ChildTableName).Columns(ChildKeyColumn)
             )
          )
    
        End Sub
    
    End Module
    

    Usage

    Public Sub LoadData()
    
        Dim ds As New DataSet
    
        Using cn As New SqlConnection With {.ConnectionString = _connectionString}
            Dim da As New SqlDataAdapter("SELECT id,FirstName,LastName,Address,City,State,ZipCode FROM Customer", cn)
            Try
                da.Fill(ds, "Customer")
                Dim dt As DataTable = ds.Tables("Customer")
    
                da = New SqlDataAdapter("SELECT id,CustomerId,OrderDate,Invoice FROM Orders", cn)
                da.Fill(ds, "Orders")
                ds.SetRelation("Customer", "Orders", "Id", "CustomerId")
    
                da = New SqlDataAdapter("SELECT id,OrderId ,ProductName,UnitPrice,Quantity FROM OrderDetails", cn)
                da.Fill(ds, "OrderDetails")
                ds.SetRelation("Orders", "OrderDetails", "Id", "OrderId")
    
                Master.DataSource = ds
                Master.DataMember = ds.Tables(0).TableName
    
                Details.DataSource = Master
                Details.DataMember = ds.Relations(0).RelationName
    
                OrderDetails.DataSource = Details
                OrderDetails.DataMember = ds.Relations(1).RelationName
    
                CustomerTable = ds.Tables("Customer")
    
            Catch ex As Exception
                HasErrors = True
                ExceptionMessage = ex.Message
            End Try
        End Using
    End Sub
    

    Full code samples

    MS-Access https://code.msdn.microsoft.com/Basics-of-manuall-creating-aa1a5c3d?redir=0 (VS2010)

    SQL-Server https://code.msdn.microsoft.com/-Detail-for-ListBoxs-and-d896b5ed?redir=0


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 9, 2019 1:01 PM
    Moderator
  • Hey

    Thx for your answer. But it does not fit my needs...

    In your example I would have to read all data from Database into my local RAM and then concate it.

    But I would Need it, that this is already done on LinQ Level, so that the resulting query to database has all the Things done already. - Otherwise I would have to load all my data, which would end up in an extreme Overhead.

    What I did not say in my first post, is that after the written Linq I will Change the "result" variable with a where clause so, that not all will get loaded.

    THX

    Wednesday, January 9, 2019 1:27 PM
  • Here is another take using in this case Entity Framework in tangent with lambda. LazyLoadingEnabled means don't load navigation items e.g. orders is related to products for example and we don't want products and orders is related to customers and we (I assume) already have a customer record.

    Using context = New NorthWindEntities()
    	context.Configuration.LazyLoadingEnabled = False
    	Dim orders = context.Orders.Where(Function(ord) ord.CustomerIdentifier = 3).Select(Function(ord) New With {Key .OrderDate = ord.OrderDate.Value}).ToList()
    	Dim dates = orders.Select(Function(item) item).ToArray()
    End Using

    To load the customer without navigation items

    Using context = New NorthWindEntities()
    	context.Configuration.LazyLoadingEnabled = False
    	Dim cust = context.Customers.FirstOrDefault(Function(c) c.ContactIdentifier = 3)
    End Using

    The model this is from


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Zero-G. _ Wednesday, January 9, 2019 3:28 PM
    Wednesday, January 9, 2019 2:54 PM
    Moderator