score:1
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();
Source: stackoverflow.com
Related Query
- Order by array values in Linq to Entity Framework Query
- Should the order of LINQ query clauses affect Entity Framework performance?
- Entity Framework - Linq query with order by and group by
- Check null values in Linq query in Entity Framework
- How to return values from a LINQ query and display them in a table using C#, ASP.NET MVC and Entity Framework
- How to Order Data Query in Entity Framework Table Relationships LINQ Method syntax?
- ASP.NET MVC Entity Framework - Linq query with order by
- Proper Linq Query for objects with many to many relation ship generated with code first entity framework
- Entity Framework Code Most First Efficient Linq Query
- Entity Framework Code First - The entity or complex type cannot be constructed in a LINQ to Entities query
- Entity framework linq query Include() multiple children entities
- How to avoid Query Plan re-compilation when using IEnumerable.Contains in Entity Framework LINQ queries?
- Entity Framework 6 Compiled LINQ Query
- Convert string to int in an Entity Framework linq query and handling the parsing exception
- Deleting multiple records with Entity Framework using a single LINQ query
- Simple conditional LINQ query in Entity Framework 4
- Entity Framework Order by descending by row field in query
- ThenInclude not working for an Entity Framework LINQ query
- Dynamic linq query expression tree for sql IN clause using Entity framework
- Retrieve single Entity Framework entities using a LINQ query or GetObjectKey?
- Entity Framework Query - Get Objects in Specific Order
- Entity Framework Linq Query to List - Error when using contains: Only primitive types, enumeration types and entity types are supported
- Entity Framework with Oracle using odp.net not taking parameters in linq query
- How can I view the Entity Framework LINQ query plan cache?
- Entity Framework Core LINQ query with multiple where clauses
- Queryable Linq Query Differences In Entity Framework
- Reusable linq select query in Entity Framework
- Entity Framework Core linq query returns InvalidCastException
- Internal access for entities in Entity Framework makes simple linq where query crash
- Entity Framework - Linq NOT IN query
More Query from same tag
- Linq Sum based on some columns
- What return value to use to match two different kinds of Types
- C# Linq multiple queries in a single one
- How to pass multiple properties to Expression<Func<TEntity, IEnumerable<TProperty>>>
- C# Linq eqiuvalent of SQL Count()
- Linq and a select with two tables
- Can't do multiple groups in MongoDB C# Driver
- Fill DataTable from LinqDataSource
- LINQ to XML: How to clone nodes while retaining annotations?
- Find an Object that contains a string in a list of strings using LINQ
- how return an IEnumerable<> of my customized model?
- Entity Framework: DefiningQuery error
- Cannot convert string to int32 LINQ
- How do I grab a single attribute from a Collection of objects that I group into a list?
- Sort dictionary by value - descending THEN alphabetical C#
- LINQ - simple select based on value of child element
- Linq2Sql -> Searching the database against a local collection of values - "Queries with local collections are not supported"
- linq nested select new
- Order complex object via LINQ
- SQL to C# LINQ query with multiple group by inner queries and having clauses
- Group by all properties of an object
- Querying two datatables
- Pass list of object properties to be used to form a new object inside a LINQ query
- Serialize a class containing a list<float[]> using SOAP Edit: list<MyClass>
- LINQ Getting Distinct Data and looping through to get related data
- Where() with Replace() in Dictionary.Where(p => p.Key is T)
- build linq query based off parameters
- Optimize finding all classes implementing IInterface<T> and those explicitly implementing it with a specific type
- Is it possible to convert foreach operation into LINQ if it does two things?
- Reflecting on executing assembly