Asp / C# .Net Entity Framework - Create Extended Parameterized Query based on Conditions

Question

In ADO.Net I created a DAL file that took parameters from the user and if they were not null, the query got extended based on if statements. Now I want to do the same in Entity Framework. I have searched a number of sites including Stack Overflow but failed to get my satisfactory answer. for Reference, the following link also could not help me

Select Query with Where condition depending on list values in asp.net

the required scenario is

cmd.text = "SELECT FROM tbl_name WHERE id>0 "

if(param_value != null)
{
    cmd.text += " AND (param_name = '@param_value')";
    if(!cmd.contains("param_name"))
          cmd.parameters.addwithvalue("param_name", @param_value);
    cmd.parameters["param_name"] = @param_value;
}

// proceed further with cmd.text

please ignore the syntax right now, I just wanted to convey my concept of what I want to do.

I want to apply the same concept for Entity Framework

Well two days back I found a scenerio in wheich the query (text) was built in an aspx.cs file and it was passed as it is to a custom built function in DAL which passed the text to cmd.text and processed the rest of retrieval in an ADO.net style.

This method is potentially dangerious as anyone with a bit knowlege can break this security down to grounds. I want to create a query that has parameters as well as its vales like I have shown in above code block.

then please tell me any alternative solutions for the sake so that I could perform my custom selections. — ahmednawazbutt, Feb 18 '15 at 06:45
@GulabMehak Please, take a look at this: http://stackoverflow.com/questions/13233461/dynamic-conditions-in-linq-to-entities-query and this http://stackoverflow.com/questions/9122220/dynamic-where-clause-in-linq-to-entities — Augusto Barreto, Feb 18 '15 at 15:57

score 0 · Answer 1 · answered Feb 18 '15 at 17:26

Using LINQ-to-SQL:

var param_value = 0;
db.tbl_name.Where(x => x.id > 0).Where(x => x.some_property == param_value).ToString();

If you look at the generated SQL, you'll see that's its parameterized (it picks the param_names, though).

I added the ToString() at the end, just so you could see the resulting SQL; based on OP, I'd say to leave this off, and keep modifying the query further directly in LINQ, rather than converting to SQL as a string and concatenating.

score 0 · Accepted Answer · edited May 23 '17 at 11:53

I just found out working with Entity framework is a totally different world that classic approach. In here, we work with models/objects and their relationships with each other and we access them based on that relationship. So to answer the question we need to get that model first like

Movie movie = db.Movies.Find(id);

and than from that, we get a model object which does have different properties in it like title, IMDb, rating etc. We get them repeatedly using where clause as below:

db.Where(movies=>movies.IMDb==10).Where(movies=>movies.title=="The Game Plan")

this all is equal to the following in classic approach

AND (IMDb = 10) AND (title = 'The Game Plan')

following that, one can extend his query as much as he likes. Again ignore the syntax here because I am here t convey the idea only.

For reference, the following links might be helpful keeping in mind the context i have explained.

Multiple where conditions in EF - StackOverflow

Asp / C# .Net Entity Framework - Create Extended Parameterized Query based on Conditions

2 Answers2