An item with the same key has already been added. Key: Id-CodePudding

I tried to get a list of customers that have no relationship in the pivot table with the following SQL code:

select *  from [Identity].[Customer] as c 
left outer join  [Identity].[UserCustomer] as uc  on c.id = uc.customerId 
left outer join  [Identity].[User] as u  on u.id = uc.userId where 
not exists (select userId,customerId 
from [Identity].[UserCustomer] uc 
where uc.userId = '0a65d716-7a15-4aa1-82f9-fce4418fbf1b' 
and uc.customerId = c.id);

This works great in the database, I get the result I want.

But as soon as I try to implement this in my controller and pass it to my view, I get the following error when I visit the page:

Here is my code:

Index controller

public ActionResult Index()
{
    var user = _usermanager.GetUserId(HttpContext.User);   
    var customer = _db.Customer.FromSqlRaw("select * from [Identity].[Customer] as c left outer join  [Identity].[UserCustomer] as uc on c.id = uc.customerId left outer join [Identity].[User] as u  on u.id = uc.userId where not exists (select userId,customerId from [Identity].[UserCustomer] uc where uc.userId = {0} AND uc.customerId = c.id)",user).ToList();
    return View(customer);
}

View

@model List<customer>
<h1>list with customers</h1>
@*create a new customer*@
<a asp-controller ="Customer" asp-action="Create">create new customer</a>

<table >
  <thead>
    <tr>
      <th scope="col">#</th>
      <th scope="col">firstname</th>
      <th scope="col">lastname</th>
      <th scope="col">adress</th>
      <th scope="col">phonenumber</th>
      <th scope="col">edit</th>
      <th scope="col">claim this user</th>
    </tr>
  </thead>
  <tbody>
  @foreach(var customers in @Model)
  {
    <tr>
      <td>@customers.Id</td>
      <td>@customers.firstname</td>
      <td>@customers.lastname</td>
      <td>@customers.adress</td>
      <td>@customers.phonenumber</td>
      <td><a asp-controller= "customer" asp-action="edit" asp-route-id = "@customers.Id">edit</a></td>
      <td><a asp-controller= "customer" asp-action="claimCustomer" asp-route-id = "@customers.Id">claim</a></td>
    </tr>
  }
  </tbody>
</table>

CodePudding user response：

The issue happened because there are columns with the same name in different tables.

You should specify the table's column(s) to be returned instead of using wildcard * to return all columns.

string query = @"
    select c.Id, c.FirstName, c.LastName  --And other columns to be returned
    from [Identity].[Customer] as c 
    left outer join  [Identity].[UserCustomer] as uc  on c.id = uc.customerId 
    left outer join  [Identity].[User] as u  on u.id = uc.userId where 
    not exists (select userId,customerId 
    from [Identity].[UserCustomer] uc 
    where uc.userId = {0} 
    and uc.customerId = c.id);
";

var customer = _db.Customer.FromSqlRaw(query, user).ToList();