I am trying to join to tables together in vb.Net using linq to return the distinct count of the primary keys, and I can't quite seem to get the syntax

Here's the code to create the tables

    Dim dtTable1 As New DataTable()
    dtTable1.Columns.Add("Table1Id", GetType(System.Int32))
    dtTable1.Columns.Add("MainEntityName", GetType(System.String))

    Dim dtTable2 As New DataTable()
    dtTable2.Columns.Add("Table2Id", GetType(System.Int32))
    dtTable2.Columns.Add("Table1Id", GetType(System.Int32))
    dtTable2.Columns.Add("SubEntityName", GetType(System.String))
    dtTable2.Columns.Add("JobType", GetType(System.Int32))
    dtTable2.Columns.Add("JobValue", GetType(System.Decimal))

    dtTable1.Rows.Add(1, 'Main Entity 1 Name')
    dtTable1.Rows.Add(2, 'Main Entity 2 Name')

    dtTable2.Rows.Add(1, 1, 'Sub Entity 1 Name', 1, 1000)
    dtTable2.Rows.Add(2, 1, 'Sub Entity 2 Name', 1, 2000)
    dtTable2.Rows.Add(3, 1, 'Sub Entity 3 Name', 1, 3000)
    dtTable2.Rows.Add(4, 2, 'Sub Entity 4 Name', 2, 4000)
    
    I would like join both tables together and do a distinct count of Table1Id and Table2Id
    
    In Sql I use this:
    SELECT COUNT(DISTINCT dbo.Table1.Table1Id) AS Table1IdCount, COUNT(DISTINCT dbo.Table2.Table2Id) AS Table2IdCount, 
    dbo.Table2.JobType, SUM(dbo.Table2.JobValue) AS Expr1
    FROM dbo.Table1 
        INNER JOIN dbo.Table2 ON dbo.Table1.Table1Id = dbo.Table2.Table1Id
    GROUP BY dbo.Table2.JobType
    
    This returns:
    1   3   1   6000
    1   1   2   4000        
    
    
    I would like to do this using LinQ
    
    Dim result = (From Table1 In dtTable1.AsEnumerable
                  Join Table2 In dtTable2.AsEnumerable
         On Table1.Field(Of Integer)("Table1Id") Equals Table2.Field(Of Integer)("Table1Id")
                  Group Table2 By ID = Table2.Field(Of Integer)("JobType") Into g = Group
                  Select New With {
                      Key ID,
                      .Table1IdCount = g.Distinct.Count(),
                      .Table2IdCount = g.Distinct.Count(),  ??????????
                      .JobValue = g.Sum(Function(r) r.Field(Of Decimal)("JobValue"))
                 })

Any ideas?

I am trying to join to tables together in vb.Net using linq to return the distinct count of the primary keys, and I can't quite seem to get the syntax

Here's the code to create the tables

    Dim dtTable1 As New DataTable()
    dtTable1.Columns.Add("Table1Id", GetType(System.Int32))
    dtTable1.Columns.Add("MainEntityName", GetType(System.String))

    Dim dtTable2 As New DataTable()
    dtTable2.Columns.Add("Table2Id", GetType(System.Int32))
    dtTable2.Columns.Add("Table1Id", GetType(System.Int32))
    dtTable2.Columns.Add("SubEntityName", GetType(System.String))
    dtTable2.Columns.Add("JobType", GetType(System.Int32))
    dtTable2.Columns.Add("JobValue", GetType(System.Decimal))

    dtTable1.Rows.Add(1, 'Main Entity 1 Name')
    dtTable1.Rows.Add(2, 'Main Entity 2 Name')

    dtTable2.Rows.Add(1, 1, 'Sub Entity 1 Name', 1, 1000)
    dtTable2.Rows.Add(2, 1, 'Sub Entity 2 Name', 1, 2000)
    dtTable2.Rows.Add(3, 1, 'Sub Entity 3 Name', 1, 3000)
    dtTable2.Rows.Add(4, 2, 'Sub Entity 4 Name', 2, 4000)
    
    I would like join both tables together and do a distinct count of Table1Id and Table2Id
    
    In Sql I use this:
    SELECT COUNT(DISTINCT dbo.Table1.Table1Id) AS Table1IdCount, COUNT(DISTINCT dbo.Table2.Table2Id) AS Table2IdCount, 
    dbo.Table2.JobType, SUM(dbo.Table2.JobValue) AS Expr1
    FROM dbo.Table1 
        INNER JOIN dbo.Table2 ON dbo.Table1.Table1Id = dbo.Table2.Table1Id
    GROUP BY dbo.Table2.JobType
    
    This returns:
    1   3   1   6000
    1   1   2   4000        
    
    
    I would like to do this using LinQ
    
    Dim result = (From Table1 In dtTable1.AsEnumerable
                  Join Table2 In dtTable2.AsEnumerable
         On Table1.Field(Of Integer)("Table1Id") Equals Table2.Field(Of Integer)("Table1Id")
                  Group Table2 By ID = Table2.Field(Of Integer)("JobType") Into g = Group
                  Select New With {
                      Key ID,
                      .Table1IdCount = g.Distinct.Count(),
                      .Table2IdCount = g.Distinct.Count(),  ??????????
                      .JobValue = g.Sum(Function(r) r.Field(Of Decimal)("JobValue"))
                 })

Any ideas?

• linq
• group-by
• datatable
• count
• enumeration

1 Answer 1

The problem with

.Table1IdCount = g.Distinct.Count(),

is that will just select the count of the grouped records, so you need to change this to select just the field you want from the group and then count the distinct values of that field , i.e. something like

.Table1IdCount = (From x In g Select x.Field(Of Integer)("Table1Id")).Distinct.Count() 

so the full query could be something like :

Dim result = (From Table1 In dtTable1.AsEnumerable
              Join Table2 In dtTable2.AsEnumerable
              On Table1.Field(Of Integer)("Table1Id") Equals Table2.Field(Of Integer)("Table1Id")
              Group Table2 By ID = Table2.Field(Of Integer)("JobType") Into g = Group
              Select New With 
              {
               .Table1IdCount = (From x In g Select x.Field(Of Integer)("Table1Id")).Distinct.Count() ,
               .Table2IdCount = (From x In g Select x.Field(Of Integer)("Table2Id")).Distinct.Count(),
               .JobType = ID,
               .JobValue = g.Sum(Function(r) r.Field(Of Decimal)("JobValue"))
              })
 
               

I've changed the field order to get results similar to your sql query.