I am trying to link 3 tables together using LINQ, can someone have a look at where I am going wrong?

I have 3 tables like this

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))

Dim dtTable3 As New DataTable()
dtTable3.Columns.Add("Table3Id", GetType(System.Int32))
dtTable3.Columns.Add("Table2Id", GetType(System.Int32))
dtTable3.Columns.Add("JobEntityCount", GetType(System.String))

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)

dtTable3.Rows.Add(1, 1, 100)
dtTable3.Rows.Add(2, 1, 200)
dtTable3.Rows.Add(3, 1, 300)
dtTable3.Rows.Add(4, 3, 400)

And my Sql query was this:

SELECT COUNT(DISTINCT dbo.Table1.Table1Id) AS Table1IdCount, COUNT(DISTINCT dbo.Table2.Table2Id) AS Table2IdCount, dbo.Table2.JobType, SUM(dbo.Table2.JobValue) AS JobValue, SUM(Table3Summed.JobEntityCount) AS JobEntityCount
FROM dbo.Table1 
    INNER JOIN dbo.Table2 ON dbo.Table1.Table1Id = dbo.Table2.Table1Id 
    LEFT OUTER JOIN (  -- Create a summerised table
        SELECT Table2Id, SUM(JobEntityCount) AS JobEntityCount
        FROM dbo.Table3 AS Table3_1
        GROUP BY Table2Id
    ) AS Table3Summed ON dbo.Table2.Table2Id = Table3Summed.Table2Id
GROUP BY dbo.Table2.JobType

I have tried to do this:

'create summerised table3
Dim table3Summed = From dr In dtTable3.AsEnumerable()
                    Group dr By Table2Id = dr.Field(Of Integer)("Table2Id") Into drg = Group
                    Select New With {
                            .Table2Id = Table2Id,
                            .JobEntityCount = drg.Count()}

Dim result = (From Table1 In dtTable1.AsEnumerable
                    Join Table2 In dtTable2.AsEnumerable
    On Table1.Field(Of Integer)("Table1Id") Equals Table2.Field(Of Integer)("Table1Id")
                    Join Table3 In table3Summed
    On Table2.Field(Of Integer)("Table2Id") Equals Table3.Table2Id
                    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"))
    .JobEntityCount = table3Summed.JobEntityCount,
    })

Also, how do you do a LEFT OUTER JOIN in Linq?

I am trying to link 3 tables together using LINQ, can someone have a look at where I am going wrong?

I have 3 tables like this

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))

Dim dtTable3 As New DataTable()
dtTable3.Columns.Add("Table3Id", GetType(System.Int32))
dtTable3.Columns.Add("Table2Id", GetType(System.Int32))
dtTable3.Columns.Add("JobEntityCount", GetType(System.String))

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)

dtTable3.Rows.Add(1, 1, 100)
dtTable3.Rows.Add(2, 1, 200)
dtTable3.Rows.Add(3, 1, 300)
dtTable3.Rows.Add(4, 3, 400)

And my Sql query was this:

SELECT COUNT(DISTINCT dbo.Table1.Table1Id) AS Table1IdCount, COUNT(DISTINCT dbo.Table2.Table2Id) AS Table2IdCount, dbo.Table2.JobType, SUM(dbo.Table2.JobValue) AS JobValue, SUM(Table3Summed.JobEntityCount) AS JobEntityCount
FROM dbo.Table1 
    INNER JOIN dbo.Table2 ON dbo.Table1.Table1Id = dbo.Table2.Table1Id 
    LEFT OUTER JOIN (  -- Create a summerised table
        SELECT Table2Id, SUM(JobEntityCount) AS JobEntityCount
        FROM dbo.Table3 AS Table3_1
        GROUP BY Table2Id
    ) AS Table3Summed ON dbo.Table2.Table2Id = Table3Summed.Table2Id
GROUP BY dbo.Table2.JobType

I have tried to do this:

'create summerised table3
Dim table3Summed = From dr In dtTable3.AsEnumerable()
                    Group dr By Table2Id = dr.Field(Of Integer)("Table2Id") Into drg = Group
                    Select New With {
                            .Table2Id = Table2Id,
                            .JobEntityCount = drg.Count()}

Dim result = (From Table1 In dtTable1.AsEnumerable
                    Join Table2 In dtTable2.AsEnumerable
    On Table1.Field(Of Integer)("Table1Id") Equals Table2.Field(Of Integer)("Table1Id")
                    Join Table3 In table3Summed
    On Table2.Field(Of Integer)("Table2Id") Equals Table3.Table2Id
                    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"))
    .JobEntityCount = table3Summed.JobEntityCount,
    })

Also, how do you do a LEFT OUTER JOIN in Linq?

• vb.net
• linq
• group-by

• I'm not a VB expert, but here are a few comments. (1) Shouldn't JobEntityCount be Int32 instead of a String? (2) In your table3Summed code, shouldn't you be performing a Sum() instead of Count()? (3) In the Group Table2 By ... code, I think you need to also include table3Summed (or at least the .JobEntityCount element) in the group results. (Someone else may offer the proper VB LINQ syntax). (4) I think the final .JobEntityCount calculation needs to be something like .JobEntityCount = g.Sum(Function(item) item.table3Summed.JobEntityCount), but that will depend on (#3). – T N Commented Jan 20 at 3:43
• For others who know VB LINQ better than I, here is a .Net Fiddle and here is a db<>fiddle of the reference SQL that may be useful in building a solution. – T N Commented Jan 20 at 3:47

1 Answer 1

You could first group table 3, then join between 1 and 2 while left joining 3 grouping by job type. Finally you'll aggregate distinct counts of 1, 2, and the sum of the job values and entity counts, while watching for null values :-)

Dim table3Summed = 
    From t3 In dtTable3.AsEnumerable()
    Group t3 By Table2Id = t3.Field(Of Integer)("Table2Id")
    Into Group
    Select New With {
        .Table2Id = Table2Id,
        .JobEntityCount = Group.Sum(Function(x) Convert.ToInt32(x.Field(Of String)("JobEntityCount")))
    }

Dim query = From t1 In dtTable1.AsEnumerable()
    Join t2 In dtTable2.AsEnumerable()
    On t1.Field(Of Integer)("Table1Id") Equals t2.Field(Of Integer)("Table1Id")
    Group Join t3Summed In table3Summed 
    On t2.Field(Of Integer)("Table2Id") Equals t3Summed.Table2Id Into gj = Group
    From subgj In gj.DefaultIfEmpty()
    Group By JobType = t2.Field(Of Integer)("JobType")
    Into Group
    Select New With {
        .Table1IdCount = Group.Select(Function(x) x.t1).Distinct().Count(),
        .Table2IdCount = Group.Select(Function(x) x.t2).Distinct().Count(),
        .JobType = JobType,
        .JobValue = Group.Sum(Function(x) x.t2.Field(Of Decimal)("JobValue")),
        .JobEntityCount = Group.Sum(Function(x) If(subgj Is Nothing, 0, subgj.JobEntityCount))
    }