Retrieving Data from database within the last 7 days using linq

Question

I have a view with data and I need to retrieve data only from the last 7 days. I know there is a function for this if I was using sql query. but I'm using Linq.

here is my code:

                try
                {
                var query = (from bwl in mg.BarcodeWithLocation
                             select new
                             {
                                 RequestID = bwl.RequestID,
                                 Barcode = bwl.Barcode,
                                 adrid = bwl.AdrID,
                                 name = bwl.Name,
                                 street = bwl.Street,
                                 houseno = bwl.HouseNo,
                                 postal = bwl.Postal,
                                 city = bwl.City,
                                 country = bwl.Country,
                                 latitudetxt = bwl.Latitude == "" ? "Location Unknown" : "View Map Location",
                                 latitude = bwl.Latitude,
                                 longitude = bwl.Longitude,
                                 date = bwl.ReceivedDate
                             });

                this.Grid.DataSource = query;
                this.Grid.DataBind();
                }
                catch (Exception exception)
                {
                      Console.WriteLine("ERROR in GetNoLocationScan() method. Error Message : " + exception.Message);
                }

can anyone tell me how I do this in Linq ?

Solution 1

You can use DateTime.Now.AddDays(-7) to get the record seven day older then current date. Or you can use Datime.Today.AddDays(-7) if you want the time part and start from after 12 PM.

var query = (from bwl in mg.BarcodeWithLocation
              where(bwl.ReceivedDate > DateTime.Now.AddDays(-7))
                         select new
                         {
                             RequestID = bwl.RequestID,
                             Barcode = bwl.Barcode,
                             adrid = bwl.AdrID,
                             name = bwl.Name,
                             street = bwl.Street,
                             houseno = bwl.HouseNo,
                             postal = bwl.Postal,
                             city = bwl.City,
                             country = bwl.Country,
                             latitudetxt = bwl.Latitude == "" ? "Location Unknown" : "View Map Location",
                             latitude = bwl.Latitude,
                             longitude = bwl.Longitude,
                             date = bwl.ReceivedDate
                         });

Solution 2

Try this:

    var dt = DateTime.Now.AddDays(-7);
    var query = (from bwl in mg.BarcodeWithLocation
                 where bwl.ReceivedDate > dt 
                         select new
                         {
                             RequestID = bwl.RequestID,
                             Barcode = bwl.Barcode,
                             adrid = bwl.AdrID,
                             name = bwl.Name,
                             street = bwl.Street,
                             houseno = bwl.HouseNo,
                             postal = bwl.Postal,
                             city = bwl.City,
                             country = bwl.Country,
                             latitudetxt = bwl.Latitude == "" ? "Location Unknown" : "View Map Location",
                             latitude = bwl.Latitude,
                             longitude = bwl.Longitude,
                             date = bwl.ReceivedDate
                         });

Comments

[Uriil]

I think you can't use DateTime.Now.AddDays(-1) inside LINQ to Entity

[Paolo Moretti]

Or DateTime.Today.AddDays(-7), if you don't want to filter by time