我嘗試使用以下 SQL 代碼獲取在資料透視表中沒有關系的客戶串列：
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);
這在資料庫中效果很好，我得到了我想要的結果。
但是，一旦我嘗試在我的控制器中實作它并將其傳遞給我的視圖，當我訪問該頁面時就會收到以下錯誤：
這是我的代碼：
索引控制器
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);
}
看法
@model List<customer>
<h1>list with customers</h1>
@*create a new customer*@
<a asp-controller ="Customer" asp-action="Create">create new customer</a>
<table class="table table-striped">
<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>
uj5u.com熱心網友回復：
發生此問題是因為在不同的表中有同名的列。
您應該指定要回傳的表的列，而不是使用通配符*來回傳所有列。
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();
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/444889.html
標籤：C＃ sql asp.net 核心 实体框架核心 asp.net-core-mvc