Dynamic query in linq or lambda expression C#

Question

I need to construct a dynamic query based on user input.

I used to do it using SqlCommand with string concatenation.

Example:

public ActionResult Receipt_Details(string FromDate, string ToDate, int Branch, int User, int RecietType, int PayMethod)
{
    if (FromDate == "")
        FromDate = "1980-01-01";

    if (ToDate == "")
        ToDate = "2180-01-01";

    string where = " where DmentDate>='"+FromDate+ "' and DmentDate<='"+ToDate +"'";

    if (Branch != 0)   // Branch = 0 means select all branches
        where += " and BranchID = " + Branch;

    if (User != 0)     // User = 0 means select all users
        where += " and UserID = " + User

    if (PayMethod != 0)
        where += " and PayMethodID = " + PayMethod

    string constr = "data source=.;initial catalog=db;integrated security=True;";

    using (SqlConnection con = new SqlConnection (constr))
    {
        con.Open();

        using (SqlCommand com=new SqlCommand())
        {
            com.Connection = con;
            com.command="Select * from Table "+ where;
        }
    }
}

Now I use Entity Framework. I want to use classes and linq or lambda expression

Thank you

the question how to do the same using linq or lambada expression because now i use entity framework — Maher Khalil
– Maher Khalil, Commented Mar 26, 2018 at 8:44
did you create EF context? what you have tried from your side..post your code. we will help you if there any errors in it. — user9405863
– user9405863, Commented Mar 26, 2018 at 8:44

Marco · Accepted Answer · 2018-03-26 08:53:41Z

1

If you want to refactor this approach into a solution, that uses EF and LINQ, then it works essentially the same way.

//query will be of type IQueryable<T>
var query = context.Receipts;

//modify the query based on your needs
if(Branch != 0)
   query = query.Where(x => x.Branch == Branch);
if (User!=0)
   query = query.Where(x => x.UserId == User);

/* ... and so on */

//execute
return query.ToList() // materialize your query with ToList or FirstOrDefault() or whatever you need.

A word of warning though, treating dates as strings is a receipt for disaster. Dates can be strongly typed as DateTime.

edited Mar 26, 2018 at 8:53

answered Mar 26, 2018 at 8:48

Marco

24.1k13 gold badges81 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Maher Khalil Over a year ago

thats correct i had to do that because the input from user return empty string

ChW Over a year ago

Well, but you can handle it. I suggest to use a nullable DateTime instead. Hence you can set a default value or leave it null. It's better than to use string.

Collectives™ on Stack Overflow

Dynamic query in linq or lambda expression C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related