c# - Find the top summed values and take 3 -

c# - Find the top summed values and take 3 -

i have code (not sure if works can't test it):

var test = base.unitofwork.session.query<nutritionfact>() .where(x => x.nutritionalserving.id == servingid) .groupby(x => x.userverifiedfacts) .orderbydescending(x => x.sum(e => e.userverifiedfacts.count())) .take(3) .select(r => new { c = r.key, sum = r.sum(x => x.userverifiedfacts.count()) }) .tolist();

what trying find nutritionfacts have right servingid. want count each of nutritionfacts found count of how many users verified information. want top 3 results , utilize them.

what doing results in "not implemented" error apparently because nhibernate sum can handle no parameters.

i try:

var test = ...query<nutritionfact>() .where(x => x.nutritionalserving.id == servingid) .select(x=> new { nf= x, vfcount= x.userverifiedfacts.count() }) .orderbydescending(x => x.vfcount)) .take(3) .tolist();

you don't need utilize sum. sum aggregating (summarizing) grouped columns, want count() number of rows in related table each specific nuturitionfact.

it corresponds sql statement (which is, suppose, need)

select top 3 n.*, (select count(*) userverifiedfact uv uv.nuturitionfacts_id = n.id) numbofver nutritionfact n n.nutritionalserving_id = @servingid order numbofver desc

c# .net linq nhibernate