Asp .net mvc 4 specific "select ...." command

Question

I am trying to make an ebank, for myself, so it is not that serious. This is my structure:

What I achieved so far: if a user logs in, and click on the Ebank actionlink, for him/her I display ALL of the data (I mean if logged in userId==1 I display all of the data with userId==2 too, this is what I want to change).

public ActionResult Index()
{

     var bankaccounts = db.BankAccounts.Include(b => b.Type).Include(b => b.Balance).Include(b => b.UserProfile);
     return View(bankaccounts.ToList());

}

This "var bankaccounts" row is what I have to change if I am right. If I put a breakpoint to the "return view(bankaccounts.ToList())" row I get the following data from the "bankaccounts":

    {
SELECT 
    [Extent1].[BankAccountId] AS [BankAccountId], 
    [Extent1].[UserId] AS [UserId], 
    [Extent1].[AccountName] AS [AccountName], 
    [Extent1].[AccountType] AS [AccountType], 
    [Extent1].[AccountNumber] AS [AccountNumber], 
    [Extent1].[AccountStatus] AS [AccountStatus], 
    [Extent1].[AccountAvaiability] AS [AccountAvaiability], 
    [Extent2].[AccountType] AS [AccountType1], 
    [Extent2].[TypeName] AS [TypeName], 
    [Extent3].[BalanceId] AS [BalanceId], 
    [Extent3].[BalanceAmount] AS [BalanceAmount], 
    [Extent3].[TrustAmount] AS [TrustAmount], 
    [Extent4].[UserId] AS [UserId1], 
    [Extent4].[UserName] AS [UserName]
    FROM    [dbo].[BankAccount] AS [Extent1]
    INNER JOIN [dbo].[Type] AS [Extent2] ON [Extent1].[AccountType] = [Extent2].[AccountType]
    LEFT OUTER JOIN [dbo].[Balance] AS [Extent3] ON [Extent1].[BankAccountId] = [Extent3].[BalanceId]
    INNER JOIN [dbo].[UserProfile] AS [Extent4] ON [Extent1].[UserId] = [Extent4].[UserId]}

Questions:

I cannot find any real sql command in the code(its auto generated by mvc), so If I am right

does this "var bankaccounts" row do the sql select?
If yes, how?
Where can I read after this type of programming?
I want to change this code to something like "select ... where userid == websecurity. CurrentUserId", can you help me with this please?
Any more ideas, advices that I should take or read after? The more you say the more I can learn!

Welcome to LINQ world, you can run sql scripts using DBQuery. checkout this -> msdn.microsoft.com/en-us/data/jj592907.aspx — Idrees Khan
– Idrees Khan, Commented Feb 14, 2014 at 11:46
You should Google the term "Object Relational Mapper". This answers all of your questions. — Simon Whitehead
– Simon Whitehead, Commented Feb 14, 2014 at 11:46

StuartLC · Accepted Answer · 2014-02-14 12:00:49Z

1

You are using a provider like Entity Framework which is providing the mapping from code to your database. Specifically:

In your case, the Sql gets executed when you materialize the query with ToList(). Until the materialization step, var bankaccounts is an IQueryable.
Entity Framework has a Query provider which knows how to parse the Expressions you give it to Sql statements, and similarly, to map result sets back to entities like BankAccount.
Sites like MSDN will give you an overview
Add a Where expression predicate:

var userBankaccounts = db.BankAccounts
                         .Where(ba => ba.UserId == websecurity.CurrentUserId);

The Sql you see is 'real' (sans the curly {} brackets) - once you look past the rather annoying aliasing of tables and derived tables as [Extentxxx], it will allow you to quickly check that your Linq expressions are performing the queries required.

edited Feb 14, 2014 at 12:00

answered Feb 14, 2014 at 11:50

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Asp .net mvc 4 specific "select ...." command

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related