How to use First Method in querying data with Entity Datasource?

Question

I have the following code:

var orders = context.Orders
    .Include("Clients")
    .GroupBy(i => i.Clients.ClientName)
    .Select(i => i.OrderByDescending(it => it.OrderDate).FirstOrDefault());

I want to get only the last order made by each client. The basis for the code I got from here Remove duplicates in the list using linq, from Freddy's answer. (I'm including "Clients" because Orders has ClientId but not client name and the results are to be displayed in a grid including the client name which I'm getting from Clients). This works properly.

MY QUESTION:

Is it possible to do this using an asp.net Entity Datasource control?
Is it possible to use FirstOrDefault in some way in the asp.net Entity Datasource control?

score 1 · Answer 1 · edited May 23 '17 at 12:34

1

If you move the Include to the end of the query ...

var orders = context.Orders
    .GroupBy(i => i.Clients.ClientName)
    .Select(i => i.OrderByDescending(it => it.OrderDate).FirstOrDefault())
    .Include("Clients");

... you'll get Orders with their clients included.

With the original Include the query shape changes after the Include was applied. This always makes Include ineffective. You can move it to the end of the query, because even after the grouping, the query still return Orders, so the Include is applicable.

Note however that this is a tremendous overkill. Entire Client records are queried from the database, entire Client objects are materialized and in the end you only display their names. It's much better to project the required data to a DTO that exactly contains the data you want to display. For example:

var orders = context.Orders
    .GroupBy(i => i.Clients.ClientName)
    .Select(i => i.OrderByDescending(it => it.OrderDate).FirstOrDefault())
    .Select(o => new OrderDto
                 {
                     o.OrderNumber,
                     o. ... // More Order properties
                     Client = o.Clients.Name
                 });

The DTO should be a class containing these properties.

I don't know the Entity Datasource control. From what I see in the MSDN documentation it seems too restricted to even shape the query sufficiently to get the last orders of each client. And it expects an entity set, no DTOs.

edited May 23 '17 at 12:34

Community

1
1

answered May 23 '16 at 18:07

Gert Arnold

105,341
31
202
291

1. What's the difference between putting the Include at the beginning or the end? 2. Of Include("Clients") is at the end will that not cause a problem with GroupBy(i => i.Clients.ClientName) at the beginning? 3. What is "to project the required data to a DTO" in this context and how is it done? 4. The question I asked is how to execute this query with an ASP.NET Entity Datasource control. – Dov Miller May 24 '16 at 08:29
Thank you! I tried placing the include at the end of the query as you suggested and that raised a compiler error: 'System.Linq.IQueryable' does not contain a definition for 'Include' and no extension method 'Include' accepting a first argument of type 'System.Linq.IQueryable' could be found (are you missing a using directive or an assembly reference?) – Dov Miller May 24 '16 at 09:51
Try `using System.Data.Entity`. Did you use the lambda version of Include? – Gert Arnold May 24 '16 at 09:57
using System.Data.Entity doesn't help. What's the lamda version of Include? – Dov Miller May 24 '16 at 10:07
`Include(o => o.Clients)`. I don't understand why `Include` at the end raises this exception. `context.Orders` is also `IQueryable`, so it shouldn't make a difference for the compiler. – Gert Arnold May 24 '16 at 10:15

score 0 · Answer 2 · edited May 23 '17 at 11:47

0

Instead of calling OrderbyDescending try using the max operated as explained here

edited May 23 '17 at 11:47

Community

1
1

answered May 23 '16 at 12:53

Mujahid Daud Khan

1,983
1
14
23

Interesting idea! Will this improve performance? In any event I have no problem with my query, my question is how to execute this query with an ASP.NET Entity Datasource control. – Dov Miller May 24 '16 at 08:37

score 0 · Answer 3 · edited May 23 '17 at 12:10

I found here that with the EntityDataSource you can use:

Select="top(1) it.[OrderDate]"

However if you want to Order by DESC the top will be executed before the Order by DESC. If you want the Order by executed before the top, in other words to get the last Item, instead of top do this in Code behind:

protected void entityDataSource_Selecting(object sender,EntityDataSourceSelectingEventArgs e)
{
    e.SelectArguments.MaximumRows = 1;
}

All of this I got from that link in the Qustion and Answer.

score 0 · Accepted Answer · edited May 23 '17 at 10:29

I found that I can use the EntityDataSource's QueryCreated event as demonstrated in Filter with EntityDatasource in ASP.NET webforms in the question and answers.

In the case of this question I wrote

protected void EntityDataSource1_QueryCreated(object sender, QueryCreatedEventArgs e)
{
   var ordersQuery = e.Query.OfType<Orders>();
   e.Query = ordersQuery.Include("Clients")
   .GroupBy(i => i.Clients.ClientName)
   .Select(i => i.OrderByDescending(it => it.OrderDate).FirstOrDefault());
}

How to use First Method in querying data with Entity Datasource?

4 Answers4