locked
EntityDataSource/LinqDataSource GroupBy Query RRS feed

  • Question

  • User2134889106 posted

    I'm using Entity Framework 4 and have an entity with a DistrictID property. In a drop down list on a web page I would like to display the DistrictIDs that occur more than 500 times in descending order of the times they occur. In SQL, I would use a simple query like this:

    SELECT DistrictID
    FROM dbo.Student
    GROUP BY DistrictID
    HAVING COUNT(*) > 500
    ORDER BY COUNT(*) DESC
    
    

    I have tried to do this using an EntityDataSource and a LinqDataSource, but can't seem to get it to work.

    This EntityDataSource gives the error "No overload of canonical aggregate function 'Edm.Count' is compatible with the argument types in 'Count(Edm.Int32)'.".

        <asp:EntityDataSource ID="edsDistrictLookup" runat="server" ContextTypeName="Ber.DAL.BerEntities"
            EnableFlattening="False" EntitySetName="Students"
            Select="DistrictID, Count(DistrictID) AS DistrictCount" Where="Count(it.DistrictID) < 500" GroupBy="it.[DistrictID]" OrderBy="it.DistrictCount desc">
        </asp:EntityDataSource>
    
    

    If I remove the Where property it compiles successfully, but of course doesn't return the results I'm after.

    A LinqDataSource also seems to have a problem with count in the Where and OrderBy properties.

    Next I tried specifying the query in code-behind using the selecting event of a LinqDataSource like this.

        <asp:LinqDataSource ID="ldsDistrictLookup2" runat="server" ContextTypeName="Ber.DAL.BerEntities"
            OnSelecting="ldsDistrictLookup_Selecting">
        </asp:LinqDataSource>
    
    In ldsDistrictLookup_Selecting I have:
    		var query = from s in Students
                                group s.DistrictID by s.DistrictID into g
                                where g.Count() > 500
                                orderby g.Count() descending
                                select g.Key;
                    e.Result = query;
    
    

    But there's an error on Students: "Count not find in implementation of the query pattern for source type: 'Ber.Students'. 'GroupBy' not found."

    I have tried the above query in LinqPad and it runs fine and produces the correct results.

    Is a query like this possible using Entity Framework?

    Thanks...

    Monday, December 12, 2011 9:50 AM

All replies

  • User3866881 posted

    var query = from s in Students group s.DistrictID by s.DistrictID into g

    Hello:)

    group……by should be used like this——I mean you cannot use group+Property,but just group+an instance——

    var query = from s in Students
                                group s by s.DistrictID into g
                                where g.Count() > 500
                                orderby g.Count() descending
                                select g.Key;
    e.Result = query;

    Tuesday, December 13, 2011 8:41 PM
  • User2134889106 posted

    Thanks for the reply. I made the change suggested, but there's still an error on Students: "Count not find in implementation of the query pattern for source type: 'Ber.Students'. 'GroupBy' not found."

    Thanks...

    Monday, December 19, 2011 3:25 PM
  • User3866881 posted

    Thanks for the reply. I made the change suggested, but there's still an error on Students: "Count not find in implementation of the query pattern for source type: 'Ber.Students'. 'GroupBy' not found."

    Thanks...

    How did you that?Have you done what I told you above?It should be right……

    If wrong,Please tell me where it is(I mean the error throwing point is……?)

    Best reguards!

    Monday, December 19, 2011 7:59 PM