none
EntityDataSource/LinqDataSource GroupBy Query RRS feed

  • Question

  • 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 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.

    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 even possible using Entity Framework?

    Thanks...



    • Edited by TD615 Friday, December 9, 2011 2:57 PM
    Friday, December 9, 2011 2:53 PM

Answers

All replies