Grouping on dynamic columns in LINQ to SQL

Johan Larsson

12y10.9kLINQ

I have a SQLite database which I'm running LINQ to SQL queries on. The following query works fine when I know that I want to group by "Dim1" and "Dim2". The problem is that the columns to group by are set dynamically. Now, I've googled around for samples using Dynamic LINQ but I can't seem to solve my problem. Any help will be great.. This is as far as I've come..

var query = (from bl in DatabaseConnection.DataMemoryContext.GetTable()

where bl.BudgetID == filter.BudgetId && bl.BudgetType == filter.BudgetType
group bl by new { FirstDimensionName=primaryDimension.Name,SecondDimensionName=secondaryDimension.Name } into DimGroup
//group bl by new { Dimension1 = primaryDimension.Name, Dimension2 = secondaryDimension.Name } into DimGroup
select new BudgetData
{
Dim1 = DimGroup.Key.FirstDimensionName,
Dim2 = DimGroup.Key.SecondDimensionName,
Sum = DimGroup.Sum(grp => (System.Single)grp.Value1)
}).ToList();

I've also tried, but then res2 is null!?

var res2 =

DatabaseConnection.DataMemoryContext.GetTable().AsQueryable()
.GroupBy("Dim1","it")
.Select("new (Key as Dim1,Key as Dim2, Sum(Value1) as Sum)")
as IQueryable;

my BudgetData looks like this

public class BudgetData
{

public string Dim1 { get; set; }
public string Dim2 { get; set; }
public float Sum { get; set; }

}

my BudgetLineEntity looks like this

[Table(Name = "BudgetLine")]
public class BudgetLineEntity
{

[Column(Name = "BudgetLineID")]
public int BudgetLineID { get; set; }

[Column(Name = "BudgetID")]
public int BudgetID { get; set; }

[Column(Name = "BudgetType")]
public int BudgetType { get; set; }

[Column(Name = "Year")]
public int Year { get; set; }

[Column(Name = "Period")]
public int Period { get; set; }

[Column(Name = "Dim1")]
public string Dim1 { get; set; }

[Column(Name = "Dim2")]
public string Dim2 { get; set; }

[Column(Name = "Dim3")]
public string Dim3 { get; set; }

[Column(Name = "Dim4")]
public string Dim4 { get; set; }

[Column(Name = "Dim5")]
public string Dim5 { get; set; }

[Column(Name = "Value1")]
public System.Single Value1 { get; set; }

}

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Johan Larsson12y ago
I solved it, please see the code I used below. I had tried this very same code before, the problem was that my SQLite column data types were wrong

var query = (DatabaseConnection.DataMemoryContext.GetTable().AsQueryable()
.Where(String.Format("BudgetId={0}",filter.BudgetId))
.GroupBy(String.Format("new({0},{1})" ,primaryDimension.Name, secondaryDimension.Name), "new(Value1)")
.Select("new (Key.Dim1 as Primary, Key.Dim2 as Secondary, Sum(Value1) as Sum)")).AsQueryable();
Jignesh Trivedi12y ago
hi,
Please refer below article it might help you.
http://www.codeproject.com/Articles/43678/Dynamically-evaluated-SQL-LINQ-queries
http://tomasp.net/blog/dynamic-linq-queries.aspx/
http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library
Khan Abrar Ahmed12y ago
Hi, you can try like this also
var eq = from c in table group by new {c.Dim1,c.Dim2,c.Dim3} into g where g.count() >1 select g;
Johan Larsson12y ago
That might work, but it uses a datatable which might now be the best solution in my case. Ss there a good dynamic linq query I can use?
Khan Abrar Ahmed12y ago
Hi,
http://www.codeproject.com/Articles/24961/Linq-Group-By-over-multiple-keys-columns-on-an-in
Johan Larsson12y ago
I want to group by 2 columns, which are determined at runtime. So the code should be able to handle grouping on any two dimensions, given the value of the variables

string first = "Dim1";
string second = "Dim2";
...

In this case it should group by Dim1, Dim2.
Khan Abrar Ahmed12y ago
hi , can you please let us know how you want the grouping only Dim1,Dim2,Dim3...
or you want if Dim1 is null or empty then take Dim2 etc..
Johan Larsson12y ago
using Dynamic LINQ, I also tried this. But get

An exception of type 'System.InvalidCastException' occurred in System.Data.SQLite.dll but was not handled in user code

Additional information: Specified cast is not valid.

            var table = DatabaseConnection.DataMemoryContext.GetTable().AsQueryable();
            var eq = table.GroupBy("new (Dim1, Dim2)", "it").Select("new(it.Key as Key, it as Lines)");
            var keyLinesList = (from dynamic dat in eq select dat).ToList();

            foreach (var group in keyLinesList)
            {
                var key = group.Key;
                var elist = group.Lines;

                foreach (var emp in elist)
                {

                }
            }
Johan Larsson12y ago
Thanks, but I have my dimension in a string. Those two posts will not solve my problem.
Khan Abrar Ahmed12y ago
Hi you can refer this
http://www.c-sharpcorner.com/Blogs/10547/group-by-in-linq-to-sql.aspx
http://stackoverflow.com/questions/448203/linq-to-sql-using-group-by-and-countdistinct