Linq grouping

[Bigbacon]

I am having a hell of a time trying to figue out groupings on data tables with LINQ. I am using VB

I have two data tables. I'll define from

Table Law = columns (ven_grp,comp_no,acct_unit,description,company)

Table Comps = columns(comp_parent,comp_struct1, comp_struct2, .... through comp_struct9

Now here is the SQL equivalent of what I need in LINQ



SELECT [ven_grp], xx_lawTemp.[comp_no],[acct_unit],[description],[company]
FROM xx_lawTemp inner join
companies ON xx_lawTemp.company = companies.comp_struct1 and xx_lawTemp.ven_grp = companies.comp_parent
group by [ven_grp], xx_lawTemp.[comp_no],[acct_unit],[description],[company]


For reasons I won't get into I can't use SQL to do what I need to do.

this is how far I got with LINQ



Dim query = From l In law, c In comps _
Where l("ven_grp") = c("comp_parent") And l("company") = c("comp_struct1") _
Select l

which works ok, but I can't for the life of me figure out how to group on the fields I need to and then dump the query results into datatable. i need to follow the groupings that are in the SQL query above. I've looked at tons of examples on line and I just don't seem to get it or it does not work the way examples show. Both of my tables are defined as similar "Dim law = ds.tables(0).asenumerable()

 

[JPWilson]

To be completely honest, I know little to nothing about Linq but wouldn't using DISTINCT return the same result as grouping by the selected values?

Otherwise, and I'm grasping here, you might try wrapping your statement in another call and then grouping based on those values. So maybe something like:

Dim query = From q In (--your stuff--) Group q By... etc.

 

[Parmenides]

Well, I'm not 100% on what you're after. But my suggestion, is to dump everything into a dictionary, then use the contents of the dictionary into your datatable.

I'm not sure about the VB equivalent, but with C#, I just use something like:

var data = (from x in db.table
group x by x.[ven_grp] {etc..} into x
select new
{
ColName = x.key,
col1 = x.[ven_grp],
col2 = x.xx_lawTemp.[comp_no],
col3 = x.[acct_unit],
col4 = x.[description],
col5 = x.[company],
}).ToDictionary(x => x.ColName, x => new myCustomColumnClass(col1, col2, col3, col4, col5).

After that, you just 'foreach' through all the dictionary items inserting it into DataTable. My explanation is super sloppy and some of it is slightly psuedo code in C#, but hopefully this will give you ideas.



edit.... thinking back upon the group by, you might have to have a sort of subquery for your columns.