linq group by month problem

[Seb]

I want to count activity in a given month. I'm trying to do so with
the linq code below however it reports:
Error 1 'a' is inaccessible due to its protection level

var ActivityByMonths = from a in db.ActivityDatas
where a.BeginDateTime >= From && a.BeginDateTime <= To
where a.SubActivityId == sat
group a by a.BeginDateTime.Month into mg
select new { a.BeginDateTime.Month, count = mg.Count() };

I want to select is the month number and the number of activities that
took place in that month.

best regards,
seb

 

[Pavel Minaev]

I want to count activity in a given month. I'm trying to do so with
the linq code below however it reports:
Error   1       'a' is inaccessible due to its protection level

                var ActivityByMonths = from a in db.ActivityDatas
                                                           where a.BeginDateTime >= From && a.BeginDateTime <= To
                                                           where a.SubActivityId ==sat
                                                           group a by a.BeginDateTime.Month into mg
                                                           select new { a.BeginDateTime..Month, count = mg.Count() };

I want to select is the month number and the number of activities that
took place in that month.

Once you group 'a', it goes out of scope (since there's no single 'a'
associated with each group). The reason for the confusing error
message you get is probably that you have another 'a' in scope, which
is indeed inaccessible (most likely, a field with such name in a base
class). Instead, to access the value of the grouping key for each
group, use mg.Key:

                var ActivityByMonths = from a in db.ActivityDatas
                                                           where
a.BeginDateTime >= From && a.BeginDateTime <= To
                                                           where
a.SubActivityId == sat
                                                           group a by
a.BeginDateTime.Month into mg
                                                           select new
{ mg.Key, count = mg.Count() };

 

[Seb]

Once you group 'a', it goes out of scope (since there's no single 'a'
associated with each group). The reason for the confusing error
message you get is probably that you have another 'a' in scope, which
is indeed inaccessible (most likely, a field with such name in a base
class). Instead, to access the value of the grouping key for each
group, use mg.Key:

                 var ActivityByMonths = from a in db.ActivityDatas
                                                            where
a.BeginDateTime >= From && a.BeginDateTime <= To
                                                            where
a.SubActivityId == sat
                                                            group a by
a.BeginDateTime.Month into mg
                                                            select new
{ mg.Key, count = mg.Count() };

That gives me another question. Because in that case the code only
works if we are dealing with only one year. In my case the period
might be spanning several years. So I need to select both the year and
the month.

In the end I display the data in a graph with month and year on the x-
axis and number of activities on the y-axis... so being able to select
both month and year is a must.

 

[Seb]

That gives me another question. Because in that case the code only
works if we are dealing with only one year. In my case the period
might be spanning several years. So I need to select both the year and
the month.

In the end I display the data in a graph with month and year on the x-
axis and number of activities on the y-axis... so being able to select
both month and year is a must.

var ActivityByMonths = from a in db.ActivityDatas
where a.BeginDateTime >= From && a.BeginDateTime <= To
where a.SubActivityId == sat
group a by a.BeginDateTime.Month.ToString() + " " +
a.BeginDateTime.Year.ToString() into mg
select new { Month = mg.Key, Count = mg.Count() };

To my surprise the above code works but I feel it's a bit... fishy.
And a problem is that I wanted to display the month by name rather
then number.
I tried with
group a by a.BeginDateTime.ToString("M y") into mg
but that gives me: Method 'System.String ToString(System.String)' has
no supported translation to SQL.

Any ideas?

 

[Marc Gravell]

