locked
How to Show aggregate data based on parent/ child table relationship in LightSwitch RRS feed

  • Question

  • Hi All,

    have two tables FarmerMaster and FarmerDetails. The FarmerMaster table has province, district and reporting-period columns and the FarmerDetails table has gender column along with other. Now I want to count the number of farmers based on their gender for a specific Reporting-period for a province and district. I know how make a query for it in Sql Server But I don't have any idea how to do this aggregation and group by in LightSwitch Query.  

    Any help will be appreciated, Thanks in Advance!!

    Inayat Rehman


    Inayat Rehman

    Tuesday, January 1, 2013 11:59 AM

Answers

  • Hi Rehman

    Check the below article. Michael explained in detail about references.

    WCF RIA Service: Combining Two Table

    Regards


    Rashmi Ranjan Panigrahi
    www.lightswitchspecial.com

    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.
    This will help other users to find their answer quickly.

    • Marked as answer by Inayat Rehman Tuesday, January 8, 2013 12:25 PM
    Monday, January 7, 2013 5:40 AM
  • Hi Rashmi,

    I figured out the issue. Actually When I add the WCF RIA as datasource, then the classes in the RIA services class are concatenated and the + operator is used in DataServiceImplementation.cs class. I changed the + sign to . and it worked correctly...

    Best,


    Inayat Rehman

    • Marked as answer by Inayat Rehman Thursday, January 10, 2013 4:24 AM
    Thursday, January 10, 2013 4:24 AM

