Tuesday, September 24, 2013

How To Delete table row in sql database by LINQ query?


First of all get the row form id 

This is a method for delete row from summary table.

So here I pass id for which I want to delete the row

This is database context 

private UserContext database = new UserContext("UserConnection");


table name :summeries

   public ActionResult del_summary(int id = 0)
        {
            var summary_delete = database.summeries.Find(id);

            var delete = database.summeries.Remove(summary_delete);

            database.SaveChanges();

            return RedirectToAction("ExceptionListing");

        }


It will delete row from table.

Thanks for visiting.Please comment here if you have any query regarding it.

Tuesday, September 17, 2013

How To make dynamic query in LINQ in MVC4

Make Dynamic query in Linq

There are two way to make dynamic query in Linq

  1. Use Dynamic LINQ libarary
  2. Use PredicateBuilder

Use Dynamic LINQ libarary


Step1:You need to install DynamicLINQ library by Package Manager Console

To install DynamicLINQ, run the following command in the Package Manager Console
PM> Install-Package DynamicLINQ


Step 2: Write Query

Case: I want to write the dyanmic query for search form where fields are menitoned below



So here I have made linq query in two way 

1.By variable 
2.By same as sql server


string strWhere = string.Empty;// initialize string strWhere 

 strWhere = strWhere + "TRNDTE >= @0 and TRNDTE<=@1 and PRODVAL >=@2 and PRODVAL<=@3"; // assign some fields value in variable as it will give error in below manner

Error:opernads >= and <= incompatible with linq;

To avoid above error I assign those value in variable and assign to string 


Now rest of code similar to sql server dynamic query and assign to string strWhere 

            if (!string.IsNullOrEmpty(vouchers.CUSTNAME))
            {
                if (!string.IsNullOrEmpty(strWhere))
                    strWhere = strWhere + " and ";
                strWhere = strWhere + "CUSTNAME.StartsWith(\"" + vouchers.CUSTNAME + "\")";
            }

            if (!string.IsNullOrEmpty(vouchers.PANNUM) && vouchers.PANNUM != "0")
            {
                if (!string.IsNullOrEmpty(strWhere))
                    strWhere = strWhere + " and ";
                strWhere = strWhere + "PANNUM == \"" + vouchers.PANNUM + "\"";

            }
            if (!string.IsNullOrEmpty(vouchers.TRNNUM) && vouchers.TRNNUM != "0")
            {
                if (!string.IsNullOrEmpty(strWhere))
                    strWhere = strWhere + " and ";
                strWhere = strWhere + "TRNNUM == \"" + vouchers.TRNNUM + "\"";

            }
           
            if (vouchers.CRDTYPE != 0)
            {
                if (!string.IsNullOrEmpty(strWhere))
                    strWhere = strWhere + " && ";
                strWhere = strWhere + "CRDTYPE == " + vouchers.CRDTYPE;

            }

Finally pass string to lambda representation and replace variable to value pass to function 
            var vouchers_search = database.vouchers.Where(strWhere, TRNDTE_ST, TRNDTE_EN, PRODVAL_MIN, PRODVAL_MAX);

return vouchers_search;

If still you have a query please comment here ,I'll definitly try to help you 

Thanks for visit