CODEWITHSUNDEEP
c#entity-frameworklinqlambda
Maher Khalil
Maher Khalil

Reputation: 539

Dynamic query in linq or lambda expression C#

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

Upvotes: 0

Views: 314

Answers (1)

Marco
Marco

Reputation: 23937

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.

Upvotes: 1

Related Questions