You need a composite group; here's an example from a previous
conversation; note that it demonstrates how [with LINQ-to-SQL] you can
even use UDFs in the mix [this doesn't work with LINQ-to-EF, though]

[again - the WorkWeek function is a bit overkill!]

Marc

var qry = from order in ctx.Orders
where order.OrderDate.HasValue
group order by
new {
order.OrderDate.Value.Year,
order.OrderDate.Value.Month,
// week is provided by a UDF via
[Function] on the ctx
Week = ctx.WorkWeek(order.OrderDate.Value)
}

into agg
orderby agg.Key.Year, agg.Key.Month,
agg.Key.Week
select new { Year = agg.Key.Year,
Month = agg.Key.Month,
Week = agg.Key.Week,
Count = agg.Count() };
foreach (var item in qry)
{
Console.WriteLine("{0}, {1}, {2}: {3}", item.Year,
item.Month, item.Week, item.Count);
}

 

[Seb]

You need a composite group; here's an example from a previous
conversation; note that it demonstrates how [with LINQ-to-SQL] you can
even use UDFs in the mix [this doesn't work with LINQ-to-EF, though]

[again - the WorkWeek function is a bit overkill!]

Marc

                      var qry = from order in ctx..Orders
                      where order.OrderDate.HasValue
                      group order by
                        new {
                            order.OrderDate.Value.Year,
                            order.OrderDate.Value.Month,
                            // week is provided by a UDF via
[Function] on the ctx
                            Week = ctx.WorkWeek(order.OrderDate.Value)
                        }

                      into agg
                      orderby agg.Key.Year, agg.Key..Month,
agg.Key.Week
                      select new { Year = agg.Key..Year,
                          Month = agg.Key.Month,
                          Week = agg.Key.Week,
                          Count = agg.Count()};
            foreach (var item in qry)
            {
                Console.WriteLine("{0}, {1}, {2}: {3}", item.Year,
item.Month, item.Week, item.Count);
            }

The code below works...

var ActivityByMonths = from a in db.ActivityDatas
where a.BeginDateTime >= From && a.BeginDateTime <= To
where a.SubActivityId == sat
group a by new { a.BeginDateTime.Year, a.BeginDateTime.Month }
into g
select new { g.Key.Year, g.Key.Month, Count = g.Count() };
...
string mydate = new DateTime(abm.Year, abm.Month, 1).ToString("y")


But I don't think it's very pretty... is this really the way to do it?

 

[Marc Gravell]

Well, you can probably do some things with the culture's calendar -
but what you have is simple. Note the difference here is in the SQL
query; using the above the SQL at the database is simple - allowing
your scalable system (the app-servers) to do the formatting of numbers
to strings. Which is what we want ;-p

Marc

 

[Pavel Minaev]

You need a composite group; here's an example from a previous
conversation; note that it demonstrates how [with LINQ-to-SQL] you can
even use UDFs in the mix [this doesn't work with LINQ-to-EF, though]

[again - the WorkWeek function is a bit overkill!]

Marc

                      var qry = from order in ctx.Orders
                      where order.OrderDate.HasValue
                      group order by
                        new {
                            order.OrderDate..Value.Year,
                            order.OrderDate..Value.Month,
                            // week is provided by a UDF via
[Function] on the ctx
                            Week = ctx.WorkWeek(order.OrderDate.Value)
                        }

                      into agg
                      orderby agg.Key.Year, agg.Key.Month,
agg.Key.Week
                      select new { Year = agg.Key.Year,
                          Month = agg.Key.Month,
                          Week = agg.Key.Week,
                          Count = agg.Count() };
            foreach (var item in qry)
            {
                Console.WriteLine("{0}, {1}, {2}: {3}",item.Year,
item.Month, item.Week, item.Count);
            }

The code below works...

                var ActivityByMonths =  from a in db.ActivityDatas
                                                                where a.BeginDateTime >= From && a.BeginDateTime <= To
                                                                where a.SubActivityId == sat
                                                                group a by new { a.BeginDateTime.Year, a.BeginDateTime.Month }
into g
                                                                select new { g.Key.Year, g.Key.Month, Count = g.Count() };
...
                        string mydate = new DateTime(abm.Year, abm.Month, 1).ToString("y")

But I don't think it's very pretty... is this really the way to do it?

Well, yes. I don't see what's wrong with it - you group stuff by year
and month, so you get group objects with key consisting of year and
month... how else is it supposed to work? SQL-style grouping is even
less prettier.

 

[Seb]

I guess you are right. Thanks a lot for the help.


seb