Select query on sub table

Question

Im writing a basic CRM to capture leads.

You are able to add notes to leads.

I want to return a list of leads that have no notes within the last two days.

I cant work out the linq query to enable this.

So far i have the following which will return all the leads without a note.

vm.LeadsNoNotes = _context.Lead.Include(x => x.Notes).Where(x => x.Notes.Count == 0).Take(10).ToList();

below is my model structure

public class Lead
{
    public int LeadId { get; set; }
    public DateTime? DateCreated { get; set; }
    public DateTime? DateModified { get; set; }

    public string Name { get; set; }
    public string Email { get; set; }
    public string Telephone { get; set; }

    public string Comment { get; set; }

    public string Status { get; set; }

    public string Source { get; set; }

    public string PreferedContactMethod { get; set; }

    public string Vehicle { get; set; }

    public List<Note> Notes { get; set; }

}

public class Note
{
    public int NoteId { get; set; }
    public int? LeadId { get; set; }
    public int? CreditApplicationId { get; set; }
    public DateTime DateCreated { get; set; }
    public DateTime DateModified { get; set; }
    public DateTime NoteDate { get; set; }


    public string UserId { get; set; }

    [Required]
    public string Subject { get; set; }

    [DataType(DataType.MultilineText)]
    public string Description { get; set; }
}

Where is your date condition part of the 'WHERE clause'. Do you have date field in your notes table? You need to add the date condition in your where clause so that you can restrict the output only to the notes that are entered within last 2 days. Also, is Take(10) for paging purpose? — Learning Curve, Oct 31 '17 at 16:31

Andrei · Accepted Answer · 2017-10-31T16:46:09.860

Assuming Note class has something like DateCreated property, you want leads that:

Either have no notes at all
Or have some notes, but then all notes should be older then two days

That directly translates into the following condition:

var twoDaysAgo = DateTime.Now.AddDays(-2);

.Where(
    x =>
        x.Notes.Count == 0
        || 
        x.Notes.All(note => note.DateCreated.Date < twoDaysAgo.Date);
)

Notice .Date - this makes sure only dates are compared. Remove if time should also be taken into account.

Also, that will work even without first part of the condition, because All returns true for empty queryables, but that might be a bit not intuitive.

score 0 · Answer 2 · answered Oct 31 '17 at 16:41

0

vm.LeadsNoNotes = _context.Lead.Include(x => x.Notes).Where(x => x.Notes.Count == 0 && x.Notes.DateCreatd > DateTime.Now().AddDays(-2)).Take(10).ToList();

I have included a date comparison condition to your where clause. I haven't tested the above code but you can give it a try. It will give you an idea. You can modify the date part to get the exact result.

answered Oct 31 '17 at 16:41

Learning Curve

1,449
7
30
60

i am unable to access the property of notes.datecreated as it returns a list – PowerMan2015 Oct 31 '17 at 16:45

Select query on sub table

2 Answers2