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,307
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
 
Top Bottom