question

wavemaster avatar image
0 Votes"
wavemaster asked ZhiLv-MSFT answered

Controller Action Method - resolve one too many based on "isActive"

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

dotnet-aspnet-core-webapi
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @wavemaster,

Can you share the definition of the related table (such as the Provider, Messages and the ProviderUserTxn table)?

0 Votes 0 ·

Provider and Messages are tables / Entities

ProviderUserTxn is a dto to display some information related to Provider / User / Message

 [Key] public int ProviderId { get; set; }
    
         public string Company { get; set; }
    
         public int? UserId { get; set; }
    
         public virtual ICollection<Message> Messagesets { get; set; }

public partial class Message
{
[Key] public int Mid { get; set; }
public int ProviderId { get; set; }
public string Message1 { get; set; }
public bool? IsActive { get; set; }
}



0 Votes 0 ·

1 Answer

ZhiLv-MSFT avatar image
0 Votes"
ZhiLv-MSFT answered

Hi @wavemaster,

You could refer the following sample code:

Model: The Provider and Message has one-to-many relationship, and the ProviderUserTxn model it used to display the result.

 public class Provider
 {
     [Key]
     public int ProviderId { get; set; }
     public string ProviderName { get; set; }
     public string Company { get; set; }

     public List<Message> Messages { get; set; }
 }
 public partial class Message
 {
     [Key] 
     public int Mid { get; set; }
     public int ProviderId { get; set; }
     public string Message1 { get; set; }
     public bool? IsActive { get; set; }
 }
 public class ProviderUserTxn
 {
     [Key] 
     public int ProviderId { get; set; }

     public string Company { get; set; }

     public int? UserId { get; set; }

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

Initial test data:

103015-capture.png

Controller action method:

 public IActionResult Index2()
 { 
     var result = dbContext.Providers.Select(c => new ProviderUserTxn()
     {
             ProviderId = c.ProviderId,
             Company = c.Company != null ? c.Company : "n/a",
             Messagesets = c.Messages.Where(d=>d.IsActive==true).ToList()
     }).ToList();
     return View(result);
 }

View page: use @if to check if the message is null, then display the result.

 @model IEnumerable<WebApplication1.Models.ProviderUserTxn>
 
 <table class="table">
     <thead>
         <tr>
             <th>
                 @Html.DisplayNameFor(model => model.ProviderId)
             </th>
             <th>
                 @Html.DisplayNameFor(model => model.Company)
             </th>
             <th>
                 Messages
             </th>
             <th></th>
         </tr>
     </thead>
     <tbody>
         @foreach (var item in Model)
         {
             <tr>
                 <td>
                     @Html.DisplayFor(modelItem => item.ProviderId)
                 </td>
                 <td>
                     @Html.DisplayFor(modelItem => item.Company)
                 </td>
                 <td>
                     <dl class="row">
                         @if (item.Messagesets.Count > 0)
                         {
                             @foreach (var message in item.Messagesets)
                             {
                                 <dt class="col-sm-2">
                                     <span>@message.IsActive -- @message.Message1 </span>
                                 </dt>
                             }
                         }
                         else
                         {
                             <dt class="col-sm-2">
                                 <span>null</span>
                             </dt>
                         }

                     </dl>
                 </td>
                 <td>
                     @Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
                     @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
                     @Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
                 </td>
             </tr>
         }
     </tbody>
 </table>


The result as below:

102993-capture1.png

If there has any question about the above sample, please let me know freely.


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion


capture.png (28.8 KiB)
capture1.png (13.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.