This is a migrated thread and some comments may be shown as answers.

[Solved] Grouping on Date, ignoring Time

22 Answers 456 Views

Grid

This is a migrated thread and some comments may be shown as answers.

acm2001 asked on 25 Jan 2009, 10:32 AM

I want to perform grouping on a RadGrid like the example in the demo section for Outlook like grouping.

My database holds a list of blog posts with Date and Time in the same field {pubDate}

I only want to group on the Date part, not the time part

Is this possible?
I tried changing the value of pubDate the field in the itemdatabound and/or itemdatacreate but that did not seem to work.

Thanks
Alan

<telerik:RadGrid ID="ResultsGrid2" Skin="Vista" Width="430px" PageSize="30" AllowSorting="true"
            AllowPaging="True" ShowGroupPanel="True" AutoGenerateColumns="False" GridLines="none" runat="server" >
              <PagerStyle Mode="NumericPages" Position="TopAndBottom" ></PagerStyle>
            <MasterTableView DataKeyNames="PostID" >
            <GroupByExpressions>
                    <telerik:GridGroupByExpression>
                        <SelectFields>
                            <telerik:GridGroupByField FieldAlias="Date" FieldName="PubDate" FormatString="{0:D}"></telerik:GridGroupByField>
                        </SelectFields>
                        <GroupByFields>
                            <telerik:GridGroupByField FieldName="PubDate" SortOrder="Descending"></telerik:GridGroupByField>
                        </GroupByFields>
                    </telerik:GridGroupByExpression>
                </GroupByExpressions>
                <Columns>
                    <telerik:GridDateTimeColumn ItemStyle-Width="60" DataField="PubDate" HeaderText="Date" DataFormatString="{0:dd/MM/yyyy}" UniqueName="pubDate" ></telerik:GridDateTimeColumn>
                    <telerik:GridHyperLinkColumn DataTextField="Title" DataNavigateUrlFields="Link" HeaderText="Post" UniqueName="PostLink" Target="_blank"></telerik:GridHyperLinkColumn>
                    <telerik:GridHyperLinkColumn ItemStyle-Width="80" DataNavigateUrlFields="Blog.title" DataNavigateUrlFormatString="/tag.aspx/{0}" DataTextField ="Blog.title" HeaderText="Blog" UniqueName="BlogTitle"></telerik:GridHyperLinkColumn>
                    <telerik:GridBoundColumn DataField="Description" UniqueName="Description" Visible="false" ></telerik:GridBoundColumn>
                </Columns>
            </MasterTableView>
            <ClientSettings EnablePostBackOnRowClick="true" EnableRowHoverStyle="true">
                <ClientEvents OnRowMouseOut="RowMouseOver" />
                <Selecting AllowRowSelect="True"></Selecting>
            </ClientSettings>
        </telerik:RadGrid>

22 Answers, 1 is accepted

answered on 26 Jan 2009, 04:04 PM

Telerik people: Dynamic grouping would be an awesome feature. If we could specify via a delegate (or some event) *how* the grouping expression should compare the row currently being analyzed, we could customize the way our data looks so much easier. Rather than having to resort to changing the data itself (eg sending extra data in each row that could easily be extrapolated from the remaining fields).

For example: re grouping by partial dates, changing the grouping by month to year (or day or whatever) wouldn't require a change at the db layer. See http://www.telerik.com/community/forums/aspnet-ajax/grid/group-ip-addresses-by-subnet.aspx for another example of where dynamic grouping would be perfect.

Where do I file feature requests around here?

answered on 27 Jan 2009, 03:07 AM

Thanks
I had a look at the sample. Thank you.

Unfortunately, I am using Linq with OpenAccess. The example showed how to change the code for SQL and that makes sense, but i could not get it to work with my linq query.

Dim scope As IObjectScope = BlogLinkVB.ObjectScopeProvider1.GetNewObjectScope()
Dim StartDate As DateTime = DateTime.Now.AddDays(-45)
Dim result As Object = From p In scope.Extent(Of BlogLinkVB.Post)() Where (p.PubDate.Value > StartDate) Order By p.PubDate Descending Select p

Any thoughts?

Should i use itemdatabound or itemdatacreated to make a new gridboundcolumn?

Thanks

Alan

answered on 29 Jan 2009, 03:51 PM

Hello,

