Wednesday, 27 November 2013

Composable LINQ to SQL query with dynamic Parameters

var _companyId = 1;
var Name = "OP";
var FromDate;
var  EmpId;
 var query = from audit in edmx.tblAudit
              from asst in edmx.tblAssets.Where(x => x.AutoId == audit.Asset_Id)
              where audit.Company_Id == _companyId
                  && asst.Name.Contains(string.IsNullOrEmpty(Name) ? asst.Name : Name)
           select new
             {
                        AssetName = asst.Name,
                        Serial_No = asst.Serial_No,
                        EmployeeName = audit.EmpName,
                        Date = audit.Audit_Date
             };

if (FromDate.Length > 7)
{
     var _FromDate = Convert.ToDateTime(FromDate);
     query = query.Where(x => x.Date >= _FromDate);
}
if (EmpId.Length > 0)
{
     var _empId = Convert.ToInt16(EmpId);
     query = query.Where(x => x.EmployeeId == _empId);
 }

 var auditData = (from asst in query
          asst.AssetName, asst.Serial_No, asst.EmployeeName,
          asst.Date, asst.AssetType
          }).OrderBy(x => x.AssetType).ToList();



looking like :
SELECT AssetName, Serial_No, EmployeeName,Date,AssetType FROM [dbo].[tblAssets] AS [t0]
 Inner join tblAudit t1 on t1.Asset_Id = t0.AutoId
WHERE (t0.
AssetName like '%op%') AND (t0.Fromdata >= '01/01/2013') AND (t1.empId = 11) 

for more information please see below link :
http://blogs.msdn.com/b/swiss_dpe_team/archive/2008/06/05/composable-linq-to-sql-query-with-dynamic-orderby.aspx?CommentPosted=true#commentmessage

No comments:

Post a Comment