Count occurrences of values across multiple columns

Question

I am having a terrible time finding a solution to what I am sure is a simple problem.

I started an app with data in Lists of objects. It's pertinent objects used to look like this (very simplified):

class A {  
    int[] Nums;  
}

and

List<A> myListOfA;

I wanted to count occurrences of values in the member array over all the List.

I found this solution somehow:

var results  
    from a in myListOfA  
    from n in a.Nums  
    group n by n into g
    orderby g.Key
    select new{ number = g.Key, Occurences = g.Count}

int NumberOfValues = results.Count();

That worked well and I was able to generate the histogram I wanted from the query. Now I have converted to using an SQL database. The table I am using now looks like this:

MyTable {
  int Value1;
  int Value2;
  int Value3;
  int Value4;
  int Value5;
  int Value6;
}

I have a DataContext that maps to the DB.

I cannot figure out how to translate the previous LINQ statement to work with this. I have tried this:

MyDataContext myContext;

var results =
    from d in myContext.MyTable
    from n in new{ d.Value1, d.Value2, d.Value3, d.Value4, d.Value5, d.Value6 }
    group n by n into g
    orderby g.Key
    select new { number = g.Key, Occurences = g.Count() };

I have tried some variations on the constructed array like adding .AsQueryable() at the end - something I saw somewhere else. I have tried using group to create the array of values but nothing works. I am a relative newbie when it come to database languages. I just cannot find any clue anywhere on the web. Maybe I am not asking the right question. Any help is appreciated.

Answer 1

How about creating your int array on the fly?

var results =
    from d in myContext.MyTable
    from n in new  int[] { d.Value1, d.Value2, d.Value3, d.Value4, d.Value5, d.Value6 }
    group n by n into g
    orderby g.Key
    select new { number = g.Key, Occurences = g.Count() };

Answer 2

In a relational database, such as SQL Server, collections are represented as tables. So you should actually have two tables - Samples and Values. The Keys table would represent a single "A" object, while the Values table would represent each element in A.Nums, with a foreign key pointing to the one of the records in the Samples table. LINQ to SQL 's O/R mapper will then create a "Values" property for each Sample object, which contains a queryable collection of the attached Values. You would then use the following query:

var results = 
    from sample in myContext.Samples
    from value in sample.Values
    group value by value into values
    orderby values.Key
    select new { Value = values.Key, Frequency = values.Count() };

Answer 3

I received help on a microsoft site. The problem is mixing LINQ to SQL with LINQ to Objects.

This is how the query should be stated:

var results = 
    from d in MyContext.MyTable.AsEnumerable()
    from n in new[]{d.Value1, d.Value2, d.Value3, d.Value4, d.Value5, d.Value6}
    group n by n into g
    orderby g.Key
    select new {number = g.Key, Occureneces = g.Count()};

Works like a charm.