Entity framework query to include child entities of entity collection

Question

I have a ticket entity that has ticketNotes (0 to many) and would like to pull back the user details for who created each note when I query tickets.

I use the following code to query a ticket

var ticket = (from t in context.Tickets
.Include(t=>t.Site)
.Include(t=>t.Caller)
.Include(t=>t.Caller.Site)
.Include(t => t.Notes)
.Include(t=>t.OpenedByUser)
select t).First(t => t.TicketId == ticketId);

My TicketNote Class is:

public class TicketNote
{
    public Guid TicketNoteId { get; set; }
    public string NoteText { get; set; }
    public DateTime CreatedTime { get; set; }
    public Guid TicketId { get; set; }
    public virtual Ticket Ticket { get; set; }
    public bool ReadOnly { get; set; }
    public DateTime? DateTimeDeleted { get; set; }
    public Guid TenantId { get; set; }

    [Required]
    [DefaultValue(typeof(Guid), "00000000-0000-0000-0000-000000000000")]
    public Guid CreatedByUserId { get; set; }
    [ForeignKey("CreatedByUserId")]
    public virtual User CreatedByUser { get; set; }
 }

I would like to add

.Include(t => t.Notes.CreatedByUser)

However as notes is a collection I don't get the option.

Please advise the best approach to achieve pulling back the CreatedByUser which is NULL at the moment.

Thanks

Not sure if Any() would work here - .Include(t => t.Notes.Any().CreatedByUser)? — Kami
– Kami, Commented Apr 23, 2015 at 9:22
Hi, Thanks I just tried this but I don't have any entity options after any so it doesn't work. — David Hawkins
– David Hawkins, Commented Apr 23, 2015 at 9:24
Maybe this article will help you: link. Especially parts concerning explicit loading. — sszarek
– sszarek, Commented Apr 23, 2015 at 9:27

Richard · Accepted Answer · 2015-04-23 09:36:40Z

2

You can include more complex statements to get at multilevel collections. Try the following:

.Include(t => t.Notes.Select(n => n.CreatedByUser))

answered Apr 23, 2015 at 9:36

Richard

30.7k12 gold badges79 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andriy F. · Accepted Answer · 2015-04-23 09:26:50Z

0

You have ticket variable which have property Notes inside it. For performance you may use

ticket.Notes.Select(t => n.CreatedByUser)

answered Apr 23, 2015 at 9:26

Andriy F.

2,5451 gold badge26 silver badges23 bronze badges

1 Comment

David Hawkins Over a year ago

Please can you advise how to use this when returning the ticket? I return a ticket entity in my repository so can't call a select later as the context is closed.

Collectives™ on Stack Overflow

Entity framework query to include child entities of entity collection

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related