c# - Efficient query involving count in subquery -

c# - Efficient query involving count in subquery -

say have hypothetical many-to-many relationship:

public class paper { public int id { get; set; } public string title { get; set; } public virtual icollection<author> authors { get; set; } } public class author { public int id { get; set; } public string name { get; set; } public virtual icollection<paper> papers { get; set; } }

i want utilize linq build query give me "popularity" of each author compared other authors, number of papers author contributed divided total number of author contributions in general across papers. i've come couple queries accomplish this.

option 1:

var query1 = author in db.authors allow sum = (double)db.authors.sum(a => a.papers.count) select new { author = author, popularity = author.papers.count / sum };

option 2:

var temp = db.authors.select(a => new { auth = a, contribs = a.papers.count }); var query2 = temp.select(a => new { author = a, popularity = a.contribs / (double)temp.sum(a2 => a2.contribs) });

basically, question this: of these more efficient, , there other single queries more efficient? how of compare 2 separate queries, this:

double sum = db.authors.sum(a => a.papers.count); var query3 = author in db.authors select new { author = author, popularity = author.papers.count / sum };

well, first of all, can seek them out , see 1 takes longest instance.

the first thing should translate sql or close possible info doesn't acquire loaded in memory apply computations.

but sense alternative 2 might best shot ,with 1 more optimization cache total sum of pages contributed. way create 1 phone call db authors anyway need, rest run in code , there can paralellize , whatever need create fast.

so (sorry, prefer fluent style of writing linq):

//here can load needed info if don't need whole entity. //i imagine might need name , pages.count can utilize below, optimization. var allauthors = db.authors.all(); var totalpagecount = allauthors.sum(x => x.pages.count); var theendresult = allauthors .select(a => new { author = a, popularity = a.pages.count/ (double)totalpagecount });

c# linq entity-framework-5