0

I am new to C# world and Entity Framework.

I need to retrieve items from a table which are over due in 30 days. I am aware of the filtering I can do on the SQL side (with a stored procedure), but I am looking for a solution in Entity Framework.

This is my current code: 

using (var db = new MyDbContext())
{
    var deliverableItems = db.Deliverables
            .ToList() // fetch all records from Deliverable table.
            .Where(d => Is30DaysDue(d));

    foreach (var deliverable in deliverableItems)
    {
        Console.WriteLine(deliverable.DeliverableTitle);
        // perform action on due items....
    }
}

internal static bool Is30DaysDue(Deliverable deliverable)
{
    var deliverableDueDate = deliverable.DeliverableRevisedDueDate ?? deliverable.DeliverableDueDate.Date;
    var dateDiff = (deliverableDueDate.Date - DateTime.Now.Date).TotalDays;
    return dateDiff == 30;
}

As you can see in line 4, I am first retrieving all records from the Deliverables table and then doing the required filtering.

Is there any way to fetch 30 days due items using Entity Framework?

Edit:

Forgot to mention before, I have two dates DeliverableRevisedDueDate and DeliverableDueDate. If revised due date is null only then deliverable due date is considered.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
R G
  • 3
  • 8
  • So you want to retrieve data that are 30 days? – AT-2017 Aug 27 '16 at 17:23
  • yes, wch are due in 30 days – R G Aug 27 '16 at 17:24
  • I'm not sure if you know what Entity Framework is. It's basically a way to materialize the data in your database into C# objects. The current code is perfectly fine although you could put the `Where` before the `ToList` to do database-side filtering. – Jeroen Vannevel Aug 27 '16 at 17:24
  • If I put ``where`` before ``ToList``, I get this exception "LINQ to Entities does not recognize the method, and this method cannot be " – R G Aug 27 '16 at 17:27
  • Because it can't translate your custom method to a built-in method in the database. Remove all the noise from the method and just use an inline lambda, it will understand that. – Jeroen Vannevel Aug 27 '16 at 17:30
  • @JeroenVannevel forgot to mention before, I have two dates ``DeliverableRevisedDueDate`` and ``DeliverableDueDate``. if revised due date is null only then deliverable due date is considered, I'm not sure how to use both dates in inline lambda – R G Aug 27 '16 at 17:35

1 Answers1

0

Edited

I've forgotten that some datetime functionalities does not support by linq to entity. so you have to use it like this

var newDate = DateTime.Now.Date.AddDays(-30);
 var deliverableItems = db.Deliverables
 .ToList() // fetch all records from Deliverable table.
.Where(d => d.DeliverableDueDate.Year == newDate.Year &&
d.DeliverableDueDate.Month == newDate.Month&&
d.DeliverableDueDate.Day == newDate.Day  );
Ashkan S
  • 10,464
  • 6
  • 51
  • 80
  • when I try ``.Where(d => d.DeliverableDueDate.Date == DateTime.Now.Date.AddDays(-30));`` . I get following exception An ``unhandled exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll Additional information: The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.`` – R G Aug 27 '16 at 17:53
  • @RG sorry, check this out – Ashkan S Aug 27 '16 at 20:18