CODEWITHSUNDEEP
c#sql-serverentity-frameworkreflection
user1470240
user1470240

Reputation: 716

Add sort on reflected navigation property

I'm using EF 6.0 .NET-Framework and MS SQL Sever and I have the follow situation: I have a dynamic data selection on a Navigation property from a given entity. This works so far OK. But: I like to add some sortings. But I cannot figure out how to make EF understand, that the sort shall be sent to the database instead sorting on client side afterwards. The problem seems, that the data is requested from database when I retrieve the navigation property's value and not when I complete the command chain with the sort.

My code is like (simplyfied):

var dynamicRelatedEntityType = typeof(RelatedEntity);

using (var dbContext = new DBContext())
{
    var orderByFunction = buildOrderByFunction(dynamicRelatedEntityType ); // this just builds a function for the order by ...
    var masterEntity = dbContext.MasterEntity.first(x=> x.Whatever = true);
    var navigationProperty = masterEntity.GetType().GetProperty(dynamicRelatedEntityType.Name); 

    var result = navigationProperty.GetValue(masterEntity).OrderBy(orderByFunction).ToList();

    // result is OK, but sort wasn't sent to data base ... it was done by my program which is quite time expensive and silly too ...
}

So, how I can change this behaviour, any ideas? Thank you in advance!

EDIT The solution provided for this question solves to do dynamic predicates, but you cannot apply them if you still use navigationProperty.GetValue(masterEntity). In that case the EF will fire SQL immediatley without any order or where clause ...

Upvotes: 3

Views: 217

Answers (1)

Yennefer
Yennefer

Reputation: 6234

Your database server is able to process TSQL statements only. Entity Framework (particularly the SQL Server pluging for Entity Framework) is capable of translating a small subset of C# expressions in a valid TSQL (in your case for an order by statement).

When your expression is too complex (for example invokes methods, alters the state) to be translanted into TSQL, Entity Framework will resort to an in memory operation.

If your are using .NET Core, you can use the following snipped while registering the content to spot all the "unsupported" statements that are executed in memory.

var builder = new DbContextOptionsBuilder<MyContext>();
var connectionString = configuration.GetConnectionString("DefaultConnection");
builder.UseSqlServer(connectionString);

// the following line is the one that prevents client side evaluation
builder.ConfigureWarnings(x => x.Throw(RelationalEventId.QueryClientEvaluationWarning));

Givem that, which is important to understand when a custom expression is involved, LINQ requires a static expression to infer the ordering. However, you can generate a dynamic expression as suggested by LINQ Dynamic Expression Generation. Although I never tried the described approach, it seems to me a viable way to achieve what you ask.

Upvotes: 1

Related Questions