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?
CodePudding user response:
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.