locked
how to use  this Query in linq Lamda Expression RRS feed

  • Question

  • User457274204 posted

    SELECT *
    FROM StudentLedger
    WHERE
    MONTH(PaidAmountDate) = MONTH(dateadd(dd, -1, GetDate()))
    AND
    YEAR(PaidAmountDate) = YEAR(dateadd(dd, -1, GetDate())) and FeeTypeID=1 and StudentID=1654

    Wednesday, June 20, 2018 7:39 PM

All replies

  • User753101303 posted

    Hi,

    When selecting a date/time span even for a single month, I'm always using PaidAmountDate>=startValue && PaidAmountDate<endValue (here startValue being the first day of the targeted month and endValue the first day of the next month).

    The benefit is that it works the same way regardless of the exact date/time you want it can still use a possible index on PadAmountDate which is not possible when using MOTNH and YEAR (that I'm using as needed but not when this is just a "shortcut" to select a particular period).

    Wednesday, June 20, 2018 8:02 PM
  • User1520731567 posted

    Hi AhsanMicrosoft,

    According to your code, I suggest you could use AddDays() to instead of dateadd().

    I make a demo, you could refer to it:

    var query = db.StudentLedger.Where(p => p.PaidAmountDate.Month == DateTime.Now.AddDays(-1).Month && p.PaidAmountDate.Year == DateTime.Now.AddDays(-1).Year && p.FeeTypeID == 1 && p.StudentID == 1654).ToList();
    

    Best Regards.

    Yuki Tao

    Thursday, June 21, 2018 10:17 AM
  • User457274204 posted

    Hi Yuki Tao,

    My query

    var query = dc.StudentLedgers.Where(p => p.PaidAmountDate.Value.Month == DateTime.Now.AddDays(-1).Month && p.PaidAmountDate.Value.Year == DateTime.Now.AddDays(-1).Year && p.FeeTypeID == 4 && p.StudentID ==_id).ToList();

    Error Exception


    Additional information: LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)' method, and this method cannot be translated into a store expression.

    Friday, June 22, 2018 8:39 PM
  • User1120430333 posted

    Additional information: LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)' method, and this method cannot be translated into a store expression.

    What you are being told is that the EF engine cannot create T-SQL based on the criteria given for data.time.adddays. 

    You can make a variable that has the datetime thing you are doing and use the variable in the Linq query, but EF is not going to allow some kind of calculation with the datetime in the Linq query. 

    Saturday, June 23, 2018 3:08 PM
  • User1520731567 posted

    Hi AhsanMicrosoft,

    Additional information: LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)' method, and this method cannot be translated into a store expression.

    If so, you could modify your code as what @DA924 said:

     DateTime dt_today = DateTime.Now.AddDays(-1);
     var query = options.Where(p => p.Dateofbirth.Month == dt_today.Month && p.Dateofbirth.Year == dt_today.Year && p.EnumId == 2).ToList();

    But in my project it works well.

    As the picture shows,two methods in my project:

    Best Regards.

    Yuki Tao

    Monday, June 25, 2018 9:49 AM