Tutorial :How to do a nested count with OData and LINQ?



Question:

Here is the query I am trying to run from my OData source:

var query = from j in _auditService.AuditJobs.IncludeTotalCount()     orderby j.Description     select new         {           JobId = j.ID,           Description = j.Description,           SubscriberCount = j.JobRuns.Count()        };  

It runs great if I don't use the j.JobRuns.Count(), but if I include it I get the following error:

Constructing or initializing instances of the type <>f__AnonymousType1`3[System.Int32,System.String,System.Int32] with the expression j.JobRuns.Count() is not supported.

It seems to be a problem of attempting to get the nested count through OData. What is a work around for this? I was trying to avoid getting the whole nested collection for each object just to get a count.

Thanks!


Solution:1

As of today the OData protocol doesn't support aggregates.

Projections yes, but projections that include aggregate properties no.

Alex


Solution:2

You need .Net 4.0 and In LinqPad you can run following over netflix OData Service

void Main()  {      ShowPeopleWithAwards();      ShowTitles();  }    // Define other methods and classes here  public void ShowPeopleWithAwards()  {      var people = from p in People.Expand("Awards").AsEnumerable()              where p.Awards.Count > 0              orderby p.Name              select new              {                p.Id,                      p.Name,                AwardCount = p.Awards.Count,               TotalAwards = p.Awards.OrderBy (a => a.Type).Select (b => new { b.Type, b.Year} )                   };        people.Dump();  }    public void ShowTitles()  {      var titles = from t in Titles.Expand("Awards").AsEnumerable()              where t.ShortName != string.Empty &&                     t.ShortSynopsis != string.Empty &&               t.Awards.Count > 0              select t;      titles.Dump();  }  


Solution:3

You can now use my product AdaptiveLINQ and the extension method QueryByCube.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »