Joining asp_Users db tables in LINQ using asp.net MVC

I am working on an example asp.net project using MVC, but my database is a live one which I can't make changes to (technically it's the test version of this database but my point is changes to the database aren't possible).

I use the UserID from the asp_Users table to store who makes changes to various aspects of the system, and I want to start showing the user name in various front-end tables, but how do I link the tables to get this user name?

So to clarify, I'm going to want to do this for several tables throughout the system so I was hoping I could do it using LINQ.

I can get the info I want from using the join query, but how do I pass this to my View to use?

        var plans = from users in db.aspnet_Users
                           join import in db.Plan_Imports
                           on users.UserId.ToString()
                           equals import.User_ID

                           select new
                           {
                               Date = import.Date,
                               UserName = users.UserName
                           };

Sample tables

asp_Users

UserID

UserName

...

table1

ID

field1

field2

...

User_ID <--- ref to asp_Users

table2

ID

field1

field2

...

User_ID <--- ref to asp_Users

Answers


I would create a ViewModel for each view.

ViewModel is just a POCO class

public class PlanViewModel
{
  public string UserName { set;get;}
  public DateTime ImportDate { set;get;}
}

Then Get the Data to this ViewModel/Collection of ViewModel using LINQ Projections from your query.

public ActionResutl Show()
{
   var plans = (from users in db.aspnet_Users
                           join import in db.Plan_Imports
                           on users.UserId.ToString()
                           equals import.User_ID

                           select new PlanViewModel
                           {
                               ImportDate = import.Date,
                               UserName = users.UserName
                           }).ToList();
   return View(plans);    
}

Now Lets make our view strongly typed to a collection of our PlanViewModel

@model List<PlanViewModel>
@foreach(var plan in Model)
{
  <p>@plan.UserName</p>
  <p>@plan.ImportDate.ToString()</p>
}

The solution provided by Shyju worked perfectly, and it wasn't too complex to do, however I decided that I didn't think using LINQ was appropriate in this case as the code was getting out of hand for what should be a simple call.

What I did instead was use a stored procedure to get the information, and saved it to a complex object which I passed to my view.

The code is now much neater and easier to manage, as the code above became just

var plans = db.SP_SelectImports();  

Read more about stored procedure mapping here: http://dotnet.dzone.com/news/mapping-stored-procedure


Need Your Help

Canvas and color swapping

javascript canvas

I'm swapping 2 circles color but when I swap them based on a timer the strokes do not swap even though I don't even have a stroke applied to them:

how to Move world view instead of camera view

delphi delphi-xe2 firemonkey perspectivecamera

I have a viewport3d with a camera and some blocks inside of it. Currently using the keyboard to move the camera up/down/left/right/rotate ect.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.