locked
Group by in lightswitch RRS feed

  • Question

  • i have this screen

    i would like to show the "importe" field but group by programa and paritem

    here is an example

    EO10       10

    EO10       20

    EO10       30

    .

    .

    .

    and this is how i wish to show my result

    E010     60

    i am showing the result using query designer, not linq

    thanks

    Thursday, February 21, 2013 8:27 PM

All replies

  • Hi,

    Can you give me some more information?

    If you group by paritem and programa, how did you want the importe field to display?

    I think what you are looking for is the mix between a query and a custom control to group the information on the screen and display it.

    -Pierson

    Thursday, February 21, 2013 9:02 PM
    Moderator
  • Hi,

    Can you give me some more information?

    If you group by paritem and programa, how did you want the importe field to display?

    I think what you are looking for is the mix between a query and a custom control to group the information on the screen and display it.

    -Pierson

    thanks for answering, please look at the image, i have an "programa" and "paritem" field.


    Each "programa" has his own "importe" and "paritem"


    so i would like to "mix" or "sum" or "group by", programa with paritem so instade of having a los of programa and paritem with the same result, i can have only one with the sum of importe.


    for example:

    programa    importe    paritem

    EO10             10            11301

    EO10             20            11301

    EO10             30            11301

    EO10              5             11301

    EO10              2             11301


    I´d like to show only the sum

    programa         importe     paritem

    EO10                 67              11301

    sorry, my english is not so good

    Thursday, February 21, 2013 9:16 PM
  • Hi Oscar,

    The Entity Designer doesn't have a group by option by default, although we allow you to write custom code to do it.

    Here are a few articles to get you started:

    Advanced Lightswitch - Writing Queries in Lightswitch Code

    Advanced Programming Made Easy With Visual Studio Lightswitch

    By writing custom queries, you can use LINQ to do your group by and your summation.

    Hope that helps

    -Pierson

    Thursday, February 21, 2013 10:13 PM
    Moderator
  • Oscar:

    An easy way to do this is by using a SQL view:

    1) Create a view in SQL Server with any grouping that you need.

    2) In LightSwitch, if you don't have an external Data Source, create one.

    3) Add the view to your Data Source.

    4) Optionally, create a query in LightSwitch for any filtering or sorting that you need.

    5) Add the query to a screen and use that for your grid.

    Mark

    Thursday, February 21, 2013 10:43 PM
  • could you guys help me with the linq query sintaxis:

    i hace this but an error shows:

            partial void Query1_PreprocessQuery(ref IQueryable<tabla1Item> query)
            {
                query = from t1 in tabla1
                        group t1 by t1.campo1 into g
                        select t1;
                        
            }

    Friday, February 22, 2013 1:23 AM
  • You can't group by in a PreProcessQuery method. Doing so would be changing the "shape" of the entity that the query is based on, & this is not allowed.

    You can only create new entities (ie ones that don't exist as tables) with a custom RIA service (or using a SQL view, for external data sources - note views are read-only).

    This blog post shows you how to create a custom RIA service to do grouping (plus shows some other really helpful techniques as well).

    How Do I: Display a Chart Built On Aggregated Data


    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.

    • Proposed as answer by babloo1436 Saturday, February 23, 2013 1:58 PM
    Friday, February 22, 2013 12:39 PM
    Moderator
  • Hi Oscar,

    here the code.

            partial void Query1_PreprocessQuery(ref IQueryable<tabla1Item> query)
           
    {
                query
    = (from t1 in tabla1
                       
    group t1 by t1.campo1).Select(t1 => t1.FirstOrDefault());
            }

    we use it only by SQL Server Views!

    Regards

    Jürgen


    • Edited by J.Nord Friday, February 22, 2013 2:55 PM
    • Marked as answer by Oscar.Caballero Friday, February 22, 2013 7:59 PM
    • Unmarked as answer by Oscar.Caballero Friday, February 22, 2013 8:32 PM
    Friday, February 22, 2013 2:51 PM
  • im sorry but im trying everything and still not have the result i want to:

    for example:

    programa    importe    paritem

    EO10             10            11301

    EO10             20            11301

    EO10             30            11301

    EO10              5             11301

    EO10              2             11301

    is the another way i can only get one row of E010 but the sum of importe only for those who paritem are the same???

    Friday, February 22, 2013 8:34 PM
  • Oscar,

    Have you tried the RIA Service or SQL View?

    A SQL view would look something like:

         SELECT programa, paritem, sum(importe) as importe

         FROM TableName

         GPOUP BY programa, paritem

    Mark

    Friday, February 22, 2013 9:46 PM
  • Oscar,

    The way Lightswitch Entities work is that you would have to use a RIA service or SQL View (like Mark suggested above) to do the grouping. Lightswitch Entities won't allow you to change how the entity schema is through grouping. The only way to do it is to force grouping through an external source and use that as a new entity within Lightswitch.

    Please look at either the links I posted or Yann's link in his post for additional help.

    -Pierson

    Friday, February 22, 2013 9:57 PM
    Moderator
  • Oscar,

    Have you tried the RIA Service or SQL View?

    A SQL view would look something like:

         SELECT programa, paritem, sum(importe) as importe

         FROM TableName

         GPOUP BY programa, paritem

    Mark

    i have tryed a ria service, and everything works fine, the problem is that i´d like to do a subquery using the query so i dont know how to do that.

    i´ve tryed a view but the same problem, i´d like to do a query from the vie query and still have problem.

    also tryed query designer: this is the most accurate result i have been looking for the only problem is the group by

    i really dont know how to solve this problem

    Friday, February 22, 2013 10:02 PM
  • Can you explain what you are trying to do in the subquery?
    Friday, February 22, 2013 10:04 PM
  • Can you explain what you are trying to do in the subquery?

    i´ll try to explain the best as i can but remember english is not my first language.

    i have two tables: par and pres, and here are the tables

    Par:

    Pres:

    as you can see, at the par table the important field is partida and in pres the important are programa and importe.

    when i run this query:

    SELECT pres.`programa`,par.`partida`,pres.`importe`,par.`des_par`
    FROM pres, par WHERE pres.`anio`=2013 AND pres.mes = 1 AND pres.`tipo`='so'
    AND pres.`partida` = par.`partida`
    GROUP BY pres.`programa`, pres.`partida`

    this is the result

    the par.partida "joins" pres.programa and pres.importe.

    In other words, each pres.programa has its own par.partida

    for example

    PROGRAMA         PARTIDA

    EO10                   1

    EO10                   2

    EO10                   3

    M001                   1

    M001                    2

    M001                   3

    E019                   1

    E019                    2

    E019                    3

    and so on.

    ok, i can get this using a view BUT i need the result of the last query to do another query

    for example:

    select * from pres where programa = "(the result of programa from the previous query)" and partida = "(the result of partida from the previous query)"

    i hope you can understand me xD

    thanks for your help

    Saturday, February 23, 2013 12:37 AM
  • Oscar:

    Sorry, my MySQL syntax knowledge is not great.  I have a couple of ideas though.  If the resulting query is readonly, which it seems like it is, I think that it's a bit easier.  I would still think that you could do the whole query in a view in MySQL.  Try using a derived table, in which you can create the first part of the query, and then join to the other table to get the last part.  Then add this view to your DataSource or a RIA Servce.

    Another option would be to add just the first part of the query, which I think that you have done and filter it in the PreprocessQuery for the first query.  The following is an example from my program.  It builds a list of user names from one table and uses that list to filter the other table.  You would have to build two lists I think.

    partial void queryUsersByPermission_PreprocessQuery(string PermissionName, ref IQueryable<User> query)
            {
                Permission permission = this.DataWorkspace.SecurityData.Permissions.Where(p => p.Name.Equals(PermissionName, StringComparison.OrdinalIgnoreCase)).SingleOrDefault();
    
                if (null == permission)
                {
                    // Short-circuit the query to return no results if the permission was not found
                    query = query.Where(a => false);
                }
                else
                {
                    // Get list of user names that have the permission
                    IEnumerable<string> userNames = permission.RolePermissions.SelectMany(rp => rp.Role.RoleAssignments).Select(ra => ra.UserName);
    
                    userNames = userNames.Distinct(StringComparer.Ordinal).ToList();
    
                    // Return users who are in the list of user names
                    query = query.Where(approver => userNames.Contains(approver.UserName));
                }
            }

    Saturday, February 23, 2013 10:50 PM
  • You can simply add a second query to your RIA service, that acts on the result of the default query. You can even add parameters to the query if you like (you just have to make each one nullable).

    LightSwitch will make this second query available to be used like any other sub-query (ie a query based on another query).

    You can add as many of these sub-queries to your RIA service as you need.

    VB:

    <Query(IsDefault:=True)>
    Public Function ClientItems() As IQueryable(Of ClientItem)
        Dim result As New List(Of ClientItem)
        Dim query = Me.Context.Clients
    
        result = _
            (
                From q In query _
                Where (some condition might go in here) _
                Select
            ).ToList
    
            Return result.AsQueryable
        End Function
    
    Public Function ClientItems_List( _
        ShowInactive As Boolean? _
        ) As IQueryable(Of ClientItem)
        Dim result As New List(Of ClientItem)
    
        Dim query = Me.ClientItems
        Dim includeInactive = ShowInactive.GetValueOrDefault
        
        result = _
            ( _
                From q In query
                Where _
                    (some other condition might go in here) _
            ).ToList
    
        Return result.AsQueryable
    End Function

    C#:

    [Query(IsDefault=true)]
    public IQueryable<ClientItem> ClientItems()
    {
        List<ClientItem> result = new List<ClientItem>();
        var query = this.Context.Clients;
    
        result = 
            (
                from q in query
                where (some condition might go in here)
                select;
            ).ToList();
    
    
        return result.AsQueryable();
    }
    
    public IQueryable<ClientItem> ClientItems_List(bool? ShowInactive)
    {
        List<ClientItem> result = new List<ClientItem>();
    
        var query = this.ClientItems();
        var includeInactive = ShowInactive.GetValueOrDefault();
    
        result = 
            (
                from q in query
                where (q.ID != 0)
                select q;
            ).ToList();
    
        return result.AsQueryable();
    }


    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.

    Sunday, February 24, 2013 5:27 AM
    Moderator
  • Oscar,

    I still don't understand these values, but I can identify the problem: Your SQL syntax is wrong.  Here are a couple of pointers -- hopefully this will allow you to figure out how to modify your SQL syntax to achieve the desired result set.

    First: You should only include in the SELECT clause that which will be included in the result set (each column that you want to see the resulting record set), and that must include each column that is used in a GROUP BY or ORDER BY clause.  You seem to either want a value that you are not specifying in your SELECT clause from your example query, or to be including additional values that you do not want in your resulting record set.

    Second: You may use database server functions directly on the column names within the SELECT clause to perform operations on that column before the record set is returned.  The one that you seem to want is a standard function (part of the ANSI SQL syntax) called SUM.  If you want SUM(partida) you may ask the database server to return that sum to you, instead of returning each discrete row value.

    Third: You may nest queries, so long as the result of a nested query is one column of results.  You actually showed something like this syntax in your bottom line above -- you may literally do that.  A query may contain another query or multiple other queries, so long as each sub-query is returns only one column of results.

    Good luck!

    R/Don// 

    Sunday, February 24, 2013 10:02 AM
  • @Jürgen - let me reiterate, so it's clear. You CANNOT use Group clause in a PreProcessQuery method, to return records.

    There are several restrictions when writing PreProcessQuery methods:

    1. You cannot add records to the query
    2. You cannot delete records from the query (although you can filter them out)
    3. You cannot change the shape of the records being returned (if a query is based on a Customer entity, its PreProcessQuery method can only return Customer entities.

    The only time you can use grouping in a PreProcessQuery method is if you're using it to help filter the records in the query collection parameter.


    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.

    Sunday, February 24, 2013 12:36 PM
    Moderator
  • i have created a ria service

    when i execute group by query it seems everything works fine, now the problem is when i try to execute the secund query over the first query, here is the code:

    public IQueryable<Campos> GetSupplierData()
            {
    
                consulta = "SELECT pres.idPres, pres.programa,pres.partida," +
                "des_par FROM pres,par WHERE pres.partida = par.partida AND anio = 2013 " +
                "AND mes = 1 GROUP BY pres.programa, pres.partida ORDER BY pres.programa, pres.partida";
    
    
    
                dominio.Clear();
    
                MySqlConnection cnn = new MySqlConnection(conexion);
                MySqlCommand cmd = new MySqlCommand(consulta, cnn);
    
    
                try
                {
                    cnn.Open();
    
                    MySqlDataReader dataReader = cmd.ExecuteReader();
                    Campos campos;
                    Campos campo2;
                    {
                        while (dataReader.Read())
                        {
                            campos = new Campos();
    
                            string consulta2 = "select * from pres where programa =" +dataReader["programa"].ToString();
    
                            MySqlCommand cmd2 = new MySqlCommand(consulta2, cnn);
    
                            MySqlDataReader dataReader2 = cmd2.ExecuteReader();
    
                            while (dataReader2.Read())
                            {
                                campo2 = new Campos();
                                campo2.programa = dataReader2["programa"].ToString();
    
    
                            }
    
                            
    
    
    
    
    
                           /* campos.idPres = (int)dataReader["idPres"];
                            campos.programa = dataReader["programa"].ToString();
                            campos.partida = dataReader["partida"].ToString();
                            campos.des_par = dataReader["des_par"].ToString();
                            //campos.importe = (int)dataReader["importe"];*/
                           
    
    
    
    
    
    
    
                            dominio.Add(campos);
    
                        }
                    }
    
                }
                finally
                {
                    cnn.Close();
                }
                return dominio.AsQueryable();
    
            }

    and this is the error:

    Sunday, February 24, 2013 5:28 PM
  • Can you guys help me??
    Monday, February 25, 2013 11:27 PM
  • If the entity(s) that you're trying to manipulate already exist in LightSwitch (either intrinsic data, or attached data), then you don't need to use SQL Connections & SQL Commands. I supplied a link to a blog post that does exactly what you're trying to do (just ignore the part about using a chart).

    Did you read it?

    How Do I: Display a Chart Built On Aggregated Data

    When you write a second query, based on another query, you also don't need SQL Connections & Commands, you simply use the first query as the source for the second (as I showed in my example code).


    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.

    Tuesday, February 26, 2013 3:02 AM
    Moderator
  • I´m really sorry but i cant see where you use the result of a query inside another query.

    Can you explain me your code a little bit??


    Tuesday, February 26, 2013 9:20 PM
  • No problem. Here's the VB version of the code again.

    <Query(IsDefault:=True)>
    Public Function ClientItems() As IQueryable(Of ClientItem)
        Dim result As New List(Of ClientItem)
        Dim query = Me.Context.Clients
    
        result = _
            (
                From q In query _
                Where (some condition might go in here) _
                Select
            ).ToList
    
            Return result.AsQueryable
        End Function
    
    Public Function ClientItems_List( _
        ShowInactive As Boolean? _
        ) As IQueryable(Of ClientItem)
        Dim result As New List(Of ClientItem)
    
        Dim query = Me.ClientItems
        Dim includeInactive = ShowInactive.GetValueOrDefault
        
        result = _
            ( _
                From q In query
                Where _
                    (some other condition might go in here) _
            ).ToList
    
        Return result.AsQueryable
    End Function

    The first query, ClientItems (the one that's being used as the default query), is using context.ClientItems as its source of data. The technique for setting u a context is described in the blog post I mentioned.

    The second query, ClientItems_List, is using the first query, ClientItems, as its source of data.

    Can you see now how the second query is based on the first query?


    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, February 27, 2013 2:51 AM
    Moderator