Is there a way to set a where clause in Entity Framework to a parameter?

Question

I have this EF qQuery

[HttpGet]
public IActionResult GetCISCustomers(string Option, string Input)
{
    using (var db = new cismodel.eedcbuildingdbContext())
    {
        var result = from a in db.ConsolidatedCustomerDatabase
                     where (a.AccountNo == Input)
                     select new
                            {
                                AccountNo = a.AccountNo
                            };

        string json = Newtonsoft.Json.JsonConvert.SerializeObject(result, Newtonsoft.Json.Formatting.Indented);

        return Ok(json);
    }
}

Is it possible to make the a.AccountNo to be the (Option) parameter as I want users to be able to pass in different options such as (AccountNo, MeterNo, Name) and different input.

Unclear what you are asking, `where (a.AccountNo == Input) || (a.AccountNo == Option)`? — mxmissile, Nov 16 '21 at 14:29
So you want the user to be able to specify, using a string, on which column your query will query? — CodeCaster, Nov 16 '21 at 14:31
That's a little bit dangerous. You'd have to at least make sure the option exists. So, I'd probably use an Enum , split the query and add the where clause depending on the enum value. Remains the problem in the selector. You'd need to return the respective result, too. — Fildor, Nov 16 '21 at 14:31
I get this i do not know what the user will like to do the where on. The user could use where(a.AccountNo == input) where (a.Meterno == Input). I want it to be something like where(a.Option == input) — Anikwenwa Tosan, Nov 16 '21 at 14:32
@Fildor Yes that is what i want. I can ensure the options exist. Can you write the query structure to your suggestion? — Anikwenwa Tosan, Nov 16 '21 at 14:34
I amended my prior comment. One thing I did not take into account: When I did this, I always returned the same result type. I used it just as a filter. But here this would also affect the result. — Fildor, Nov 16 '21 at 14:35
Well, not really like this. using `var` for `result` hides that it actually has a type. That is an anonymous type, which holds an `AccountNo`. That type is not dynamic, i.e. it has to be fixed at compile time. So this way you cannot simply switch it to be an anonymous type that holds a `Meterno` or so. — JHBonarius, Nov 16 '21 at 15:05
Please see my answer to [this question](https://stackoverflow.com/q/57607655/592958) which shows how to create a dynamic expression that takes a string value to create a filter. It's a little more complicated but is completely generic and won't require maintenance when properties are added or removed from your model and could be modified to act on any class. — phuzi, Nov 16 '21 at 19:28

score 1 · Answer 1 · edited Aug 19 '22 at 20:23

1

You can write base query as IQueryable and have filtering based on your condition :

 IQueryable<EntityName> query = db.ConsolidatedCustomerDatabase;

 if(option == "A")
 {
      query = query.Where(x=>x.A == Input);
 }
 else if (option == "B")
 {
      query = query.Where(x=>x.B == Input);
 }

When you are done with adding filtering, you can materialize the query:

 var result = query.Select(x => new { AccountNo = x.AccountNo }).ToList();

edited Aug 19 '22 at 20:23

marc_s

732,580
175
1,330
1,459

answered Nov 16 '21 at 14:35

Akash KC

16,057
6
39
59

It's the general idea like this that'll work (if you declare `query` as `IQueryable`), but stuff like this quickly turns into a maintenance nightmare. – CodeCaster Nov 16 '21 at 14:37
OP will still have to alter the select, too. If I query for `MeterNo`, I don't want `AccountNo` as the result. – Fildor Nov 16 '21 at 14:37
1

@Fildor I don't see that in the OP's request. They want to filter the same collection on different properties, but might just want to return all properties. – CodeCaster Nov 16 '21 at 14:38
@CodeCaster Ah, you may be right. I somehow inferred that. – Fildor Nov 16 '21 at 14:39
@CodeCaster : I agree with you on maintenance point but IQueryable come in handy when you've to write conditional filtering. – Akash KC Nov 16 '21 at 14:40
1

@CodeCaster: Fixed, Thanks. – Akash KC Nov 16 '21 at 15:26

JHBonarius · Answer 2 · 2021-11-16T15:20:40.820

1

You can pass an Expression as a method parameter, e.g.

AccountNoType GetAccountNoForSelector(Expression<Func<Customer, bool>> selector)
{
    return db.ConsolidatedCustomerDatabase
        .Where(selector)
        .Select(c => c.AccountNo)
        .FirstOrDefault();
}

Then call it like

var myAccount = GetAccountNoForSelector(c => c.MeterNo == 100);

edited Nov 16 '21 at 15:20

answered Nov 16 '21 at 15:10

JHBonarius

10,824
3
22
41

score 0 · Answer 3 · answered Nov 16 '21 at 14:32

0

If you know the possible values of Option, then you can build a conditional logic like below.

    var result = from a in db.ConsolidatedCustomerDatabase
                 where (Option == "AccountNo" && a.AccountNo == Input) 
                        || (Option == "MeterNo" && a.MeterNo == Input)
                 select new
                 {
                     AccountNo = a.AccountNo
                 };

answered Nov 16 '21 at 14:32

Deniz

356
1
6
12

Is it possible not to hard code the Option = "AccountNo", Option = "AccountNo" and make it open. I will prefer to pass it from the web api get endpoint – Anikwenwa Tosan Nov 16 '21 at 14:36
I will like my web api to be called as localhost:2067/api/Incidence/GetCISCustomers?Option=AccountNo&&Input=0003403108 localhost:2067/api/Incidence/GetCISCustomers?Option=MeterNo&&Input=0003403108 – Anikwenwa Tosan Nov 16 '21 at 14:39
I have not build that but it seems it's possible by using reflection. [link](https://michael-mckenna.com/sorting-iqueryables-using-strings-and-reflection/) Anyway, i would not prefer to use it that way. I will go with either building conditional logic or create multiple routes for options. – Deniz Nov 16 '21 at 14:48
1

Ok. Creating multiple routes for options is pretty easy i just wanted to see the possibility of having just one dynamic route – Anikwenwa Tosan Nov 16 '21 at 14:56
Yep, i think it also will be a cleaner approach. – Deniz Nov 16 '21 at 15:01

Is there a way to set a where clause in Entity Framework to a parameter?

3 Answers3