Count occurrences of values across multiple columns

I am having a terrible time finding a solution to w开发者_如何学Chat 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.

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.

If you wish to use LINQ to SQL, you could try this "hack" that I recently discovered. It isn't the prettiest most cleanest code, but at least you won't have to revert to using LINQ to Objects.

var query = 
    from d in MyContext.MyTable
    let v1 = MyContext.MyTable.Where(dd => dd.ID == d.ID).Select(dd => dd.Value1)
    let v2 = MyContext.MyTable.Where(dd => dd.ID == d.ID).Select(dd => dd.Value2)
    // ...
    let v6 = MyContext.MyTable.Where(dd => dd.ID == d.ID).Select(dd => dd.Value6)
    from n in v1.Concat(v2).Concat(v3).Concat(v4).Concat(v5).Concat(v6)
    group 1 by n into g
    orderby g.Key
    select new
    {
        number = g.Key,
        Occureneces = g.Count(),
    };

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() };

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() };