You may try returning an anonymous type from your linq query which to have all the needed properties plus the formatted date without the time part similar to the codelibrary my colleague Daniel have pointed. Thus your linq query should look something similar to the following:

    
            Dim result As Object = From p In scope.Extent(Of BlogLinkVB.Post)() 

                Where (p.PubDate.Value > StartDate) 

                Order By p.PubDate Descending 

                Select  New With {.id = p.id, .FormattedDate = New DateTime(p.PubDate.Year, p.PubDate.Month, p.PubDate.Day)}

Regards,
Rosen
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

answered on 30 Jan 2009, 02:30 PM

Thanks for this.

I had to change the code slightly to get it to sort of work. I added the .value after the variable

   Dim result As Object = From p In scope.Extent(Of BlogLinkVB.Post)() _
                                Where (p.PubDate.Value > StartDate) _
                                Order By p.PubDate Descending _
                                Select New With {.id = p.PostID, .FormattedDate = New DateTime(p.PubDate.Value.Year, p.PubDate.Value.Month, p.PubDate.Value.Day)}

but now am getting the error

Unable to cast object of type 'System.Object[]' to type 'OpenAccessRuntime.DataObjects.query.Node'.

Have tried to solve this but still stuck

Regards
Alan

answered on 02 Feb 2009, 04:39 PM

Hello,

You may need to evaluate the result before applying the projection. Thus the code snippet should look similar to the following:

    
             Dim scope As IObjectScope = BlogLinkVB.ObjectScopeProvider1.GetNewObjectScope()  
        
             Dim StartDate As DateTime = DateTime.Now.AddDays(-45)  
        
             Dim result = From p In scope.Extent(Of BlogLinkVB.Post)() Where (p.PubDate.Value > StartDate) Order By p.PubDate Descending Select p  
        
            Dim convertedReesult = result.ToList().Select(Function(order) New With {.id = p.PostID, .FormattedDate = New DateTime(p.PubDate.Value.Year, p.PubDate.Value.Month, p.PubDate.Value.Day)})

Best wishes,
Rosen
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

answered on 03 Feb 2009, 10:52 PM

yes i still get the same error.

Public member 'Select' on type 'List(Of Post)' not found.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.MissingMemberException: Public member 'Select' on type 'List(Of Post)' not found.

Source Error:

Line 92:         '  Select New With {.postid = p.PostID, .FormattedDate = New DateTime(p.PubDate.Value.Year, p.PubDate.Value.Month, p.PubDate.Value.Day)}
Line 93: 
Line 94:         Dim convertedResult As Object = result.ToList().Select(Function(order) New With {.id = result.PostID, .FormattedDate = New DateTime(result.PubDate.Value.Year, result.PubDate.Value.Month, result.PubDate.Value.Day)})
Line 95: 
Line 96:         RadGrid1.DataSource = result

can i ask what type you think result should be?

In the example you have "Dim result = From p" but there is no "as" declaration. what type is it?
Is it of type IObjectScopeQuery(Of BlogLinkVB.Post) ?

Dim scope As IObjectScope = BlogLinkVB.ObjectScopeProvider1.GetNewObjectScope()

Dim StartDate As DateTime = DateTime.Now.AddDays(-45)

Dim result = From p In scope.Extent(Of BlogLinkVB.Post)() Where (p.PubDate.Value > StartDate) Order By p.PubDate Descending Select p

Dim convertedReesult = result.ToList().Select(Function(order) New With {.id = p.PostID, .FormattedDate = New DateTime(p.PubDate.Value.Year, p.PubDate.Value.Month, p.PubDate.Value.Day)})

also, from your example code should p.postid and p.pubDate actually be result.postid and result.pubdate etc?

appreciate you sticking with this. i have done a lot of searching to try and find answers but there is not much out there.
thanks
alan

answered on 06 Feb 2009, 01:17 PM

dategroupingoalinqvb.zip

Hi,

As you may know with .net 3.5 and VB 9.0 you can use inplicit type declaration for local variables. Thus you do not need to explicitly state the type of the variable but the type can be inferred from the assigned value. This is the case with the Dim declaration in question.

I have attached a simple project which demonstrates the approach in question. Please give it a try and let us know if this helps.

Sincerely yours,
Rosen
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

answered on 21 Sep 2009, 03:47 PM

Hi Rosen,

I am running into the same issue. I have separated the select into my domain object into a separate dot-notation section from the query-syntax code.

I am however still receiving this message.

