locked
How to Group Data in LS R2? RRS feed

  • Question

  • Hi there!

    I'm trying to group data so I wrote the following code:

     

    Private Sub SearchProducts_InitializeDataWorkspace(saveChangesTo As System.Collections.Generic.List(Of Microsoft.LightSwitch.IDataService))
    ' Write your code here.

    Dim q = From p In Me.DataWorkspace.ApplicationData.Products _
    Group p By p.CategoryID Into g = Group _
    Select New With {g, .NumProducts = g.Count()}

    End Sub

     

    Bellow the Group operator there is an error message: "Definition of method GroupBy is not accessible in this context".

    Why the code doesn't compile? Is there a way to workaround?

    Many thanks for any kind of help.

    Best regards,

     

     

     

     

     


    Ciro

    Saturday, April 9, 2011 9:27 PM

Answers

  • Ciro,

    I confirmed with Eric that creating a RIA service that connects back to the intrinsic database is indeed possible. Assuming that is what you are trying, the connection string code in your service class should look like this:

    Private m_context As ApplicationDataObjectContext
    Public ReadOnly Property Context() As ApplicationDataObjectContext
    	Get
    		If Me.m_context Is Nothing Then
    			Dim builder As New EntityConnectionStringBuilder()
    			builder.Metadata = "res://*/ApplicationData.csdl|res://*/ApplicationData.ssdl|res://*/ApplicationData.msl"
    			builder.Provider = "System.Data.SqlClient"
    			builder.ProviderConnectionString = WebConfigurationManager.ConnectionStrings("_IntrinsicData").ConnectionString
    
    			Me.m_context = New ApplicationDataObjectContext(builder.ConnectionString)
    		End If
    		Return Me.m_context
    	End Get
    End Property
    
    
    Hope this helps,
    Steve Hoag Microsoft aka the V-Bee
    • Marked as answer by Oric.CQ Friday, April 15, 2011 2:59 PM
    Thursday, April 14, 2011 6:36 PM
    Moderator
  • If the schema of the Product table was created within your LightSwitch application, what you can do is refactor the schema into two tables (Product and ProductCategory), add a 1:Many relationship from ProductCategory to Product (a Product has a ProductCateogry and a ProductCategory can have many Products), and then add a computed field to the ProductCategory table called Total which counts how many products the category has. Then you can add the ProductCategories collection to your screen, where you will be able to visualize the category name and the number of products in the category.

    This way, you can side-step the issue that LightSwitch does not allow for grouping within a composed query.

    Tuesday, April 12, 2011 4:56 AM