All replies

  • Hi Rehman

    By default lightswitch will not support aggregation or group by etc. To accomplish this task you have to create a WCF RIA Service. Follow the below article for step by step procedure on how to create a WCF RIA service and consume the service in lightswitch. You have to write Linq Query for this.

    How Do I: Display a chart built on aggregated data (Eric Erhardt)

    For more information you can follow lightswitchhelpwebsite.com

    Hope this help...

    Regards


    Rashmi Ranjan Panigrahi
    www.lightswitchspecial.com

    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.
    This will help other users to find their answer quickly.

    Tuesday, January 1, 2013 12:09 PM
  • Dear Babloo,

    Thanks for link, it is very informative but didn't solve my problem. Actually I want to have search screen in which the used will select the Reporting period and the result will be displayed as follow:

    Province      District     Gender     No.Of.Farmers

    A                A             Male         30

    B                B             Male         40

    B                B             Female     30

    If I want to write the Linq Query for about, then it would look like below:

    var result= (from a in FarmerMaster

                     join b in  FarmersDetails on a.FID equals b.FID

                     Where a.ReportingPeriod==@param

                     group a by new {a.province, a.district,b.gender,} into g

                     select new {g.key.province, g.key.district, g.key.gender, g.count()};

    Please help me in achieving this result.....


    Inayat Rehman

    Tuesday, January 1, 2013 12:34 PM
  • That link shows the correct (& only) method of doing what you want (other then using a SQL view).

    But you need to add a second query to the service class that has a parameter (or several). Your second method can call the first (default) method, filtering the results it produces, using the parameter values. When you update your data source (based on the RIA Service), you'll find that LightSwitch has now added a parameterised query for you, with the parameters you created. One thing to note, is that the parameters in the method that you create must be nullable.

    Or you could add the parameterised query in LightSwitch itself, based on the entity that the RIA Service provides.

    I prefer to do it the first way (with all my queries in code, rather than one in code & the other in LightSwitch), but that's just my preference. The second method is a bit simpler.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Wednesday, January 2, 2013 5:34 AM
    Moderator
  • Thank you Yann and Babloo,

    Today I will give it a try and will let you know, if any problem occured!!

    Best,

    Inayat


    Inayat Rehman

    Wednesday, January 2, 2013 9:52 AM
  • You're welcome Inayat.

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Wednesday, January 2, 2013 11:23 AM
    Moderator
  • Hi,

    The following line in the above article gives me error.

    WebConfigurationManager.ConnectionStrings("xxx").

    I looked for System.Web.Configuration in the .NET assemblies but couldn't find it. I don't know that To which library I should give reference to avoid this error????


    Inayat Rehman

    Monday, January 7, 2013 4:30 AM
  • Hi Rehman

    I think you have to add System.Web and System.Configuration reference in the Class Library Project. Keep one thing in mind that in VS 2012 your class library must contain .Net 4 framework. If you add .Net 4.5 framework you cannot able to consume your RIA Service in LightSwitch.

    Regards


    Rashmi Ranjan Panigrahi
    www.lightswitchspecial.com

    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.
    This will help other users to find their answer quickly.

    Monday, January 7, 2013 5:08 AM
  • Dear Babloo,

    Thanks for the prompt reply. I have already added both of the mentioned references. But I still get the same error .

    any other solution!!

    Best,


    Inayat Rehman

    Monday, January 7, 2013 5:34 AM
  • Hi Rehman

    Check the below article. Michael explained in detail about references.

    WCF RIA Service: Combining Two Table

    Regards


    Rashmi Ranjan Panigrahi
    www.lightswitchspecial.com

    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.
    This will help other users to find their answer quickly.

    • Marked as answer by Inayat Rehman Tuesday, January 8, 2013 12:25 PM
    Monday, January 7, 2013 5:40 AM
  • Thanks Babloo,

    This is very helpful and today I will start from the scratch again. But there is nothing about the "WebConfigurationManager".

    The other thing is that I only have app.config file in my application and there is no web.config or that might be hidden.

    Please tell me that how can I use the "WebConfigurationManager" Or how to import the System.Web.Configuration; I mean what steps should I follow.???

    Best,


    Inayat Rehman


    Monday, January 7, 2013 6:18 AM
  • Thanks Babloo,

    The above post solved my problem.

    Best,


    Inayat Rehman

    Tuesday, January 8, 2013 12:26 PM
  • Hi Babloo,

    I have made the WCF RIA Services using the above post. But when Add this as the datasource and run the solution, it gives me the following error.

    1.  ) expected

    2.  Invalid expression term ')'

    3.  ; expected

    4.  A new expression requires (), [], or {} after type

    The above error are generated in the following class..

    ..\ServerGenerated\GeneratedArtifacts\DataServiceImplementation.cs

    Any help will be appreciated..

    Best,


    Inayat Rehman

    Wednesday, January 9, 2013 4:29 AM
  • Hi Rehman

    Can you please provide your code to us. It will be more helpful to understand

    Regards


    Rashmi Ranjan Panigrahi
    www.lightswitchspecial.com

    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.
    This will help other users to find their answer quickly.

    Wednesday, January 9, 2013 6:45 AM
  • Dear Rashmi,

    Following is the code:

    //The Class which has the connection to the datasourc

    public class ReportServices : DomainService
        {
            private ApplicationDataObjectContext m_context;
            public ApplicationDataObjectContext Context
            {
                get
                {
                    if (this.m_context == null)
                    {
                        string connString =
                        System.Web.Configuration.WebConfigurationManager.ConnectionStrings["_IntrinsicData"].ConnectionString;
                        EntityConnectionStringBuilder builder = new EntityConnectionStringBuilder();
                        builder.Metadata ="res://*/ApplicationData.csdl|res://*/ApplicationData.ssdl|res://*/ApplicationData.msl";
                        builder.Provider ="System.Data.SqlClient";
                        builder.ProviderConnectionString = connString;
                        this.m_context = new ApplicationDataObjectContext(builder.ConnectionString);
                    }
                    return this.m_context;
                }
            }

    /// The Class which is used for mapping the fields

     public class Indicator5Reports
            {
                [Key]
                public int Id { get; set; }
                public string Province { get; set; }
                public string District { get; set; }
                public string Gender {get; set; }
                public DateTime ReportingPeriod { get; set; }
                public int TotalFarmers { get; set; }

                internal int NoOfFarmers
                {
                    set
                    {
                        this.TotalFarmers = value;
                    }

                }

            }

    // The Methods in which I have written the Query

     [Query(IsDefault = true)]
            public IQueryable<Indicator5Reports> MonthlyReports()
            {
                var result = from a in this.m_context.Ind5Masters
                             join b in this.m_context.DistrictsSet on a.Ind5Master_Districts equals b.Id
                             join c in this.m_context.Provinces on b.Districts_Province equals c.Id
                             join d in this.m_context.Ind5Details on a.Id equals d.Ind5Master_Ind5Detail
                            
                             group d by new {d.Id,c.Provine,b.District,d.Gender,a.ReportingPeriod } into g

                             select new Indicator5Reports()
                             {
                                 Id=g.Key.Id,
                                 Province=g.Key.Provine,
                                 District=g.Key.District,
                                 Gender=g.Key.Gender,
                                 ReportingPeriod=g.Key.ReportingPeriod,
                                 NoOfFarmers=g.Count(),
                             };

                return result;
            }
            // Override the Count method in order for paging to work correctly
            protected override int Count<T>(IQueryable<T> query)
            {
                return query.Count();
            }

    Hope you will find out some solution!!

    Best,


    Inayat Rehman

    Wednesday, January 9, 2013 12:09 PM
  • Hi Rashmi,

    I figured out the issue. Actually When I add the WCF RIA as datasource, then the classes in the RIA services class are concatenated and the + operator is used in DataServiceImplementation.cs class. I changed the + sign to . and it worked correctly...

    Best,


    Inayat Rehman

    • Marked as answer by Inayat Rehman Thursday, January 10, 2013 4:24 AM
    Thursday, January 10, 2013 4:24 AM