• I removed the www from the URL, please update your bookmarks and pay attention to the bottom of the screen if using Google Chrome to allow push notifications again.

Question Linq join typed to untyped help with group by and count

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
I am trying to write a linq query and having a fit trying to figure it out. Am not very good with linq, just the basics. I code in vb. So I created a datatable in code, then want to write a query that joins it to a typed datatable. Here is what i have so far:
Code:
        Dim dt As New DataTable
        dt.Columns.Add("cctrid", gettype(integer))

       .......Add some rows to dt here.

        Dim query = From a In dt
                    Join b In EMS_DS.TEMSCCTR
                    On a.cctrid Equals b.cctr_id
                     Group By a.cctrid Into cctrcnt = Count(a.cctrid)
                     Select a.cctrid, cctrcnt

         cctrgc.datasource=query
    End Sub
I have tried a.field(of integer)("cctrid") and many other things, but I keep getting a type expected error.

Please help.
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,277
Location
Norway
Programming Experience
10+
I have tried a.field(of integer)("cctrid")
That would be correct, but Linq needs a 'name', so assign it to a variable, and use that later in query:
Let a_cctrid = a.Field(Of Integer)("cctrid")
 

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
Where do i put that, in the join or group by?
 

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
i messed up in the first place

Code:
        Dim query = From a In dt
                    Join b In EMS_DS.TEMSCCTR
                    On a.cctrid Equals b.cctr_id
                     Group By a.cctrid Into cctrcnt = Count(a.cctrid)
                     Select b.cctr_cd, cctrcnt
But where do i use what you said?
 

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
Is it this:
Code:
        Dim query = From a In dt
                    Let a_cctrid = a.Field(Of Integer)("cctrid")
                    Join b In EMS_DS.TEMSCCTR
                    On a_cctrid Equals b.cctr_id
                    Group a_cctrid By cctr = b.cctr_cd, cctrcnt = a_cctrid Into Group
                    Select New With
                           {
                               .cctr = cctr,
                               .cctrcnt = Group.Count()
                           }
Still get type expected
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,277
Location
Norway
Programming Experience
10+
I don't see the problem.
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,277
Location
Norway
Programming Experience
10+
I don't see the problem.
Here I kind of replicated the code and didn't get any error from compiler. I also didn't see any other problems visually with what you posted. I still don't see the problem or where it could be.
 
Top Bottom