score:1

Accepted answer

quite challenging task. i was thinking initially just to switch the roles and perform pagination (orderby/skip/take) on otherentity table, but unfortunately that doesn't work due to one to many relationship. so i ended up with doing some pre pagination in memory on otherentity. however, in order to do that i needed counts of the matching items in enitytoorder, so this is retrieved with additional db query, which makes the solution involving 3 db queries and some memory processing. here it is

var countbyotherid = db.entitytoorder
    .groupby(e => e.otherid)
    .select(g => new { id = g.key, count = g.count() })
    .todictionary(e => e.id, e => e.count);

var other = new dictionary<long, string>();
int skipcount = startindex, usecount = 0;
foreach (var e in db.otherentity.orderby(e => e.name))
{
    int count;
    if (!countbyotherid.trygetvalue(e.id, out count)) continue;
    if (skipcount > 0 && other.count == 0)
    {
        if (skipcount >= count) { skipcount -= count; continue; }
        count -= skipcount;
    }
    other.add(e.id, e.name);
    if ((usecount += count) >= pagesize) break;
}


var entities = db.entitytoorder
    .where(e => other.keys.contains(e.otherid))
    .asenumerable()
    .select(e => new entitytoorder { id = e.id, name = e.name, 
        otherid = e.otherid, othername = other[e.otherid] })
    .orderby(e => e.othername).thenby(e => e.name)
    .skip(skipcount).take(pagesize)
    .tolist();

now, i'm not quite sure if that's better to what are you doing currently, but it's worth trying.

score:0

if you can change the model, then you might try the following:

public class entitytoorder
{
    [datamember]
    public long id { get; set; }
    [datamember]
    public string name { get; set; }
    [datamember]
    public long otherid { get; set; }

    [foreignkey("otherid")]
    public otherentity otherentity{ get; set; }
}

then, you should be able to perform this query:

using system.data.entity;

var entities = maincontext
    .entitytoorder
    .include(x => x.otherentity)
    .orderby(e => e.otherentity.name)
    .skip(startindex)
    .take(pagesize)
    .tolist();

edit : sorry, i missed the point that you had 2 databases....

score:0

i found an alternative which i thought i would post in case it is useful to anyone. i used a .join() to merge the dictionary of otherentity into my query. this still selects into an ienumerable so i don't think it is more efficient.

var entities = maincontext.entitytoorder;
var otherids = entities.select(e => e.otherid).tolist();

dictionary<long, string> othernames = secondcontext.otherentity
    .where(oe => otherids.contains(oe.id))
    .select(oe => new { id = oe.id, name = oe.name })
    .todictionary(oe => oe.id, oe => oe.name);

func<entitytoorder, keyvaluepair<long, string>, entitytoorder> joinfunc = ((a, b) => { 
    a.othername= b.value;
    return a;
});

return entities.join(othernames, e => e.otherid, oe => oe.key, joinfunc)
    .orderby(e => e.othername)
    .skip(startindex)
    .take(pagesize)
    .tolist();

note on includes

when applying join you select into an ienumerable and therefore lose the ability to access properties from a linked table. to counter this you would need to add a .include() for any linked table you need to access before applying the .join(). e.g.

var entities = maincontext.entitytoorder
    .include("linkedentity");

return entities.join(othernames, e => e.otherid, oe => oe.key, joinfunc)
    .orderby(e => e.othername)
    .thenby(e => e.linkedentity.name) //reference to linked table
    .tolist();

Related Query

More Query from same tag