C# - How to use Expression with parametrs in LINQ where

Question

Task: I need to give some expression with parameters into LINQ's where to get some data from database, but have an error above This example of working expression:

var shopExp = GetPersonForShop(PersonTypeIds.Director, new Guid("adda423f-8c38-40e0-9f39-6deceb787bc0")); // id
Where(shopExp)

But i need assign id dynamically, but got error above :

_repository.Persons
.Where(GetPersonForShop(PersonTypeIds.Director, person.PersonId)

And got error:

{"Unable to cast object of type 'System.Linq.Expressions.InstanceMethodCallExpression2' to type 'System.Linq.Expressions.LambdaExpression'."}

How does function for where(linq) look:

private Expression<Func<Person, bool>> GetPersonForShop(PersonTypeIds personTypeId, Guid personId)
        {
            return person => person .PeronTypeId== (int) personTypeId && person .PersonId == personId;
        }

This is approximate look like out production, just change names of parametrs code

How can I add expression with parameters to Where clause??

EF/EF Core already allow dynamic queries. There are multiple answers that show how to add `Where` clauses dynamically. I suspect the real problem is adding a "generic" repository on top of EF and losing the ability to perform LINQ queries — Panagiotis Kanavos, Jan 21 '20 at 13:59
I add some more info about this task. Yes, I know how lambda work, I need to move expression(the function that return expression) with parameters into 'where' linq method. — Denys Matsiura, Jan 21 '20 at 14:17
I'm confused. First you use `GetPersonForShop` but then you show us the `GetKpisForEntity` method with the same (apparent) signature. Are you not just confused between these two method names? There are also a lot of typos in your code, which makes it impossible for us to judge if you made these while writing the question or writing the code itself. — Flater, Jan 21 '20 at 14:19
From what i can get, this should help you: [ref] https://stackoverflow.com/questions/14297633/c-sharp-pass-lambda-expression-as-method-parameter — venter, Jan 21 '20 at 14:23
Yes, I paste not that function, my bad. But in code its not confused, vs will give error for this confuse after all). Ask is how to move GetPersonForShop with parametrs into Where, like this .Where(GetPersonForShop(PersonTypeIds.Director, person.PersonId)) — Denys Matsiura, Jan 21 '20 at 14:24
If you press F12 on `Where`, Visual Studio should bring you to `public static IQueryable Where(this IQueryable source, Expression> predicate);` With the syntax you posted, it should work. Which .net framework are you using? — Manuel Fabbri, Jan 21 '20 at 14:31
If EF Core can't convert to SQL, you will get an error. Your question has no error message - what is it? — NetMage, Jan 21 '20 at 19:29

Peter B · Answer 1 · 2020-01-21T15:40:10.243

Lambda expressions use => notation. Try something like this:

var idToFind = new Guid("adda423f-8c38-40e0-9f39-6deceb787bc0");

var result = _repository.Persons
    .Where(p => p.TypeId == PersonTypeIds.Director && p.PersonId == idToFind);

In this expression, p represents each Person record in the Persons table, compared one-by-one using the boolean expression that follows it.

Depending on your datasource, the comparison for each p will either be done by .NET in memory, or it will happen inside your database using a SQL WHERE clause which is constructed from the boolean expression. The last would be optimal because it would mean that not the entire Persons table has to be transferred into .NET memory before comparison can take place.

Update - To apply the same condition multiple times without repeating it in your code, while still keeping the advantages of LINQ to SQL translation intact, you can put the condition in an Expression<Func<Person, bool>> object and then use that multiple times:

Expression<Func<Person, bool>> expression =
    p => p.TypeId == PersonTypeIds.Director && p.PersonId == idToFind;

var result1 = datasource1.Where(expression);
var result2 = datasource2.Where(expression);
var result3 = datasource3.Where(expression);

Or through a method that produces the Expression object:

var result1 = datasource1.Where(GetExpression(idToFind));
var result2 = datasource2.Where(GetExpression(idToFind));
var result3 = datasource3.Where(GetExpression(idToFind));

public Expression<Func<Person, bool>> GetExpression(Guid idToFind)
{
    return p => p.TypeId == PersonTypeIds.Director && p.PersonId == idToFind;
}

Or alternatively you can use a helper method:

var result1 = FilterByTypeAndId(datasource1, idToFind);
var result2 = FilterByTypeAndId(datasource2, idToFind);
var result3 = FilterByTypeAndId(datasource3, idToFind);

public IQueryable<Person> FilterByTypeAndId(IQueryable<Person> datasource, Guid idToFind)
{
    return datasource.Where(p => p.TypeId == PersonTypeIds.Director && p.PersonId == idToFind);
}

Problem that this where repeats in 11 places in code, thats why i want to use expression to simplify it. More, i have more complicated logic for select, but cant use becouse where want to use expression without paramets — Denys Matsiura, Jan 21 '20 at 14:07

score 0 · Answer 2 · answered Jan 21 '20 at 15:29

based on the previous response, I am going to give you a few alternatives and suggestions.

var idToFind = new Guid("adda423f-8c38-40e0-9f39-6deceb787bc0");

var result = _repository
    .Persons
    .Where(p => p.TypeId == PersonTypeIds.Director)
    .Where(p => p.PersonId == idToFind)
    .ToList();

First is doing the where clause in 2 steps and then, adding the ToList(), with the ToList(), you will deal with collections and LINQ that is pretty useful. And by doing the where clause in 2 steps, is more for readable purposes.

C# - How to use Expression with parametrs in LINQ where

2 Answers2