Gilbert Tordeur (7) [Avatar] Offline
#1
Hello.

I would like to count the number of events stored in a table. The MS Access SQL statement for this is :
SELECT IntNumSociete, IntNumSite, Year([IntDatIntervention]) AS Annee, IntTypIntervention, Count(IntNumIntervention) AS CompteDeIntNumIntervention
FROM InterventionFluide
WHERE (((IntCodSuppression)="N"))
GROUP BY IntNumSociete, IntNumSite, Year([IntDatIntervention]), IntTypIntervention
ORDER BY IntNumSociete, IntNumSite, Year([IntDatIntervention]), IntTypIntervention

I try to write it in Linq, inspired by listing 6.14 (of the book) in VB :
Dim MesInterventions = From Intervention In ContexteFluide.InterventionFluide _
Where Intervention.IntCodSuppression = "N" _
Group Intervention By Intervention.IntDatIntervention.Year _
, Intervention.IntNumSite _
, Intervention.IntNumSociete _
, Intervention.IntTypIntervention _
Into GroupeIntervention() _
Select GroupeIntervention.keys _
, CompteIntervention = GroupeIntervention.Count

At first, Visual Studio automatically adds parentheses after GroupeIntervention.
Secondly, I get an error message (translated here in English) : The definition of the Method 'GroupeIntervention' is not accessible in this context.

So, where is my error and how should I write this Linq statement ?

Thank you for your help,
Gilbert
fabrice.marguerie (224) [Avatar] Offline
#2
Re: Group by in VB
Hello Gilbert,

The syntax in VB is not the same as in C#. I don't remember if we have a VB example for this in the book, but you can check out the VB query expression syntax on page 100 (Figure 3.4) to get an overview of the correct syntax.

The difference with C# is that VB expects you to use the Group keyword twice.
Here is what your query should look like:

[pre]
Dim MesInterventions = _
From Intervention In ContexteFluide.InterventionFluide _
Where Intervention.IntCodSuppression = "N" _
Group Intervention By _
Intervention.IntDatIntervention.Year, _
Intervention.IntNumSite, _
Intervention.IntNumSociete, _
Intervention.IntTypIntervention _
Into GroupeIntervention = Group, Count()[/pre]

Note that the Select clause is not useful in this case.
You can learn more on MSDN. The complete syntax is detailed here.

Fabrice
Gilbert Tordeur (7) [Avatar] Offline
#3
Re: Group by in VB
Merci Fabrice, pour la réponse et les liens.

About VB example : not from the book itself (it is in C#), but from the downloadable code : CodeSamplesCh6Samples.vb :
Dim query = From book In Books _
Group by key = book.SubjectId Into Group _
Select id = key, BookCount = Group.Count

Would you translate your book in VB, I would buy it immediately ! It's a pity that nearly all doc, all tutorials, all examples about Linq are written in C# !

Gilbert
fabrice.marguerie (224) [Avatar] Offline
#4
Re: Group by in VB
It would be easy to translate the book in VB since, as you know, we already have all the complete source code available in VB. Unfortunately, I'm not sure a publisher would be interested in creating it.
I'll ask Manning about it.