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.IEnumerable1[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() };