locked
LINQ filter by month and year RRS feed

  • Question

  • User521171331 posted

    Hi, I am new to LINQ.

    I managed to run some LINQ succesfully for CRUD.

    Here is an extract of my code:

            var payroll = from pm in dc.Payroll_Masters
                          from pd in dc.Payroll_Details                      
                          //where pm.Salary_date
                          select pm;
                          
            GridView1.DataSource = payroll;
            GridView1.DataBind();

    Previously when my SQL is like

            sql = " Select * FROM Payroll_Master pm "
            sql += " INNER Join Payroll_Details pd On pm.id = pd.payroll_id "
            sql += " WHERE month(salary_date) = '3' "
            sql += " AND year(salary_date) = '2017' "

    My questions is how do I do the same in LINQ?

    Wednesday, April 5, 2017 9:47 AM

All replies

  • User1930269528 posted

    You would need to use  DbFunctions class, please refer to the documentation for that:

    https://msdn.microsoft.com/en-us/library/system.data.entity.dbfunctions%28v=vs.113%29.aspx?f=255&MSPPError=-2147217396

    Hope it helps!

    Wednesday, April 5, 2017 4:06 PM
  • User753101303 posted

    Hi,

    Or you could simply use a criteria such as pm.Salary_date>=StartDate && pm.Salary_date<StartDate.AddMonth(1)

    Also the query plan should be better (ie it can take advantage of an index on the date column). Actually I'm using that even when using SQL rather than LINQ.

    Wednesday, April 5, 2017 4:34 PM
  • User-271186128 posted

    Hi ngaisteve1,

    Previously when my SQL is like

            sql = " Select * FROM Payroll_Master pm "
            sql += " INNER Join Payroll_Details pd On pm.id = pd.payroll_id "
            sql += " WHERE month(salary_date) = '3' "
            sql += " AND year(salary_date) = '2017' "

    My questions is how do I do the same in LINQ?

    You could refer to the following code:

                    var query = from ee in db.Employees.ToList()
                                join cc in db.Orders.ToList()
                                on ee.EmployeeID equals cc.EmployeeID
                                where ee.HireDate.Value.Year == 1992
                                where ee.HireDate.Value.Month == 8
                                select new
                                {
                                    ee.EmployeeID,
                                    ee.FirstName,
                                    ee.HireDate,
                                    cc.OrderDate
                                };

    Best regards,
    Dillion

    Thursday, April 6, 2017 2:09 AM
  • User521171331 posted

    Gonna try it. Thanks

    Thursday, April 6, 2017 1:38 PM