locked
Create a new List from two collections using LINQ expression in VB.NET RRS feed

  • Question

  • User1909155429 posted
    I want to return another List from two collections as shown. The task is to combine the two on VendorID and calculate a new price. The lists are not static and can enlarge in size.
    
    IList<Product> CartList = new List<Product>() {
    new Product() { VendorID = 1, Email = "John@yahoo", Price = 130,Name ="john" },
    new Product() { VendorID = 1, Email = "john@yahoo", Price = 210,Name="john" },
    new Product() { VendorID = 2, Email = "Bill@yahoo", Price = 180,Name ="Bill" },
    new Product() { VendorID = 3, Email = "Ram@yahoo" , Price = 200,Name="Ram" }
    
    };
    
    IList<Funds> Funding = new List<Funds>() {
    new Funds(){ VendorID = 1, Funds=30},
    new Funds(){ VendorID = 2, Funds=40}
    
    };
    
    Result: Vendor        Email                    Price
             1          John@yahoo             310 (ie 340-30)
             2          Bill@yahoo              140 (ie 180-40)
             3          Ram@yahoo               200
     
     

    Tuesday, April 20, 2021 8:45 PM

Answers

  • User475983607 posted

    If the C# to VB.NET converter does not work for you then take the time, like all of us, to learn the syntax by reading the reference documentation.

    Imports System.Data
    
    Public Class Product
        Public Property Email As String
        Public Property VendorId As Integer
        Public Property Name As String
        Public Property Price As Decimal
    End Class
    
    Public Class Fund
        Public Property VendorId As Integer
        Public Property Funds As Decimal
    End Class
    
    Module Program
        Sub Main(args As String())
            Dim CartList As New List(Of Product) From
            {
                New Product With {.VendorId = 1, .Email = "John@yahoo", .Price = 130, .Name = "john"},
                New Product With {.VendorId = 1, .Email = "John@yahoo", .Price = 210, .Name = "john"},
                New Product With {.VendorId = 2, .Email = "Bill@yahoo", .Price = 180, .Name = "Bill"},
                New Product With {.VendorId = 3, .Email = "Ram@yahoo", .Price = 200, .Name = "Ram"}
            }
    
            Dim Funding As New List(Of Fund) From
            {
                New Fund With {.VendorId = 1, .Funds = 30},
                New Fund With {.VendorId = 2, .Funds = 40}
            }
    
            Dim q = From p In CartList
                    Group By p.VendorId, p.Email, p.Name
                        Into CartListGroup = Group, Sum(p.Price)
                    Group Join f In Funding On f.VendorId Equals VendorId
                        Into FundingGroup = Group
    
            Console.WriteLine($"Vendor{vbTab}Email{vbTab}{vbTab}Price")
            For Each p In q
                Console.WriteLine($"{p.VendorId}{vbTab}{p.Email}{vbTab}{p.Sum - p.FundingGroup.Sum(Function(x) x.Funds)}")
            Next
    
        End Sub
    
    End Module
    

    Results

    Vendor  Email           Price
    1       John@yahoo      310
    2       Bill@yahoo      140
    3       Ram@yahoo       200

    Reference documentation.

    https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/queries/group-by-clause

    https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/linq/how-to-combine-data-with-linq-by-using-joins

    https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/queries/aggregate-clause

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 21, 2021 8:45 PM
  • User-1330468790 posted

    Hi peterthegreat,

     

    I am afraid that you cannot use online C#-VB converters to convert LINQ since they are not able to handle LINQ stuff, so this must be frustrating for you.

    @mgebhard's solution is much more cleaner with spliting the codes into two part - one is to get group and another one to calculate the final result. However, if you want to get the result in one LINQ statement, you could still refer to below LINQ in Console App. (Console App documentation (VB) click here)

    All codes (with models for Products, Result and Funds):

    Module Module1
    
        Sub Main()
            Dim CartList As IList(Of Product) = New List(Of Product)() From {
            New Product() With {
                .VendorID = 1,
                .Email = "John@yahoo",
                .Price = 130,
                .Name = "john"
            },
            New Product() With {
                .VendorID = 1,
                .Email = "John@yahoo",
                .Price = 210,
                .Name = "john"
            },
            New Product() With {
                .VendorID = 2,
                .Email = "Bill@yahoo",
                .Price = 180,
                .Name = "Bill"
            },
            New Product() With {
                .VendorID = 3,
                .Email = "Ram@yahoo",
                .Price = 200,
                .Name = "Ram"
            }
        }
            Dim Funding As IList(Of Funds) = New List(Of Funds)() From {
            New Funds() With {
                .VendorID = 1,
                .Fund = 30
            },
            New Funds() With {
                .VendorID = 2,
                .Fund = 40
            }
        }
    
    
            Dim q = From p In CartList
                    Group p By Key = New With {Key .var1 = p.VendorID, .var2 = p.Email} Into g = Group
                    Group Join f In Funding
                       On Key.var1 Equals f.VendorID Into pf = Group
                    From re In pf.DefaultIfEmpty()
                    Select New Result With {.VendorID = Key.var1, .Email = Key.var2, .Price = g.Sum(Function(p) p.Price) - If(pf.FirstOrDefault() Is Nothing, 0, pf.FirstOrDefault().Fund)}
    
    
    
            Console.WriteLine("Result:")
    
            For Each item In q
                Console.WriteLine($"VendorID : {item.VendorID}, Email : {item.Email}, Price : {item.Price}")
    
            Next
    
            Console.ReadLine()
        End Sub
    
        Public Class Result
            Public Property VendorID As Integer
            Public Property Email As String
            Public Property Price As Integer
        End Class
    
        Public Class Product
            Public Property VendorID As Integer
            Public Property Email As String
            Public Property Price As Integer
            Public Property Name As String
        End Class
    
        Public Class Funds
            Public Property VendorID As Integer
            Public Property Fund As Integer
        End Class
    
    End Module

    Result is still the same.

      

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 22, 2021 2:57 AM

