This is a migrated thread and some comments may be shown as answers.
Group By Query
3 Answers 22 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Francesco
Top achievements
Rank 1
Francesco asked on 18 Sep 2013, 07:06 AM
Hello, I run the following query on the MySQL database

Select PRACOD PRATTT,PRACCL,PRACLI PRAOPE,PRACAD,PRATIF,PRATOP,PRACES,PRACEE, ANADES,ANACIT FROM AVEPRA
LEFT JOIN COIANA ON ANACOD = PRACCL
WHERE AVEPRA = 1026
GROUP BY PRACAD

The entity AVEPRA contains fields PRACOD, PRATTT, PRACCL, PRACLI PRAOPE, PRACAD, PRATIF, PRATOP, PRACES, PRACEE, PRATLL, PRAUIL, PRANON, Pranom, PRADES

The entity Coiana contains fields ANACOD, Anades, ANACIT, ANAVER, AnaCap, ANALOP, ANAMON, ANABOT, ANAUIT, ANARES

BUT I CAN NOT USE GROUP BY CLAUSE
How can I write in both C # and VB.Net

3 Answers, 1 is accepted

Sort by
0
Viktor Zhivkov
Telerik team
answered on 19 Sep 2013, 02:18 PM
Hello Francesco,

I have tried to define a more understandable SQL query based on your one.
Can you confirm that I got your intention right?
select p.id, p.categoryId, p.product_name
from product p
left join category c on p.categoryId = c.id
where p.product_name like 'c%'
group by p.categoryId

This query produces the following error message in SQL Management Studio indicating that you can select only columns added to the group by clause or aggregate functions over these columns:
Msg 8120, Level 16, State 1, Line 1
Column 'product.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If you are referring to this exception then you can utilize OpenAccess to do the heavy lifting for you by issuing multiple queries that will load all the columns (properties) using a simple LINQ query like that:
var query = from p in context.Products
         join c in context.Categories on p.CategoryID equals c.Id
         where p.ProductName.Contains("c")
         group p by p.CategoryID;
or in VB.NET:
Dim query = From p In context.Products
         Join c In context.Categories On p.CategoryID = c.Id
         Where p.ProductName.Contains("c")
         Group p By p.CategoryID

Please note that this LINQ query will result in multiple SQL queries in order to load the data and work around the limitation in SQL.

Regards,
Viktor Zhivkov
Telerik
OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvements shipped with this release.
0
Francesco
Top achievements
Rank 1
answered on 19 Sep 2013, 04:08 PM
My company uses mysql that allows the execution of this query.
How do I get the same result without specifying all the fields in the group by?
0
Viktor Zhivkov
Telerik team
answered on 24 Sep 2013, 01:33 PM
Hi Francesco,

While MySql allows you to run such query, I am not sure that the results are the ones that are expected.
In that case MySql will return only the first row for each group ignoring any additional rows in the data.

On the other hand adding every single column from the select clause to the group by will result in useless group clause that will return a group for each row in your table.

If you want to get your data grouped by some criteria and you want to have the complete entities loaded then you should use the LINQ query that I have posted in my previous post.

Regards,
Viktor Zhivkov
Telerik
OpenAccess ORM Q3 2013 Beta is available for immediate download in your account. Get it now and play with the latest bits. See what's new >>
Tags
LINQ (LINQ specific questions)
Asked by
Francesco
Top achievements
Rank 1
Answers by
Viktor Zhivkov
Telerik team
Francesco
Top achievements
Rank 1
Share this question
or