title: LINQ to SQL Pivot table in VB.net
body: <p>I am trying to find a well structured example to create a Pivot table using linq to SQL in VB.net. there are many examples of how to do this in C# but only 2 in VB.net that did not solve my problem.</p>
  
  <p>I have tried to use the c# to VB converters to change the code to VB.net but these do not appear to work with LINQ.</p>
  
  <p>I also tried to convert an SQL Pivot table to LINQ for VB.net using Linqer, Linquer does not support the MS SQL command Pivot.</p>
  
  <p>an example of the data is,</p>
  
  <pre><code>name    prod    status
  a1      1       1
  a1      2       2
  a2      3       1
  </code></pre>
  
  <p>result should be</p>
  
  <pre><code>name    prod1    prod2    prod3
  a1      1        2        0
  a2      0        0        1
  </code></pre>
  
  <p>First objective would be to write a linq query to return the pivot table, the desired result is to perform a distinct query on the "prod" column and create a dynamic pivot table.</p>
  
  <p>Having looked at all the C# examples i can see that if i want to use linq i will need to move code to C#, unfortunately I only know VB.net.</p>
  
  <p>Any examples that use VB.net with the function option would be useful</p>
  
  <p>this example written in c# seems to address my request, unfortunately I cannot convert it to VB.net.</p>
  
  <pre><code> var query = from foo in db.Foos
          group foo by foo.Date into g
          select new {
              Date = g.Key,
              A = g.Where(x =&gt; x.Employee == "A").Sum(x =&gt; x.Job1),
              B = g.Where(x =&gt; x.Employee == "B").Sum(x =&gt; x.Job1),
              C = g.Where(x =&gt; x.Employee == "C").Sum(x =&gt; x.Job1),
              D = g.Where(x =&gt; x.Employee == "D").Sum(x =&gt; x.Job1),
              Total = g.Sum(x =&gt; x.Job1)
          };
  </code></pre>