LINQ Entity Framework - COUNT AND QUERY

Question

edmx file: OrderData:
ORDERID
SHIPMENT
DRIVER_ID
RECEIVE_NAME

we need to build a table and print for each DRIVER_ID first column:DRIVER_ID second column: how many rows with this DRIVER_ID third column: how many rows with this DRIVER_ID and has RECEIVE_NAME not null

please show me how it can be done...i only managed to print a getquery:(

    SERVER.CS

    public class OrderDataRepository : BaseRepository<OrderData>
    {


        public class OrderDataResults
        {
            public long DriverId { get; set; }
            public int OrderCount { get; set; }
            public int OrderCountWhereNameIsNotNull { get; set; }
        }




        public class OrderViewRepository : BaseRepository<OrderData>
        {
            public List<OrderDataResults> GetOrderDataResults()
            {
                return GetQuery().
                       Where(x => x.SHIPMENT != null).
                       GroupBy(o => o.DRIVER_ID).
                       Select(g =>
                                 new OrderDataResults
                                 {
                                     DriverId = g.Key,
                                     OrderCount = g.Count(),
                                     OrderCountWhereNameIsNotNull =
                                                       g.Count(o => o.RECEIVE_NAME != null)
                                 }).ToList();
            }
        }







    CLIENT.ASPX.CS

    public partial class Control : System.Web.UI.Page
    {

        protected void Page_Load(object sender, EventArgs e)
        {

            var rep = new OrderViewRepository();
            List<OrderDataResults> results = GetOrderDataResults();

**Error 12  The name 'GetOrderDataResults' does not exist in the current context
Error   11  The type or namespace name 'OrderDataResults' could not be found (are you missing a using directive or an assembly reference?)
**
            DataViewer.DataSource = results;
            DataViewer.DataBind();

        }


    }


    WEBPAGE.ASPX
<form id="Form1" runat="server">
        <asp:GridView runat="server" ID="DataViewer">
        </asp:GridView>
    </form>

Yakimych · Accepted Answer · 2011-11-05 17:12:32Z

2

Edited the answer according to the comments.
I would define a class to incorporate the results of your query first:

public class OrderDataResults // Or whatever you want to call it
{
    public int DriverId { get; set; }
    public int OrderCount { get; set; }
    public int OrderCountWhereNameIsNotNull { get; set; }
}

From your server you can return a list of those:

public class OrderViewRepository : BaseRepository<OrderView>
{
    public List<OrderDataResults> GetOrderDataResults()
    {
        return GetQuery().
               Where(x => x.SHIPMENT != null).
               GroupBy(o => o.DRIVER_ID).
               Select(g =>
                         new OrderDataResults
                         {
                             DriverId = g.Key,
                             OrderCount = g.Count(),
                             OrderCountWhereNameIsNotNull = 
                                               g.Count(o => o.RECEIVE_NAME != null)
                         }).ToList();
    }
}

And when loading the page you can call the GetOrderDataResults() method and get your list:

protected void Page_Load(object sender, EventArgs e)
{
    var rep = new OrderViewRepository();
    List<OrderDataResults> results = rep.GetOrderDataResults();

    DataViewer.DataSource = results;
    DataViewer.DataBind();
}

In order to see the results, just remove the <Columns> section from your GridView definition:

<asp:GridView runat="server" ID="DataViewer">
</asp:GridView>

And again, you will probably want to refactor this code and make some structural decisions on your own as soon as you get this code working (e.g. decide whether you want to return a list from your reporitory, etc).
Furthermore, you will probably want to customize the way your table looks, so you might want to look at the GridView documentation. In your case it seems that you only want to display the data, so a simpler control (such as the Repeater) might prove to be a better fit.

edited Nov 5, 2011 at 17:12

answered Nov 5, 2011 at 0:43

Yakimych

17.8k7 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

user1021182 Over a year ago

can you not use the getsomeresults function and show me how you use the above code without using the GetSomeResults...can you make ne function "count_drivers_orders" the code that i pasted was just for the example... this time i will paste all the code i have in the server.cs file

user1021182 Over a year ago

Your code uses my GetSomeResults function on the server side...i am searching for a way to replace that function and to retrieve only that table alone

user1021182 Over a year ago

your code gave me the error: "Error 4 The name 'GetSomeResults' does not exist in the current context

user1021182 Over a year ago

IQueryable<OrderView> results= rep.GetSomeResults(); notice, that line was missing in your code

Yakimych Over a year ago

Ok, I made another attempt to help you get the code working. You can give it a try and see if this helps.

|

Collectives™ on Stack Overflow

LINQ Entity Framework - COUNT AND QUERY

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related