Grouping on dynamic columns in LINQ to SQL

Question

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<BudgetLineEntity>()

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<BudgetLineEntity>().AsQueryable()
.GroupBy("Dim1","it")
.Select("new (Key as Dim1,Key as Dim2, Sum(Value1) as Sum)")
as IQueryable<BudgetData>;

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

}

Johan Larsson · Answer

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 Trivedi · Answer

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 Ahmed · Answer

Hi you can try like this also var eq = from c in table group by new {c.Dim1c.Dim2c.Dim3} into g where g.count() >1 select g;

Johan Larsson · Answer

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 Ahmed · Answer

Hi http://www.codeproject.com/Articles/24961/Linq-Group-By-over-multiple-keys-columns-on-an-in

Johan Larsson · Answer

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 Ahmed · Answer

hi  can you please let us know how you want the grouping only Dim1Dim2Dim3... or you want if Dim1 is null or empty then take Dim2 etc..

Johan Larsson · Answer

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 Larsson · Answer

Thanks but I have my dimension in a string. Those two posts will not solve my problem.

Khan Abrar Ahmed · Answer

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