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


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.



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

Projections yes, but projections that include aggregate properties no.



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();  }  


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
Next Post »