Unable to cast object of type 'Telerik.OpenAccess.RT.QueryBuilderImp' to type 'OpenAccessRuntime.DataObjects.query.Node'.

I think it happens on the ToList method call and not on the select.

                    return groupedData.ToList().Select(x => new MyStat(x.Date, x.Count));          

Thanks,

Brendan

answered on 21 Sep 2009, 03:59 PM

Here is the relevant portion of the stack trace. The error happens when I call ToList().

at Telerik.OpenAccess.Query.QueryContext.PerformDatabaseQuery(Type type, Expression expression, Int32& number, Boolean exec, Int32 numSkip, Int32 numTake)\r\n

at Telerik.OpenAccess.Query.QueryContext.PerformDatabaseQueryMultiple[T](Expression expression)\r\n

at Telerik.OpenAccess.Query.ObjectScopeQuery`2.GetEnumerator()\r\n

at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\r\n

at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)\r\n

answered on 23 Sep 2009, 12:57 PM

Hello Brendan Enrick,

It seems the exception is caused by missing functionality, although we have improved considerably our Linq support since the last post in this thread. Could you please provide us with the query that produces the groupedData result, so we will be able to dig into the problem and provide you with a solution?

Sincerely yours,
Alexander
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

answered on 25 Sep 2009, 09:27 AM

Hello Brendan Enrick,

Unfortunately this kind of grouping is not yet supported on the server side. You will have to retrieve the SV objects that match the criteria and do the grouping in memory:

    
            IQueryable<SV> svList = from sv in _objectScope.Extent<SV>() 
        
                                     where 
        
                                     sv.StartTime >= startDate && 
        
                                     sv.EndTime < endDate 
        
                                     select sv; 
        
            var groupedData = (from sv in svList.AsEnumerable() 
        
                               group sv by sv.StartTime.Date into dv 
        
                               select new Daily(dv.Key.Date, dv.Count())).ToList();

Hope that helps.

Best wishes,
Alexander
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

answered on 25 Sep 2009, 02:15 PM

Thanks Alexander I am getting a new error though. This is my code.

    
            using (var objectScope = SilverlightAnalyticsScopeProvider.ObjectScope()) 
        
            { 
        
                IQueryable<SV> svList = from sv in objectScope.Extent<SV>() 
        
                    where sv.StartTime >= startDate && sv.EndTime < endDate 
        
                    select sv; 
        
                IEnumerable<Daily> gv = (from sv in svList.AsEnumerable() 
        
                    group sv by sv.StartTime.Date 
        
                    into dv 
        
                        select new Daily(dv.Key.Date, dv.Count())); 
        
                return gv; 
        
            }

This is the error.

The "IObjectScope" is already closed.

Thanks,
Brendan

answered on 02 Oct 2009, 11:52 AM

Hello Brendan Enrick,

I am sorry for misunderstanding you, I thought this is the same issue that you posted about in the other thread. However, it is not excluded that the two problems are related.
I would suggest you to use the "scope per page" approach instead of "scope per method". As an effect of the Lazy loading mechanism, in some cases it is possible that some fields are not yet populated with data when you try to access them and need to be loaded from the scope. In your case this might be happening to the Date field of the Daily objects. That is why it is good to keep alive the same object scope during the whole page life-cycle. Please, try switching to the "scope per page" approach and let us know if it solves the problem. You can use this knowledge base article for reference.

Greetings,
Alexander
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

answered on 02 Oct 2009, 07:27 PM

Hi Alexander.

Thanks, I am going to try this to see if it helps, however, there is a bug in the knowledge base article.

In the PreRequestHandlerExecute method it checks to see if session is null and if it isn't it puts that value in there. Either way it calls the CreateTransaction method.

                    void context_PreRequestHandlerExecute(object sender, EventArgs e)          
            {          
                if (HttpContext.Current.Session != null)          
                {          
                    HttpContext.Current.Session[SCOPE_KEY] =          
                      ObjectScopeProvider1.GetNewObjectScope();          
                }          
                CreateTransaction();          
            }         

Then in the CreateTransaction method it depends on having Session not be null. This means that if Session is ever null you will get a null reference exception.

                    protected void CreateTransaction()          
            {          
                IObjectScope scope = (IObjectScope)HttpContext.Current.Session[SCOPE_KEY];          
                scope.Transaction.Begin();          
            }          

So there needs to be some kind of a change here.

Thanks very much for the help.

Brendan