c# - Linq2SQL grouping and ungrouping in the same query -

c# - Linq2SQL grouping and ungrouping in the same query -

here's stumper in linq sql:

string p = prefix ?? ""; string d = delimiter ?? ""; var filegroups = b in folder.getfiles(data) b.uri.startswith(p) && b.uri.compareto(marker ?? "") >= 0 grouping b data.datacontext.getfilefolder(p, d, b.uri); //var folders = g in filegroups g.key.length > 0 select g; //var files = g in filegroups g.key.length == 0 select g; var files = filegroups.selectmany(g => g.key.length > 0 ? b in g.take(1) select new fileprefix { name = g.key } : b in g select new fileprefix { name = b.uri, original = b }); var retval = files.take(maxresults);

folders cannot nested (out of control) filenames can contain slashes , whatever deeper folder construction can emulated

folder.getfiles simple linq equiv (iorderedqueryable) select * files folderid=@folderid order uri

prefix filter saying homecoming files start with...delimiter path delimiter, such '/'marker pagination - starts returning @ specified point

data.datacontext.getfilefolder maps sql scalar function: homecoming whole string , including next delimiter occurs after prefix string return substring(@uri, 0, charindex(@delimiter, @uri, len(@prefix)) + len(@delimiter)) troubleshooting - original client-side clause did map correctly tsql. had hoped doing function alter things in final graph, nope.

in above, filegroups, , commented out folders, , files, work expected

the goal nail database once. i'd to, in single return, show subfolders , files based upon interpretation of fileprefix object (folders have null 'original' value)

the issue final selectmany throwing "could not format node 'clientquery' execution sql."

i suspect work if weren't tsql translation, looking @ logically, why not database work , select fileprefixes client side final step?

it's late ;) tomorrow i'll revert double tap on database slipping tolist() or similar somewhere there cause final step total client side (kludge). if has insights on how accomplish 1 database nail (short of writing stored procedure), i'd love hear it!!

the downside kludge final take(maxresults) expensive if db nail results in number of records far exceeds that. , subsequent skip(maxresults).take(1) didn't quote, marking next page, wound twice much.

thank much

welp, looks 2 database hits necessary. started noticing phone call graph converted tertiary operator iif led me think iif, on sql side, doesn't subqueries parameters.

string p = prefix ?? ""; string d = delimiter ?? ""; var filegroups = b in folder.getfiles(data) b.uri.startswith(p) && b.uri.compareto(marker ?? "") >= 0 grouping b data.datacontext.nx_getfilefolder(p, d, b.uri); var folders = g in filegroups g.key.length > 0 select g.key; var files = b in folder.getfiles(data) b.uri.startswith(p) && b.uri.compareto(marker ?? "") >= 0 && data.datacontext.nx_getfilefolder(p, d, b.uri).length == 0 select b; folders = folders.orderby(f => f).take(maxresults + 1); files = files.orderby(f => f.uri).take(maxresults + 1); var retval = folders.asenumerable().select(f => new fileprefix { name = f }) .concat(files.asenumerable().select(f => new fileprefix { name = f.uri, original = f })) .orderby(b => b.name).take(maxresults + 1); int count = 0; foreach (var bp in retval) { if (count++ < maxresults) yield homecoming bp; else newmarker.name = bp.name; } yield break;

a bit less elegant... left filegroups , folders, rewrote files query rid of grouping (generated cleaner sql , more efficient).

concat still gave me problem in new approach, kicked in asenumerable calls, point breaks 2 hits database.

i kept maxresults in sql limit traffic, worst case twice much info want going on wire. +1 next record user can notified start on next page. , used iterator pattern wouldn't have loop 1 time again next record.

c# sql sql-server linq