Entity Framework - LINQ - Use Expressions in Select

Question

I am using within my code some EF LINQ expressions to keep complex queries over my model in one place:

public static IQueryable<User> ToCheck(this IQueryable<User> queryable, int age, bool valueToCheck = true)
{
    return queryable.Where(ToBeReviewed(age, valueToCheck));
}

public static Expression<Func<User, bool>> ToCheck(int age, bool valueToCheck = true)
{
    return au => au.Status == UserStatus.Inactive
        || au.Status == UserStatus.Active &&
        au.Age.HasValue && au.Age.Value > age;
}

I am then able to use them in queries:

var globalQuery = db.Users.ToCheck(value);

And also in selects:

var func = EntityExtensions.ToCheck(value);

var q = db.Department.Select(d => new
{
    OrdersTotal = d.Orders.Sum(o => o.Price),
    ToCheck = d.Users.AsQueryable().Count(func),
})

What I am trying to achieve is to actually use the same expression/function within a select, to evaluate it for each row.

var usersQuery = query.Select(au => new {
    Id = au.Id,
    Email = au.Email,
    Status = au.Status.ToString(),
    ToBeChecked = ???, // USE FUNCTION HERE
    CreationTime = au.CreationTime,
    LastLoginTime = au.LastLoginTime,
});

I am pretty that threre would be a way using plain EF capabilities or LINQKit, but can't find it.

can you explain difference between `db.Department.Select(d => new { OrdersTotal = d.Orders.Sum(o => o.Price), ToCheck = d.Users.AsQueryable().Count(func), })` and what you want? — Grundy, Jan 10 '18 at 17:42
2 first examples are used as functions to query the set, the last one is where I want to reuse my function on a specific row to get its value — Benjamin Soulier, Jan 10 '18 at 17:45
The sample function is the last one, previous calls are done to filter or count on the entity. This last one is to query and retrieve the value itself for each row — Benjamin Soulier, Jan 10 '18 at 18:05
@ivan-stoev I have been checking LINQKit but need help on how to make this work — Benjamin Soulier, Jan 10 '18 at 18:21
If performance isn't too much of a concern, you _could_ execute the query, and then use `.Select` on the retrieved data: `var users = query.ToList().Select(au => ...)`. That would allow you a lot more freedom in terms of what you can use in the `Select`. — JLRishe, Jan 10 '18 at 18:21
Take a look at `AsExpandable` and `Invoke` custom extension methods. Something like `query.AsExpandable().Select(.... ToBeChecked = func.Invoke(au))`. Another example - https://stackoverflow.com/questions/38398507/entity-framework-using-expressions-to-build-global-and-reusable-filter-query-rul/38401783#38401783 — Ivan Stoev, Jan 10 '18 at 18:23
Did you mean for your function to be `ToBeReviewed` and not `ToCheck`? — NetMage, Jan 10 '18 at 20:55

score 3 · Accepted Answer · answered Feb 28 '18 at 17:03

Answering my own question :)

As pointed by @ivan-stoev, the use of Linqkit was the solution:

var globalQueryfilter = db.Users.AsExpandable.Where(au => au.Department == "hq");

var func = EntityExtensions.ToCheck(value);
var usersQuery = globalQueryfilter.Select(au => new
{
    Id = au.Id,
    Email = au.Email,
    Status = au.Status.ToString(),
    ToBeChecked = func.Invoke(au),
    CreationTime = au.CreationTime,
    LastLoginTime = au.LastLoginTime,
});
return appUsersQuery;

It's required to use the AsExpandable extension method from Linqkit along with Invoke with the function in the select method.

score 0 · Answer 2 · answered Sep 21 '21 at 09:16

I want to add one more example:

Expression<Func<AddressObject, string, string>> selectExpr = (n, a) => n == null ? "[no address]" : n.OFFNAME + a;

var result = context.AddressObjects.AsExpandable().Select(addressObject => selectExpr.Invoke(addressObject, "1"));

Also, expression can be static in a helper. p.s. please not forget to add "using LinqKit;" and use "AsExpandable".

Entity Framework - LINQ - Use Expressions in Select

2 Answers2

Linked

Related