LINQ outer join on association tables?

Question

This is a LINQ Code First problem:

Two classes/tables (message and image) with a many-to-many relationship, one image can be used in multiple messages and one message can contain multiple images:

public class Message
{
  public int MessageId { get; set; }
  public string Message { get; set; }
  public ICollection<Image> Images { get; set; }
}

public class Image
{
  public int ImageId {get;set;}

  [Required, StringLength(2048)]
  public string Path {get;set;}

  [Required, StringLength(255)]
  public string ContentType { get; set;}

  public virtual ICollection<Message> Courses { get; set; }
}

This creates three tables: Message, Image, MessageImage, the goal is to get all the images for a given message or is NOT be used in a message. This is the SQL code that gives me the desired result:

SELECT i.imageId, i.Path, i.ContentType, im.essageId
  FROM Image i
  LEFT join MessageImage mi on mi.ImagesId = i.Id
 WHERE im.essageId = 14 or im.ImagesId is null 

The problem I am having is that within the C# code there is no object for MessageImage table, how does one create a Linq statement (Query Syntax base) to return the results?

Answer 1

Try this query (used slightly different naming in entites):

var listAsync = await ctx.Images
    .Where(i => i.Messages.Any(m => m.MessageId == message.MessageId) || !i.Messages.Any())
    .ToListAsync();

Or define the junction table explicitly (see the Indirect many-to-many relationships part of the docs):

public class Message
{
  // ...
  public ICollection<MessageImage> MessageImages { get; set; }
}

public class Image
{
  // ...
  public virtual ICollection<MessageImage> MessageImages { get; set; }
}

public class MessageImage
{
  public Image Image { get; set; }
  public Message Message { get; set; }
}

And use it in queries.