All replies

  • User-1330468790 posted

    Hi peterthegreat,

     

    You can refer to below LINQ to get a list of Result which can be tested in Console App. 

    Just a reminder, I guess you will sum the prices by "VendorID" and "Email" but there is a mismatch in your sample data. Apart from that, the Funds property of Funds class will cause an error as the property name should not be the same as the enclosed type. Hence, I made some modifications in the codes.

      

    Linq Test:

    You might find that here the outer join is implemented by using DefaultIfEmpty. => refer to this Left Join

    IList<Product> CartList = new List<Product>() {
                    new Product() { VendorID = 1, Email = "John@yahoo", Price = 130,Name ="john" },
                    new Product() { VendorID = 1, Email = "John@yahoo", Price = 210,Name="john" },
                    new Product() { VendorID = 2, Email = "Bill@yahoo", Price = 180,Name ="Bill" },
                    new Product() { VendorID = 3, Email = "Ram@yahoo" , Price = 200,Name="Ram" }
                };
    
                IList<Funds> Funding = new List<Funds>() {
                    new Funds(){ VendorID = 1, Fund=30},
                    new Funds(){ VendorID = 2, Fund=40}
                };
    
    
                var q = (from p in CartList
                        group p by new
                        {
                            p.VendorID,
                            p.Email
                        } into g
                        join f in Funding on g.Key.VendorID equals f.VendorID into pf
                        from re in pf.DefaultIfEmpty()
                        select new Result { VendorID = g.Key.VendorID, Email = g.Key.Email, Price = g.Sum(p=>p.Price) - (re==null?0:re.Fund) })
                        .ToList();
    
    
                Console.WriteLine("Result:");
                foreach(var item in q)
                {
                    
                    Console.WriteLine($"VendorID : {item.VendorID}, Email : {item.Email}, Price : {item.Price}");
    
                }
    
    
                Console.ReadLine();

    Result:

     

    Hope helps.

    Best regards,

    Sean

    Wednesday, April 21, 2021 3:20 AM
  • User1909155429 posted

    That's fantastic. You know your stuff!

    Two further questions:

    I am using VB.NET so could you convert into vb instead. And when you refer to console app is that a third party addition? 

    I can never seem to obtain output whenever i browse console window in dev tools?

    Wednesday, April 21, 2021 12:31 PM
  • User475983607 posted

    A Console application is a standard project found in Visual Studio.  Every development environment has the ability to create a command line application like a console app.  Command line apps are usually the first application type every beginning level developer learns.  

    https://docs.microsoft.com/en-us/visualstudio/ide/quickstart-visual-basic-console?view=vs-2019

    A console application is a great tool for testing an idea or library without the need to build an entire web application.  It is quick and easy. I recommend that you set aside time to learn the technology.

    peterthegreat

    I can never seem to obtain output whenever i browse console window in dev tools?

    You must understand the difference between code running on the server and code (JavaScript) running in a browser.  Only code running in the browser can output to the dev tools console.   

    Wednesday, April 21, 2021 12:57 PM
  • User1909155429 posted

    Thanks for the advice.

    The code you sent is written in C# i am operating in VB.NET. So could you convert it into VB.NET instead. There is a different syntax!

    Wednesday, April 21, 2021 3:06 PM
  • User475983607 posted

    Thanks for the advice.

    The code you sent is written in C# i am operating in VB.NET. So could you convert it into VB.NET instead. There is a different syntax!

    Use a C# to VB.NET converter.

    https://converter.telerik.com/

    You should learn C#, as you have experienced, most code examples are in C#.  I, as well as many other .NET developers, moved from VB to C# long ago because of this.  The transition is not difficult and does not take very long - a few weeks. 

    Wednesday, April 21, 2021 3:30 PM
  • User-474980206 posted

    also vb.net is not supported in asp.net core and beyond. you should start the migration.

    Wednesday, April 21, 2021 3:57 PM
  • User1909155429 posted

    Thanks for the advice. Though my problem is here and now. I am using the standard ASP.NET version.

    I tried the convertor and output is incorrect. No easy fix i guess?

    I have changed g Into to Group but still the Result variable is not recognised? It also omits the funding list.

      Dim q = (From p In CartList Group p By __groupByKey1__ = New With {p.VendorID, p.Email
            } Into Group 
                     Select New Result With {
                .VendorID = g.Key.VendorID,
                .Email = g.Key.Email,
                .Price = g.Sum(Function(p) p.Price) - (If(re Is Nothing, 0, re.Fund))
            }).ToList()

    Wednesday, April 21, 2021 7:20 PM
  • User475983607 posted

    If the C# to VB.NET converter does not work for you then take the time, like all of us, to learn the syntax by reading the reference documentation.

    Imports System.Data
    
    Public Class Product
        Public Property Email As String
        Public Property VendorId As Integer
        Public Property Name As String
        Public Property Price As Decimal
    End Class
    
    Public Class Fund
        Public Property VendorId As Integer
        Public Property Funds As Decimal
    End Class
    
    Module Program
        Sub Main(args As String())
            Dim CartList As New List(Of Product) From
            {
                New Product With {.VendorId = 1, .Email = "John@yahoo", .Price = 130, .Name = "john"},
                New Product With {.VendorId = 1, .Email = "John@yahoo", .Price = 210, .Name = "john"},
                New Product With {.VendorId = 2, .Email = "Bill@yahoo", .Price = 180, .Name = "Bill"},
                New Product With {.VendorId = 3, .Email = "Ram@yahoo", .Price = 200, .Name = "Ram"}
            }
    
            Dim Funding As New List(Of Fund) From
            {
                New Fund With {.VendorId = 1, .Funds = 30},
                New Fund With {.VendorId = 2, .Funds = 40}
            }
    
            Dim q = From p In CartList
                    Group By p.VendorId, p.Email, p.Name
                        Into CartListGroup = Group, Sum(p.Price)
                    Group Join f In Funding On f.VendorId Equals VendorId
                        Into FundingGroup = Group
    
            Console.WriteLine($"Vendor{vbTab}Email{vbTab}{vbTab}Price")
            For Each p In q
                Console.WriteLine($"{p.VendorId}{vbTab}{p.Email}{vbTab}{p.Sum - p.FundingGroup.Sum(Function(x) x.Funds)}")
            Next
    
        End Sub
    
    End Module
    

    Results

    Vendor  Email           Price
    1       John@yahoo      310
    2       Bill@yahoo      140
    3       Ram@yahoo       200

    Reference documentation.

    https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/queries/group-by-clause

    https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/linq/how-to-combine-data-with-linq-by-using-joins

    https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/queries/aggregate-clause

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 21, 2021 8:45 PM
  • User-1330468790 posted

    Hi peterthegreat,

     

    I am afraid that you cannot use online C#-VB converters to convert LINQ since they are not able to handle LINQ stuff, so this must be frustrating for you.

    @mgebhard's solution is much more cleaner with spliting the codes into two part - one is to get group and another one to calculate the final result. However, if you want to get the result in one LINQ statement, you could still refer to below LINQ in Console App. (Console App documentation (VB) click here)

    All codes (with models for Products, Result and Funds):

    Module Module1
    
        Sub Main()
            Dim CartList As IList(Of Product) = New List(Of Product)() From {
            New Product() With {
                .VendorID = 1,
                .Email = "John@yahoo",
                .Price = 130,
                .Name = "john"
            },
            New Product() With {
                .VendorID = 1,
                .Email = "John@yahoo",
                .Price = 210,
                .Name = "john"
            },
            New Product() With {
                .VendorID = 2,
                .Email = "Bill@yahoo",
                .Price = 180,
                .Name = "Bill"
            },
            New Product() With {
                .VendorID = 3,
                .Email = "Ram@yahoo",
                .Price = 200,
                .Name = "Ram"
            }
        }
            Dim Funding As IList(Of Funds) = New List(Of Funds)() From {
            New Funds() With {
                .VendorID = 1,
                .Fund = 30
            },
            New Funds() With {
                .VendorID = 2,
                .Fund = 40
            }
        }
    
    
            Dim q = From p In CartList
                    Group p By Key = New With {Key .var1 = p.VendorID, .var2 = p.Email} Into g = Group
                    Group Join f In Funding
                       On Key.var1 Equals f.VendorID Into pf = Group
                    From re In pf.DefaultIfEmpty()
                    Select New Result With {.VendorID = Key.var1, .Email = Key.var2, .Price = g.Sum(Function(p) p.Price) - If(pf.FirstOrDefault() Is Nothing, 0, pf.FirstOrDefault().Fund)}
    
    
    
            Console.WriteLine("Result:")
    
            For Each item In q
                Console.WriteLine($"VendorID : {item.VendorID}, Email : {item.Email}, Price : {item.Price}")
    
            Next
    
            Console.ReadLine()
        End Sub
    
        Public Class Result
            Public Property VendorID As Integer
            Public Property Email As String
            Public Property Price As Integer
        End Class
    
        Public Class Product
            Public Property VendorID As Integer
            Public Property Email As String
            Public Property Price As Integer
            Public Property Name As String
        End Class
    
        Public Class Funds
            Public Property VendorID As Integer
            Public Property Fund As Integer
        End Class
    
    End Module

    Result is still the same.

      

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 22, 2021 2:57 AM
  • User1909155429 posted

    That does the trick, thankyou to both contributors. 

    I was wondering about the Result variable. You made another class property. Is it possible to insert the Result property values into another collection from with the statement body by using a Tolist method for example?

    Thursday, April 22, 2021 8:30 PM
  • User1909155429 posted

    Brilliant answer!

    I have been looking at LINQ TO SQL file creation. 

    Is this the right procedure for extracting data from database objects using LINQ query, since i am already using ADO.NET in my project to retrieve results set from database. I thought of mixing with LINQ to retrieve data also, to practice and gain experience?

    Thanks again

    Thursday, April 22, 2021 8:40 PM
  • User475983607 posted

    I was wondering about the Result variable. You made another class property. Is it possible to insert the Result property values into another collection from with the statement body by using a Tolist method for example?

    Share your source code.  Explain the expected results and the actual results.  

    Hint: both examples have a For Each loop where that can easily be repurposed to populate a collection.

    Thursday, April 22, 2021 8:44 PM