LINQ GroupBy month

Question

I am having trouble getting an IQueryable list of a (subsonic) object grouped by Month and Year.

Basic view of the object...

public partial class DatabaseObject
{
    [SubSonicPrimaryKey]
    public int objectID { get; set; }

    public string Description { get; set; }

    public decimal Value { get; set; }

    public string Category { get; set; }

    public DateTime DateOccurred { get; set; }
}

Method to get IQueryable in my Database repository...

public IQueryable GetData(string DataType)
{
   return (from t in db.All<DatabaseObject>()
           orderby t.DateOccurred descending
           select t)
          .Where(e => e.Category == DataType);  
}

My question is, how can I return the dates grouped by Month? I have tried the below, but this results in compiler warnings regarding anonymous types...

public IQueryable GetData(string DataType)
{
   var datalist = (from t in db.All<FinancialTransaction>().Where(e => e.Category == DataType);
                   let m = new
                   {
                       month = t.DateOccurred.Month,
                       year = t.DateOccurred.Year
                   }
                   group t by m into l select new
                   {
                       Description = string.Format("{0}/{1}", l.Key.month, l.Key.year),
                       Value = l.Sum(v => v.Value), // Sum(v => v.Value),
                       Category = "Grouped"
                       DateOccurred = l.Last(v => v.DateOccurred)
                   }
    return datalist;
}

Any ideas?

Answer 1

Try this couple issues i found, but you basically need to select a Database object versus anonymous type?

IQueryable<DatabaseObject> datalist = (
from t in db.All<FinancialTransaction>().Where(e => e.Category == DataType)
let m = new
{
    month = t.DateOccurred.Month,
    year = t.DateOccurred.Year
}
group t by m into l 
select new DatabaseObject()
{
    Description = string.Format("{0}/{1}", l.Key.month, l.Key.year),
    Value = l.Sum(v => v.Value),   //Sum(v => v.Value),
    Category = "Grouped", 
    DateOccurred = l.Max(v => v.DateOccurred)
}).AsQueryable();

Let me know if my solution is now what you want. I also noticed you were using Last? The extension you were using I do not have so I replaced it with Max. I don't have subsonic installed so it might come with the libraries.

Answer 2

Any way don't combine LINQ in query syntax and LINQ in extension methods syntax. Use next:

from t in db.All<DatabaseObject>()    
where e.Category equals DataType
orderby t.DateOccurred descending
select t;

Answer 3

The issue is apparantly to do with the way Subsonic interprests certain linq statements and is a known bug.

IEnumerable<DatabaseObject> datalist = (
from t in db.All<FinancialTransaction>().Where(e => e.Category == DataType).ToList()
let m = new
{
    month = t.DateOccurred.Month,
    year = t.DateOccurred.Year
}
group t by m into l 
select new DatabaseObject()
{
    Description = string.Format("{0}/{1}", l.Key.month, l.Key.year),
    Value = l.Sum(v => v.Value),   //Sum(v => v.Value),
    Category = "Grouped", 
    DateOccurred = l.Max(v => v.DateOccurred)
}).AsQueryable();

I have fixed this by declaring an list of type IEnumerable and using ToList() to cast the database interaction, finally the query is then recast AsQueryable()