Cannot return proper model into view when doing a join linq query
I have this code in my controller:
public ActionResult Index()
{
MembershipUser currentUser = Membership.GetUser();
Guid UserId = (Guid)currentUser.ProviderUserKey;
using (var db = new MatchGamingEntities())
{
var MyAccount = from m in db.Accounts
join n in db.BankTransactions on m.AccountId equals n.AccountId
where m.UserId == UserId
select new BankStatement{Balance = (decimal)m.Balance, MyTransactions = m.aspnet_BankTransactions.ToList()};
return View(MyAccount.Single());
}
}
Here is my View:
model MatchGaming.Models.BankStatement
@{
ViewBag.Title = "Index";
}
<h2>Bank Statement</h2>
<a href="/Cashier/Withdrawal">Withdrawal</a> | <a href="/Cashier/Deposit">Deposit</a><br /><br />
<fieldset>
<legend>BankStatement</legend>
<p>
Balance: @Model.Balance
</p>
</fieldset>
<table width="100%">
<tr>
<td>Created</td>
<td>Amount</td>
<td>Transaction Type</td>
</tr>
@foreach (var item in Model.MyTransactions)
{
<tr>
<td>@item.Created</td>
<td>@item.Amount</td>
<td>@item.TransactionType</td>
</tr>
}
</table>
This is my BankStatement Model:
public class BankStatement
{
public decimal Balance {get;set;}
public List<BankTransaction> MyTransactions { get; set; }
}
I want to be able to do a join query between my two tables Accounts and BankTransactions. This is a one to many relationship, there can be multiple BankTransactions per Account. I want to query this and display the account information including all the bank statements that are associated with it. I did a join in order to get it, but I am having trouble handling the model. I keep getting this error:
开发者_高级运维LINQ to Entities does not recognize the method 'System.Collections.Generic.List1[MatchGaming.Models.BankTransaction] ToList[BankTransaction](System.Collections.Generic.IEnumerable
1[MatchGaming.Models.BankTransaction])' method, and this method cannot be translated into a store expression.
I will assume that you are using Entity Framework and that there is a relationship between Account and BankTransaction in your model.
You could try this:
public ActionResult Index()
{
MembershipUser currentUser = Membership.GetUser();
Guid UserId = (Guid)currentUser.ProviderUserKey;
using (var db = new MatchGamingEntities())
{
var myAccount = (from m in db.Account.Include("aspnet_BankTransactions")
where m.UserId = UserId
select new BankStatement{Balance = (decimal)m.Balance, MyTransactions = m.aspnet_BankTransactions).Single();
return View(myAccount);
}
}
It's not clear to me why your query uses db.BankTransactions
in one place, but then m.aspnet_BankTransactions
in another.
I suspect you want a group join - something like this:
var account = from m in db.Accounts
where m.UserId == UserId
join n in db.BankTransactions
on m.AccountId equals n.AccountId
into transactions
select new BankStatement {
Balance = (decimal) m.Balance,
MyTransactions = transactions.ToList()
};
However, if your model is set up with the appropriate associations to start with, you don't need to do the join yourself. Just use:
var account = db.Accounts.Single(account => account.UserId == UserId);
return new BankStatement {
Balance = (decimal) account.Balance,
MyTransactions = account.Transactions.ToList() };
精彩评论