dcsimg
Results 1 to 11 of 11

Thread: code conversion

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2016
    Posts
    45

    code conversion

    i want to get list (of datarow) as a resualt of linq .join query
    i googled and got this code in C#
    but can not convert it to vb.net code
    can any one help? thanks

    HTML Code:
    dt1.AsEnumerable()
        .Join(dt2.AsEnumerable(),
            d => d.Field<string>("col1D"),
            d => d.Field<string>("col2A"),
            (d1, d2) => new { Dt1 = d1, Dt2 = d2 })
        .Select(a => {
                DataRow newRow = dt3.NewRow();
                newRow.SetField("colA", a.Dt1.Field<int>("col1A"));
                newRow.SetField("colB", a.Dt1.Field<string>("col1B"));
                newRow.SetField("colC", a.Dt1.Field<string>("col1C"));
                newRow.SetField("colD", a.Dt2.Field<string>("col2B"));
                return newRow;
            })
        .CopyToDataTable(dt3, LoadOption.OverwriteChanges);

  2. #2
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    655

    Re: code conversion

    Functional programming has made 'spaghetti lambda code' more common - the VB equivalent is:

    Code:
    dt1.AsEnumerable()
    	.Join(dt2.AsEnumerable(),
    	Function(d) d.Field(Of String)("col1D"),
    	Function(d) d.Field(Of String)("col2A"),
    	Function(d1, d2) New With {
    		Key .Dt1 = d1,
    		Key .Dt2 = d2
    	}).Select(Function(a)
    		Dim newRow As DataRow = dt3.NewRow()
    		newRow.SetField("colA", a.Dt1.Field(Of Integer)("col1A"))
    		newRow.SetField("colB", a.Dt1.Field(Of String)("col1B"))
    		newRow.SetField("colC", a.Dt1.Field(Of String)("col1C"))
    		newRow.SetField("colD", a.Dt2.Field(Of String)("col2B"))
    		Return newRow
    	End Function).CopyToDataTable(dt3, LoadOption.OverwriteChanges)
    David Anton
    Convert between VB, C#, C++, & Java
    www.tangiblesoftwaresolutions.com

  3. #3
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    901

    Re: code conversion

    Quote Originally Posted by David Anton View Post
    Functional programming has made 'spaghetti lambda code' more common - the VB equivalent is:
    Lambda coding for me is a knightmare, i personally dont like it in my code but seem to find a use for it when im stuck (usually someone offers a lanbda snippet to solve my problem )..... its really confusing to use i find....
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,679

    Re: code conversion

    Lambdas are nothing more than inline functions.
    This: Function(d) d.Field(Of String)("col1D") is an example of one.
    This:
    Code:
    Function(a)
    		Dim newRow As DataRow = dt3.NewRow()
    		newRow.SetField("colA", a.Dt1.Field(Of Integer)("col1A"))
    		newRow.SetField("colB", a.Dt1.Field(Of String)("col1B"))
    		newRow.SetField("colC", a.Dt1.Field(Of String)("col1C"))
    		newRow.SetField("colD", a.Dt2.Field(Of String)("col2B"))
    		Return newRow
    is another... they could easily be separate functions on their own... and you could call them as a function and not have any lambdas at all.

    Now, the stuff like the .Join, .Select, and the .CopyToDataTable... those are extensions and are part of LINQ.

    For me, that seems to be where the complexity comes in more so than with lambas.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,580

    Re: code conversion

    I'm not really sure how that code even works? Part of the problem is it used an incomprehensible "keys are precious" naming scheme and I don't think many of the types are right. I had to make my own project to test it out, so we may as well talk about it as we translate it to VB.

    Lambdas can make code very concise, but you have to be used to them. VB doesn't do you a lot of favors, its keyword syntax is clunky compared to arrow syntax. Part of keeping things straight is adopting very consistent indentation, another thing the VB IDE kind of works against. Let's not get into a philosophical argument there.

    I'm not familiar with LINQ's Join() method, so I had to write an example project to make sure I understood what the original does. The original author of your code didn't do you many favors: my hunch is they used "Dt" for "DataTable" in many places where they're actually manipulating a DataRow. It's always a bad sign when your original author doesn't seem to know what they were doing.

    See, what I mean is the results of the Join is an anonymous type that would look like this if it had a name:
    Code:
    class Anonymous {
        public DataRow Dt1 { get; set; }
        public DataRow Dt2 { get; set; }
    }
    That confounded me when I was trying to figure out what was going on here. Personally, instead of trying to mechanically translate that original code, I'd write some helper methods to get there in a more clear fashion.

    What's the code REALLY doing is an important thing to know. Not just here, at all times. Once I figured out the names were stupid, I was able to figure out what it really does:
    Make a DataTable that consists of columns A, B, and C from the first table and column D from the second table, matching rows where the first table's column D matches the second table's column A.
    Some of the cruft has to exist, but I think if we make helper methods and shy away from anonymous types everything gets more clear and more maintainable. Here's what I mean. Life gets easier if we use classes, the tools of VB code organization, instead of
    Code:
    Public Class TableManipulator
    
        Public Sub GetJoinedData(table1 As DataTable, table2 As DataTable, resultTable As DataTable)
            Dim joinedStream = JoinTables(table1, table2)
            Dim rows = joinedStream.Select(Function(data)
                    Return ConvertToRow(data, resultTable)
                End Function)
    
            rows.CopyToDataTable(resultTable, LoadOption.OverwriteChanges)
        End Function
    
        Private Function JoinTables(table1 As DataTable, table2 As DataTable) As IEnumerable(Of JoinedData)
            Dim table1Stream = table1.AsEnumerable()
            Dim table2Stream = table2.AsEnumerable()
            Return table1Stream.Join(
                table2Stream,
                Function(row) row("col1D").ToString(),
                Function(row) row("col2A").ToString(),
                Function(table1Row, table2Row) New JoinedData() With {
                    .Table1Row = table1Row,
                    .Table2Row = table2Row
                })
        End Function
    
        Private Function ConvertToRow(data As JoinedData, table As DataTable) As DataRow
            Dim result = table.NewRow()
            data.PopulateRow(result)
            Return result
        End Function
    
        Private Class JoinedData
            Public Property Table1Row As DataRow
            Public Property Table2Row As DataRow
    
            Public Sub PopulateRow(ByVal row As DataRow)
                row.SetField("colA", GetA())
                row.SetField("colB", GetB())
                row.SetField("colC", GetC())
                row.SetField("colD", GetD())
            End Sub
    
            Private Function GetA() As Integer
                Return Table1Row.Field(Of Integer)("col1A")
            End Function
    
            Private Function GetB() As String
                Return Table1Row.Field(Of String)("col1B")
            End Function
    
            Private Function GetC() As String
                Return Table1Row.Field(Of String)("col1C")
            End Function
    
            Private Function GetD() As String
                Return Table2Row.Field(Of String)("col2D")
            End Function
    
        End Class
    End Class
    Some people are going to gasp and whine about how many lines were added. I feel like this is much easier to read and reckon out.

    If you were using an object-based database model instead of a DataTable-based database model, it might be a little easier. We could write:
    Code:
    Public Function JoinData(table1 As IEnumerable(Of Table1Data), table2 As IEnumerable(Of Table2Data)) As IEnumerable(Of JoinedData)
        Return table1.Join(
            table2,
            Function(t1) t1.D
            Function(t2) t2.A
            Function(t1, t2) New JoinedData() With {
                .A = t1.A,
                .B = t1.B,
                .C = t1.C,
                .D = t2.D
            End Function)
    End Function
    
    Private Class JoinedData
        Public Property A As Integer
        Public Property B As String
        Public Property C As String
        Public Property D As String
    End Class
    But as you can see, DataTables are "much easier" than class-based designs.

    Really the smartest thing in this situation would be, "Do the JOIN in SQL."
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,679

    Re: code conversion

    Personally I found it confusing too... I just merely chimed in that Lambdas and LINQ don't necessarily need to be some monstrous black box that you don't understand... How ever, like any good tool, it can be easily misused and create really egregiously bad code. My guess is that this is how the original author (not the OP) learned how to do a join, and that's the way they've done it ever since, because "it just works" even though it isn't the best way. Personally I would have pulled it out using SQL if I could... if not, I might have tossed both into a dataset, created a DataRelation, then pulled the data back out of that...


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Member
    Join Date
    Feb 2016
    Posts
    45

    Re: code conversion

    thanks all i will give it a try and return

  8. #8

    Thread Starter
    Member
    Join Date
    Feb 2016
    Posts
    45

    Re: code conversion

    thank you David that is exactlly what i want

  9. #9

    Thread Starter
    Member
    Join Date
    Feb 2016
    Posts
    45

    Re: code conversion

    thanks Sitten your workAround is good but it need some work to be not specific for these two tables

  10. #10

    Thread Starter
    Member
    Join Date
    Feb 2016
    Posts
    45

    Re: code conversion

    good job
    thank you all

  11. #11

    Thread Starter
    Member
    Join Date
    Feb 2016
    Posts
    45

    Re: code conversion

    this is what i ended with Sitten Spynne code

    HTML Code:
    Public Class TableManipulator
        Public Function GetJoinedData(table1 As IEnumerable(Of DataRow), table2 As IEnumerable(Of DataRow), resultTable As DataTable, PKeyName As String, FkeyName As String) As IEnumerable(Of DataRow)
            Dim joinedStream = JoinTables(table1, table2, PKeyName, FkeyName)
            Dim rows = joinedStream.Select(Function(data)
                                               Return ConvertToRow(data, resultTable)
                                           End Function)
    
            'rows.CopyToDataTable(resultTable, LoadOption.OverwriteChanges)
            Return rows
        End Function
    
        Private Function JoinTables(table1Stream As IEnumerable(Of DataRow), table2Stream As IEnumerable(Of DataRow), PKeyName As String, FkeyName As String) As IEnumerable(Of JoinedData)
            'Dim table1Stream = table1.AsEnumerable()
            'Dim table2Stream = table2.AsEnumerable()
            Return table1Stream.Join(
                table2Stream,
                Function(row) row(PKeyName).ToString(),
                Function(row) row(FkeyName).ToString(),
                Function(table1Row, table2Row) New JoinedData() With {
                    .Table1Row = table1Row,
                    .Table2Row = table2Row
                })
        End Function
    
        Private Function ConvertToRow(data As JoinedData, table As DataTable) As DataRow
            Dim result = table.NewRow()
            data.PopulateRow(result)
            Return result
        End Function
    
        Private Class JoinedData
            Public Property Table1Row As DataRow
            Public Property Table2Row As DataRow
    
            Public Sub PopulateRow(ByVal row As DataRow)
                For Each dc As DataColumn In row.Table.Columns
                    If Table1Row.Table.Columns.Contains(dc.ColumnName) Then
                        row.SetField(dc.ColumnName, Table1Row(dc.ColumnName))
                    ElseIf Table2Row.Table.Columns.Contains(dc.ColumnName) Then
                        row.SetField(dc.ColumnName, Table2Row(dc.ColumnName))
                    Else
                    End If
                Next
            End Sub
    
        End Class
    End Class

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width