LINQ include and Projection

Question

I have some classes defining entities with relationships

Account 
has many Conversations [IEnumerable<Conversation> Conversations]

Conversation 
has many Participants [IEnumerable<Account> Participants]
has many Messages [IEnumerable<Message> Messages]

Message 
has one Sender [Account Sender]
has one Conversation [Conversation Conversation]

I'm trying to write a LINQ query that returns a list of Conversation ordered by date and including related participants and messages.

public async Task<List<Conversation>> FindAllByAccountIdAsync(Int32 id)
{
    return await _Db.Conversations
         .Where(c => c.Participants.Any(p => p.AccountId == id))
         .Include(c => c.Participants)
         .Include(c => c.Messages)
         .ToListAsync();
}

This do the work but includes to much data i do not really need.

public async Task<List<Conversation>> FindAllByAccountIdAsync(Int32 id)
{
    return await _Db.Conversations
         .Where(c => c.Participants.Any(a => a.AccountId == id))
         .Include(c => c.Participants.Select(a=> new 
                       {
                          AccountId = a.AccountId,
                          Profile = new { FullName = a.Profile.FullName,
                                          Email = a.Profile.Email
                                        }                        
                       }))
         // Only return the last message in 
         // Eventually I would not return an array with a single object but just the single object inside associated with the property LastMessageIn
         .Include(c => c.Messages.OrderBy(m => m.Date).Select(m=> new 
                       {
                          Body = m.Body,
                          SenderId = m.Sender.AccountId
                       }).Last())
         .ToListAsync();
}

This script returns a mile long exception

{"message":"An error has occurred.","exceptionMessage":"The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties........}

My mind resist understanding and learning LINQ I do not know if its just me but as soon requirements exceeds basic querying and projection it break out of my control

Someone have some hints?

Fabio · Accepted Answer · 2015-07-25T13:47:03.050

4

I'm not sure if I understand your question, but I believe you want something like this:

public async Task<List<Conversation>> FindAllByAccountIdAsync(Int32 id)
{
    return await _Db.Conversations
         .Where(c => c.Participants.Any(p => p.AccountId == id))
         .Include(c => c.Participants)
         .Include(c => c.Messages)
         .Select(c => new 
         {
            Participants = c.Participants.Select(a=> new 
                       {
                          AccountId = a.AccountId,
                          Profile = new { FullName = a.Profile.FullName,
                                          Email = a.Profile.Email
                                        }                        
                       },
             //EDIT: using OrderByDescending and FirstOrDefault
             Messages = c.Messages.OrderByDescending(m => m.Date).Select(m=> new 
                       {
                          Body = m.Body,
                          SenderId = m.Sender.AccountId
                       }).FirstOrDefault())
             //others properties here
         }
         .ToListAsync();
}

edited Jul 25 '15 at 13:47

answered Jul 24 '15 at 15:18

Fabio

11,892
1
25
41

Thx Fabio so first I need to include all I need and than flatten with Select. The only problem is with LastOrDefault which is not supported LINQ to Entities does not recognize the method '<>f__AnonymousType2`2[System.String,System.Int32] LastOrDefault – Gavello Jul 25 '15 at 13:39
1

Yeah, that was my bad. You can't use LastOrDefault(). You should use FirstOrDefault inverting the OrderBy clause using OrderByDescending. Check my edited answer – Fabio Jul 25 '15 at 13:46
THX now I'm bit closer to digest LINQ – Gavello Jul 25 '15 at 13:52
1

If you want to know why you can't use LastOrDefault() take a look at this link http://stackoverflow.com/questions/7293639/linq-to-entities-does-not-recognize-the-method-last-really – Fabio Jul 25 '15 at 17:58

score 2 · Answer 2 · answered Jul 24 '15 at 15:15

You cannot project on an Include. An include is simply Eager Loading. The output does not change in the C#. Only the amount of data that is originally loaded (ie performance) changes.

It seems you want a projection and not eager loading, which are completely incompatible concepts.

However I cannot understand what exactly what you are trying to achieve.

score 1 · Answer 3 · answered Jul 24 '15 at 16:31

public async Task<List<Conversation>> FindAllByAccountIdAsync(Int32 id)
{
    return await _Db.Conversations
         .Where(c => c.Participants.Any(p => p.AccountId == id))
         .Include(c => c.Participants.Select(_=>_))
         .Include(c => c.Messages.Select(_=>_))
         .ToListAsync();
}

Should be enough.

LINQ include and Projection

3 Answers3