A Provider record has a one to many relationship with table Messages, in fact each Provider has 4 messages, only one or none has the isActive property set to true.
In a HTTP GET action method I am trying to get the Message
.Select(p => new ProviderUserTxn
{
ProviderId = (int)p.ProviderId,
UserId = p.UserId != null ? p.User.UserId.ToString() : "n/a",
Company = p.Company != null ? p.Company : "n/a",
FName = p.User.Fname,
LName = p.User.Lname,
Email = p.User.Email,
Mobile = p.User.Mobile,
Message = *p.Messages.Where(s => s.IsActive == true) ? p.Messages.Message : ""*
}).FirstOrDefaultAsync(x => x.ProviderId == id);
I don't really know how to do this, just put down something that comes close to what it might be.
In words:
get all Messages for this provider
select the Message where isActive == true
assign Message to ProviderUserTxn.Message

