CODEWITHSUNDEEP
c#sqllinq.net-coreef-core-3.0
Denys Matsiura
Denys Matsiura

Reputation: 1

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

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??

Upvotes: 0

Views: 8722

Answers (2)

Peter B
Peter B

Reputation: 24147

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);
}

Upvotes: 2

Iria
Iria

Reputation: 497

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.

Upvotes: 0

Related Questions