Create anonymous object via LINQ lambda from one result

Question

I have the following query

var resources = Db.RESOURCE_Resource
                .Include(i => i.RESOURCE_Value.Where(s => s.ApplicatieID == applicationId))
                .Include(i => i.RESOURCE_Category)
                .Where(w => missingCultureIds.All(a => w.RESOURCE_Value.All(an => an.CultureID != a)));

Besides from wanting to return the items which are returned by the query, I also want to return the count of the list. After this query a few more filters will be implemented on the IQueryable, but I need the count beforehand.

Cant I change the query so it includes the count without it leading to multiple queries? I want the count of all these items, but I want only the data of a couple of items (implemented afterwards with

resources.Skip(pageSize).Take(pageSize*pageNumber)

So I want to generate a query that sends only the reduced list of object, but the count of more objects. I know you can make anonymous types from a collection like

.Select(s => new { property = s.item }

Is it possible to do what I want or do I have to handle this via a stored procedure/table valued function?

`Cant I change the query so it includes the count without it leading to multiple queries?` Not really, no. See also https://stackoverflow.com/questions/29218164/getting-count-and-skip-take-in-one-operation-with-linq-to-entities . — mjwills, Aug 02 '17 at 13:29

AGB · Accepted Answer · 2017-08-02T13:34:27.527

0

You either need two queries, or you need to just query for the larger data set and then filter it with code. Which way performs best will depend upon the size of the larger data set.

One query:

 var resources = Db.RESOURCE_Resource
     .Include(i => i.RESOURCE_Value.Where(s => s.ApplicatieID == applicationId))
     .Include(i => i.RESOURCE_Category)
     .Where(w => missingCultureIds.All(a => w.RESOURCE_Value.All(an => an.CultureID != a))).ToList();

var count = resources.Count();
var results = resources.Skip(pageSize).Take(pageSize*pageNumber);

Two queries:

var count = Db.RESOURCE_Resource
    .Include(i => i.RESOURCE_Value.Where(s => s.ApplicatieID == applicationId))
    .Include(i => i.RESOURCE_Category)
    .Where(w => missingCultureIds.All(a => w.RESOURCE_Value.All(an => an.CultureID != a))).Count();
var results = Db.RESOURCE_Resource
    .Include(i => i.RESOURCE_Value.Where(s => s.ApplicatieID == applicationId))
    .Include(i => i.RESOURCE_Category)
    .Where(w => missingCultureIds.All(a => w.RESOURCE_Value.All(an => an.CultureID != a))).Skip(pageSize).Take(pageSize*pageNumber);

edited Aug 02 '17 at 13:34

answered Aug 02 '17 at 13:31

AGB

2,378
21
37

1

Your two solutions are identical. Both perform two queries, which the OP specifically said they're asking how to not do. – Servy Aug 02 '17 at 13:32
Yes, just spotted that. User error :) – AGB Aug 02 '17 at 13:33
After your edit the two solutions are no longer textually identical, but they're still semantically identical. – Servy Aug 02 '17 at 13:33
You're right. I meant for the first solution to execute before the count. I've added a ToList() to do this. – AGB Aug 02 '17 at 13:35
you don't need to project ToList before count. You can (more efficiently) count the IQueryable. It issues a Sql COUNT query. – reckface Aug 02 '17 at 13:37
That's going to be *worse* than just executing the two queries (unless there's only one page worth of data in the unpaged result, which shouldn't be often if the query is being paginated). – Servy Aug 02 '17 at 13:37

Create anonymous object via LINQ lambda from one result

1 Answers1