none
How to: group by & count in entity? RRS feed

  • Question

  • Hi,

    I'm trying to create a custom query using entity in LightSwitch, I want to perform the following SQL code:

    select RegDateMonthName, COUNT(RegDateMonthName) as RegCount
    from Individuals
    group by RegDateMonthName

    how can i do that within the following method?

    partial void RegistrationByMonth_PreprocessQuery(ref IQueryable<Individuals> query)
            {
                
                        
            }

    Thursday, May 30, 2013 8:17 AM

Answers

  • Hi H. ALFADL;

    The query I posted shown below returns an anonymous type containing two fields RegDateMonthName and RegCount just like the T-SQL statement you posted in your original question.

    var results = from ind in ContextInstanse.Individuals
                  group ind by ind.RegDateMonthName into indGroup
                  select new {
                      RegDateMonthName = indGroup.Key,
                      RegCount = indGroup.Count( )
                  };

    If you look at the select statement I have, select new {, because there is no type name after the key word new the objects it creates are of anonymous type. What the query is returning are System.Linq.IQueryable<AnonymousType#1>. What the function is inputting and using is System.Linq.IQueryable<LightSwitchApplication.Individuals> which are NOT Compatible.

    What exactly is the parameter query in your function?

                   

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Thursday, May 30, 2013 6:33 PM

All replies

  • Hi H. ALFADL;

    The following Linq query will do the same as the T-Sql statement in your post.

    var results = from ind in ContextInstanse.Individuals
                  group ind by ind.RegDateMonthName into indGroup
                  select new {
                      RegDateMonthName = indGroup.Key,
                      RegCount = indGroup.Count( )
                  };

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Thursday, May 30, 2013 3:20 PM
  • Hi Fernando,

    based on your code I tried the following:

    partial void RegistrationByMonth_PreprocessQuery(ref IQueryable<Individuals> query)
            {
                query = (from ind in query
                        group ind by ind.RegDateMonthName into indGroup
                        select new {
                                      RegDateMonthName = indGroup.Key,
                                      RegCount = indGroup.Count()
                                    });
                        
            }

    The following error occured:

    Cannot implicitly convert type 'System.Linq.IQueryable<AnonymousType#1>' to 'System.Linq.IQueryable<LightSwitchApplication.Individuals>'. An explicit conversion exists (are you missing a cast?)


    • Edited by H. ALFADL Thursday, May 30, 2013 5:20 PM
    Thursday, May 30, 2013 5:20 PM
  • Hi H. ALFADL;

    The query I posted shown below returns an anonymous type containing two fields RegDateMonthName and RegCount just like the T-SQL statement you posted in your original question.

    var results = from ind in ContextInstanse.Individuals
                  group ind by ind.RegDateMonthName into indGroup
                  select new {
                      RegDateMonthName = indGroup.Key,
                      RegCount = indGroup.Count( )
                  };

    If you look at the select statement I have, select new {, because there is no type name after the key word new the objects it creates are of anonymous type. What the query is returning are System.Linq.IQueryable<AnonymousType#1>. What the function is inputting and using is System.Linq.IQueryable<LightSwitchApplication.Individuals> which are NOT Compatible.

    What exactly is the parameter query in your function?

                   

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Thursday, May 30, 2013 6:33 PM