All replies

  • It looks like you're trying to create a screen to show a count of products grouped by CategoryID.

    I don't think this approach will work for you. Even if you got the code to compile, you couldn't easily bind the results to a datagrid or any meaningful control in order to display the output.

    An alternative suggestion is to create a SQL view which returns the aggregate data.

    Another method is to use a RIA service. The following blog post by Eric Erhardt explains this. There's a section towards the middle where he creates a screen based on the aggregate results.

    http://blogs.msdn.com/b/lightswitch/archive/2011/04/08/how-do-i-display-a-chart-built-on-aggregated-data-eric-erhardt.aspx

    Saturday, April 9, 2011 10:55 PM
  • Tim, thanks for your reply!

    LS is an easy tool, but RIA is far beyond my knowledge.

    Aggregate data is a common task for database systems. I do not understand why the LS does not have this feature natively.

    Best regards,


    Ciro
    Saturday, April 9, 2011 11:13 PM
  • Ciro,

     

    Thats what I thought until I tried RIA for the first time.  Even though it has a very little learning curve and few manual code typing, that is an excellent addition to LS' capabilities.

     


    -Bala
    Sunday, April 10, 2011 4:38 AM
  • If the schema of the Product table was created within your LightSwitch application, what you can do is refactor the schema into two tables (Product and ProductCategory), add a 1:Many relationship from ProductCategory to Product (a Product has a ProductCateogry and a ProductCategory can have many Products), and then add a computed field to the ProductCategory table called Total which counts how many products the category has. Then you can add the ProductCategories collection to your screen, where you will be able to visualize the category name and the number of products in the category.

    This way, you can side-step the issue that LightSwitch does not allow for grouping within a composed query.

    Tuesday, April 12, 2011 4:56 AM
  • Hi, Tim!

    I tried to experiment RIA service to solve my problem about aggregate date. So, I read the blog that you suggested and reproduced with success the example's blog.

    I have no idea about how to setup the EntityConnection because my data source (I don't know how to say) isn't a SQL file like Northwind. My data base is in DataSource yet!

    I tried the following code, but it didn' work:

    Public ReadOnly Property Context As ApplicationData.Implementation.ApplicationDataObjectContext
        Get
          If _context Is Nothing Then
            Dim builder = New EntityConnectionStringBuilder
            builder.Metadata =
              "res://*/ApplicationData.csdl|res://*/ApplicationData.ssdl|res://*/ApplicationData.msl"
            builder.Provider = "System.Data.SqlClient"
            builder.ProviderConnectionString =
              WebConfigurationManager.ConnectionStrings("ApplicationData").ConnectionString
    
            _context =
              New ApplicationData.Implementation.ApplicationDataObjectContext(builder.ConnectionString)
          End If
          Return _context
        End Get
      End Property
    

    Do you have an idea about how to setup the connection string?

    Many thanks for any kind of help.

    Best regards,


    Ciro
    Wednesday, April 13, 2011 8:28 PM
  • Hi Bala!

    I followed your advice about RIA Service and now I have more questions. If possible, please, read my reply to Tim.

    Thank you in advance for any help.

    Best regards,


    Ciro
    Wednesday, April 13, 2011 8:33 PM
  • Hi Justin!

    Now, I'd like to use RIA Service. If possible, please, read my reply above to Tim.

    Thank you in advance for any help.

    Best regards,



    Ciro
    Wednesday, April 13, 2011 8:35 PM
  • Hi Ciro,

    See the section "Connect to a WCF RIA Service" in this topic: http://msdn.microsoft.com/en-us/library/ff852017.aspx and also the Guidelines topic here: http://msdn.microsoft.com/en-us/library/gg589479.aspx

    Hope this helps,


    Steve Hoag Microsoft aka the V-Bee
    Wednesday, April 13, 2011 9:39 PM
    Moderator
  • Hi Steve! Thanks for reply!

    In my example I don't have external database. How can I replace references from Northwind to LS ApplicationData:

    Public Overrides Sub Initialize _
      (context As System.ServiceModel.DomainServices.Server.DomainServiceContext)
      
      If (WebConfigurationManager.ConnectionStrings.Item(Me.[GetType]().FullName) _
        Is Nothing) OrElse [String].IsNullOrWhiteSpace _
      (WebConfigurationManager.ConnectionStrings.Item _
       (Me.[GetType]().FullName).ConnectionString) Then
        
        _connectionString = "data source=NorthwindDB;initial catalog= " _
          & "Northwind;user id=myID;password=myPassword"
      Else
        _connectionString = WebConfigurationManager.ConnectionStrings.Item _
          (Me.[GetType]().FullName).ConnectionString
      End If
    
      MyBase.Initialize(context)
    End Sub
    
    

    Below, I need to replace Northwind to LS ApplicationData, but I don't how!

    Protected Overrides Function CreateObjectContext() As NorthwindEntities
      Dim Connection As New EntityConnectionStringBuilder()
      Connection.ProviderConnectionString = _connectionString
      Connection.Provider = "System.Data.SqlClient"
      Connection.Metadata = "res://*/NorthwindModel.csdl|" & _
             "res://*/NorthwindModel.ssdl|" & _
             "res://*/NorthwindModel.msl"
    
      Return New NorthwindEntities(Connection.ToString)
    End Function
    
    

    Many thanks for any kind of help!

    Best regards,


    Ciro
    Wednesday, April 13, 2011 11:49 PM
  • Ciro,

    The example in the Help topic is using Northwind as a source for the RIA service. You would need to replace the Northwind references with whatever names you used in your RIA service.

    Hope this helps,


    Steve Hoag Microsoft aka the V-Bee
    Thursday, April 14, 2011 12:24 AM
    Moderator
  • Steve, thanks for your reply!

    I don't know how to setup a connection string because my solution doens't have a SQL file database yet. My solution has only LS data entity.

    So, what changes should I do in the following code:

    Public Overrides Sub Initialize _
      (context As System.ServiceModel.DomainServices.Server.DomainServiceContext)

            If (WebConfigurationManager.ConnectionStrings.Item(Me.[GetType]().FullName) _
              Is Nothing) OrElse [String].IsNullOrWhiteSpace _
            (WebConfigurationManager.ConnectionStrings.Item _
             (Me.[GetType]().FullName).ConnectionString) Then

                _connectionString = "data source=NorthwindDB;initial catalog= " _
                  & "Northwind;user id=myID;password=myPassword"
            Else
                _connectionString = WebConfigurationManager.ConnectionStrings.Item _
                  (Me.[GetType]().FullName).ConnectionString
            End If

            MyBase.Initialize(context)
        End Sub

     

    Note: when the code above run, _connectonString is always =  

    "data source=NorthwindDB;initial catalog= Northwind;user id=myID;password=myPassword"

     Best regards,

     

    Thursday, April 14, 2011 1:26 AM
  • Ciro,

    Let's back up a bit here. You said in your earlier response to Tim that you created a RIA service based on the blog post that he pointed out. That blog post shows how to create a service that talks to the NorthWind database.

    I'm assuming that the RIA service you created uses a different database or source of data - correct? Using the blog example, you would need to replace Northwind with the name of your data source, and NorthwindReports with the name of your service class.

    I'm not sure what you mean by "My solution has only LS data entity." Is this the RIA service that you created?

    Regards,


    Steve Hoag Microsoft aka the V-Bee
    Thursday, April 14, 2011 1:42 AM
    Moderator
  • Steve,

    "Let's back up a bit here. You said in your earlier response to Tim that you created a RIA service based on the blog post that he pointed out. That blog post shows how to create a service that talks to the NorthWind database.

    I'm assuming that the RIA service you created uses a different database or source of data - correct?" YES

    "I'm not sure what you mean by "My solution has only LS data entity" I mean: I've just created the entities in LS project. There is no SQL Server file in my project. My project has only ApplicationData with the tables Products and Categories.

    Based on the blog, I created the RIA SERVICE. After that my LS project was with two data sources: ApplicationData: (two tables: Products and Categories) and AggregateReportData with the entity ProductsByCategory from the RIA Service).

    My question is about how to setup the connection string.

    Regards,

     

     

     

     


    Ciro
    Thursday, April 14, 2011 1:56 AM
  • Ciro,

    Sorry, Im confused. You say that you have Products and Categories entities in the LightSwitch project. You also say that you have a Product'sByCategory entity in your RIA service. Are you trying to create a RIA service that aggregates data from the Products and Categories entities inside LightSwitch?

    If so, that's not going to work. The RIA service needs to connect to an external data source. This doesn't have to be a SQL Server database, but I'm pretty sure that you can't create a RIA service that connects to the intrinsic database. Somebody please correct me if I'm wrong.

    Regards,


    Steve Hoag Microsoft aka the V-Bee
    Thursday, April 14, 2011 2:22 AM
    Moderator
  • Hi Steve,

    According to Eric's article, that is in fact incorrect.

    "It is possible to bring aggregate data into LightSwitch using a database View.  However, that approach may not always be possible.  If you don’t have the rights to modify the database schema, or if you are using the intrinsic “ApplicationData” data source, you won’t be able to use a database View.  The following approach will work in all database scenarios."

    http://blogs.msdn.com/b/lightswitch/archive/2011/04/08/how-do-i-display-a-chart-built-on-aggregated-data-eric-erhardt.aspx

    Yann

    Thursday, April 14, 2011 2:48 AM
  • Yann,

    I stand corrected. Perhaps you could help Ciro with his issue of how to format the connection string for this scenario, since Eric's blog and our documentation don't cover it.

    Thanks, 


    Steve Hoag Microsoft aka the V-Bee
    Thursday, April 14, 2011 3:17 AM
    Moderator
  • Steve,

    I'm with you on this, I don't understand either why he needs to do anything with his connection string. I was just passing on some information that I knew about. You asked to be corrected if you were wrong.

    If he follows Eric's instructions he should be able to do what he wants without NEEDING to do anything to a connection string.

    I haven't had the time to put Eric's excellent blog post into use myself yet, but I thought I remembered him saying (from when I read it), that you could use intrinsic data with his method, which turned out to be the case.

    I'd help him if I could.

    Yann

    Thursday, April 14, 2011 4:05 AM
  • Yann, Steve

    When building Eric's excellent example I alo immediately thought that this could be a great approach to use generally when access to aggregated data is required - also in the case of the intrinsic data source (ApplicationData). After digging a bit further I realise that this might not work in the development environment where LightSwitch is using a user instance of SQL Server Express to attach the ApplicationData mdf file - as far as I can see from the documentation (http://msdn.microsoft.com/en-us/library/ms254504.aspx) a database in the user instance is opened with exclusive access, so the RIA connection will in any case not be able to access this database. In the deployed (user) environment it will be different, your application data will then be residing in a named database (e.g. as specified during the publish process) and the connection string to this database can be obtained from the configuration data.

    Kobus

    Thursday, April 14, 2011 2:19 PM
  • Ciro,

    I confirmed with Eric that creating a RIA service that connects back to the intrinsic database is indeed possible. Assuming that is what you are trying, the connection string code in your service class should look like this:

    Private m_context As ApplicationDataObjectContext
    Public ReadOnly Property Context() As ApplicationDataObjectContext
    	Get
    		If Me.m_context Is Nothing Then
    			Dim builder As New EntityConnectionStringBuilder()
    			builder.Metadata = "res://*/ApplicationData.csdl|res://*/ApplicationData.ssdl|res://*/ApplicationData.msl"
    			builder.Provider = "System.Data.SqlClient"
    			builder.ProviderConnectionString = WebConfigurationManager.ConnectionStrings("_IntrinsicData").ConnectionString
    
    			Me.m_context = New ApplicationDataObjectContext(builder.ConnectionString)
    		End If
    		Return Me.m_context
    	End Get
    End Property
    
    
    Hope this helps,
    Steve Hoag Microsoft aka the V-Bee
    • Marked as answer by Oric.CQ Friday, April 15, 2011 2:59 PM
    Thursday, April 14, 2011 6:36 PM
    Moderator
  • Steve is correct.  The problem is that you can't use "ApplicationData" for the ConnectionString.  For your ApplicationData data source, use "_IntrinsicData" instead.  Since this is a "special" data source to LightSwitch, it needs to use a special name.  For example, LS puts the RolePermission table in the ApplicationData source.  So it needed to use a reserved name for the connection string.  That way, if the developer changes the name of the "ApplicationData" data source, LightSwitch could still find the connection string.

    I apologize for not calling that out earlier.  Hopefully you should be able to get the scenario working now.

    Thursday, April 14, 2011 7:25 PM
  • Steve, after follow the instructions about how to set up de ConnectionString, other exception occurred when execution the  function below.

    note: the instruction "Dim prod = Me.Context.Products" was inserted in the function only with the purpose of debugging. The error occurred becouse Me.Context.Producs returns null, but my tables (Products and Category) aren't empty:

    <Query(IsDefault:=True)>
      Public Function GetProductByCategory() As IQueryable(Of ProductByCategory)
    
        Dim prod = Me.Context.Products
    
        Return From od In Me.Context.Products
            Group By cat = od.Category
            Into g = Group
            Select New ProductByCategory With {.CategoryId = cat.Id,
                             .CategoryName = cat.CategoryName,
                             .TotalProductsByCategory = g.Count(Function(n) n.ProductName)}
    
      End Function
    

    Follow the error details:

    System.NullReferenceException was unhandled by user code
      Message=Object reference not set to an instance of an object.
      Source=Aggregate.Reporting
      StackTrace:
           at Aggregate.Reporting.AggregateReportData.GetProductByCategory()
           at GetProductByCategory(DomainService , Object[] )
           at System.ServiceModel.DomainServices.Server.ReflectionDomainServiceDescriptionProvider.ReflectionDomainOperationEntry.Invoke(DomainService domainService, Object[] parameters)
           at System.ServiceModel.DomainServices.Server.DomainOperationEntry.Invoke(DomainService domainService, Object[] parameters, Int32& totalCount)
           at System.ServiceModel.DomainServices.Server.DomainService.Query(QueryDescription queryDescription, IEnumerable`1& validationErrors, Int32& totalCount)
      InnerException:

    Regards,


    Ciro
    Friday, April 15, 2011 2:50 PM