Wsrtjtyk

I am somewhat new to C# and MVC Razor pages. I want to show a company profile page to the logged in user which is a member of that Company. I have 3 tables for this view UsersToAddresses, CustomerNames, and Addresses. The usersToAddresses gets the UserId automatically inserted by a trigger in SQL when a user registers. This table only has 2 columns UserId and AddressId. I did this because more than one user can be a member of a company and I did not want to store duplicate data. In SQL UsersToAddresses.AddressId is linked to Addresses.AddressId by foreign key. CustomerNames.CustomerId is linked to Addresses.CustomerId by foreign Key. I want to be able to show on the user Dashboard the Company Name with a list of addresses for that company. They may only have one address. I am assuming that creating a ViewModel is the best way to do this, I have a viewModel created for the page. I also have the userId as an extension of the link. So it would be Http:localhost:3125/customers/fdsa531as654few44231431fdvafbfzs
[EDIT] After much discussion with @Marcelo Myara I have finally got this to work they way I want it to. Below I will show the changes marked with [EDIT]. Below code is fully functional and works as expected.

Controller:

        [Authorize(Roles = "CompanyAdmin")]

    public ActionResult Index(string UserId)

    {

        if (UserId == null)

        {

            return new HttpStatusCodeResult(HttpStatusCode.BadRequest);

        }

   [EDIT] - "Changed to use modelInstance"

        //Trying to get a view model for customer from the received UserId.

        CompanyOverview modelInstance = CompanyOverview.GetCompanyByUser(UserId, db);

        if (modelInstance == null)

        {

            return HttpNotFound();

        }

        return View(modelInstance);



        // Addresses userAddress = db.Addresses.Find(UserId);

        // if (userAddress == null)

        // {

        //    return HttpNotFound();

        // }

        // return View(userAddress);

    }

Basically a copy of the Details in the controller, but this uses a db.Addresses and I am not using that in the view. I am using a ViewModel.

Here is my view Model:

    public partial class CompanyOverview

{

    public CompanyOverview()

    [EDIT] - "Not used"

    //{

    //    AddressDetail = new List<Addresses>();

    //}

    [EDIT] - "Not Needed"

    // [Key]



    [Edit] - "Added proper joins for use of referance table"

            //Static method to get an instance of your model when given an userId and DbContext instance.

    public static CompanyOverview GetCompanyByUser(string userId, CustomerEntities db)

    {

        var qCus = from ad in db.Addresses

                   join ua in db.UserToAddresses on ad.AddressId equals ua.AddressId

                   join cus in db.CustomerNames on ad.CustomerId equals cus.CustomerId

                   where (ua.UserId == userId)

                   select new CompanyOverview()

                   {

                       UserId = userId,

                       AddressId = ad.AddressId,

                       Customer = cus.CustomerName,

                       CustomerName = cus,

                       Location = ad.LocationName,

                       Addresses = ad

                   };





        var result = qCus.SingleOrDefault();



        if (result != null)

        {

            result.AddressDetail = db.Addresses.Where(a => a.CustomerId == result.CustomerName.CustomerId);

        };

        return result;

    }



    public string UserId { get; set; }

    public string AddressId { get; set; }

    [EDIT] - "Added for use on page if needed - Customer allows me to show the company name at the top of the razor page"

    public string Customer { get; set; }

    public string Location { get; set; }

    public virtual CustomerNames CustomerName { get; set; }

    public virtual ICollection<Addresses> AddressDetail { get; set; }

}

This might not be correct either. So as a result I have to find the UserId in the UsersToAddresses table and have it pull the addresses from the addresses table that match the UserId. If someone can help with this it would be appreciated. Thank You.

Updated Nov 8th 2018 - added table Definitions.

UsersToAddresses:

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



CREATE TABLE [dbo].[UsersToAddresses](

[UserId] [nvarchar](128) NOT NULL,

[AddressId] [int] NOT NULL,

CONSTRAINT [PK_UsersToAddresses] PRIMARY KEY CLUSTERED 

(

[UserId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON

[PRIMARY]

) ON [PRIMARY]

GO



ALTER TABLE [dbo].[UsersToAddresses]  WITH CHECK ADD  CONSTRAINT

[FK_dbo.UsersToAddresses_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])

REFERENCES [dbo].[AspNetUsers] ([Id])

ON DELETE CASCADE

GO



ALTER TABLE [dbo].[UsersToAddresses] CHECK CONSTRAINT 

[FK_dbo.UsersToAddresses_dbo.AspNetUsers_UserId]

GO

Customers Table:

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



CREATE TABLE [dbo].[CustomerNames](

[CustomerId] [int] IDENTITY(1,1) NOT NULL,

[CustomerName] [nvarchar](max) NOT NULL,

[Status] [int] NOT NULL,

[Terms] [nvarchar](50) NULL,

[TaxCode] [nvarchar](50) NULL,

CONSTRAINT [PK_dbo.CustomerNames] PRIMARY KEY CLUSTERED 

(

[CustomerId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



ALTER TABLE [dbo].[CustomerNames] ADD  CONSTRAINT

[DF_CustomerNames_Status]

DEFAULT ((1)) FOR [Status]

GO

Addresses Table:

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



CREATE TABLE [dbo].[Addresses](

[AddressId] [int] IDENTITY(1,1) NOT NULL,

[Status] [bit] NULL,

[IsBilling] [bit] NULL,

[IsShipping] [bit] NULL,

[ContactName] [nvarchar](max) NULL,

[Line1] [nvarchar](max) NULL,

[Line2] [nvarchar](max) NULL,

[Country] [nvarchar](100) NULL,

[State] [nvarchar](100) NULL,

[City] [nvarchar](100) NULL,

[ZipCode] [nvarchar](50) NULL,

[EmailAddress] [nvarchar](100) NULL,

[PhoneNumber] [nvarchar](50) NULL,

[FaxNumber] [nvarchar](50) NULL,

[LastUpdated] [datetime] NOT NULL,

[CustomerId] [int] NULL,

 CONSTRAINT [PK_dbo.Addresses] PRIMARY KEY CLUSTERED 

 (

[AddressId] ASC

 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =

 OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 GO



ALTER TABLE [dbo].[Addresses] ADD  CONSTRAINT [DF_Addresses_LastUpdated]

DEFAULT (getdate()) FOR [LastUpdated]

GO



ALTER TABLE [dbo].[Addresses]  WITH CHECK ADD  CONSTRAINT

[FK_Addresses_CustomerNames] FOREIGN KEY([CustomerId])

REFERENCES [dbo].[CustomerNames] ([CustomerId])

GO



ALTER TABLE [dbo].[Addresses] CHECK CONSTRAINT

[FK_Addresses_CustomerNames]

GO

Update Nov 10 2018 - Added RazorView

RazorView:

@model BestenEquipment.Models.CompanyOverview



@{

ViewBag.Title = "CreateProfile";

Layout = "~/Views/Shared/CustomerDashboardLayout.cshtml";

}





<div id="page-wrapper">

<div class="row">

    <div class="col-lg-12">

        <h2>CompanyOverview</h2>

        <h4>@Html.DisplayFor(model => model.Customer)</h4>

    </div>

    <!-- /.col-lg-12 -->

</div>

<div class="row">

    <div class="col-lg-12">

        Company Addresses:

    [EDIT] - "Added Table for headers"

                    <table class="table">

            <tr>

                <th>

                    @Html.DisplayNameFor(model => model.Addresses.Status)

                </th>

                <th>

                    @Html.DisplayNameFor(model => model.Addresses.LocationName)

                </th>

                <th>

                    @Html.DisplayNameFor(model => model.Addresses.ContactName)

                </th>

                <th>

                    @Html.DisplayNameFor(model => model.Addresses.EmailAddress)

                </th>

                <th>

                    @Html.DisplayNameFor(model => model.Addresses.Line1)

                </th>

                <th>

                    @Html.DisplayNameFor(model => model.Addresses.Line2)

                </th>

                <th>

                    @Html.DisplayNameFor(model => model.Addresses.IsBilling)

                </th>

                <th>

                    @Html.DisplayNameFor(model => model.Addresses.IsShipping)

                </th>

            </tr>

    [EDIT] - "Corrected the foreach statement"

          //  @foreach (var item in Model.AddressDetail)

          //  {

          //  <dd> @Html.DisplayFor(model => model.Location)</dd>

          //  }

              @foreach (var item in Model.AddressDetail)

                {

            <tr>

                <td>

                    @Html.DisplayFor(modelItem => item.Status)

                </td>

                <td>

                    @Html.DisplayFor(modelItem => item.LocationName)

                </td>

                <td>

                    @Html.DisplayFor(modelItem => item.ContactName)

                </td>

                <td>

                    @Html.DisplayFor(modelItem => item.EmailAddress)

                </td>

                <td>

                    @Html.DisplayFor(modelItem => item.Line1)

                </td>

                <td>

                    @Html.DisplayFor(modelItem => item.Line2)

                </td>

                <td>

                    @Html.DisplayFor(modelItem => item.IsBilling)

                </td>

                <td>

                    @Html.DisplayFor(modelItem => item.IsShipping)

                </td>

                <td>

                    @Html.ActionLink("Edit", "UserEdit", "Addresses", new { id = item.AddressId }, null) |

                    @Html.ActionLink("Delete", "Delete", "Addresses", new { id = item.AddressId }, null)

                </td>

            </tr>

            }

            </table>

    </div>

    <!-- /.col-lg-12 -->

</div>

</div>

So there you have it. I will be adding encryption to the AddressId so that the user cannot change the number and view other customers records.

Ok, I'll try to address directly your question (although there's a lot of things that could be learned and improved here). So:

[Authorize(Roles = "CompanyAdmin")]

public ActionResult Index(string UserId)

{

    if (UserId == null)

    {

        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);

    }



    //Trying to get a view model for customer from the received UserId.

    CompanyOverview modelInstance = CompanyOverview.GetCompanyByUser(UserId, db);

    if (modelInstance == null)

    {

        return HttpNotFound();

    } 

    return View(modelInstance);

}



//From your question I'm taking this class as a ViewModel only

public partial class CompanyOverview

{

    //Keeping it here just because you implemented it (if not used elsewhere, just remove this)

    public CompanyOverview()

    {

        AddressDetail = new List<Addresses>();

    }



    //Static method to get an instance of your model when given an userId and DbContext instance. Correct the DbContext type name in the below parameter.

    public static CompanyOverview GetCompanyByUser(int userId, YourDbContext db)

    {

        var qCus = from ad in db.Addresses

                      join ua in db.UsersToAddresses on ad.AddressID equals ua.AddressId

                      join cus in db.CustomerNames on ua.CustomerId equals cus.CustomerId

                      where (ua.UserId == userId)

                      select new CompanyOverview() {

                          UserId = userId,

                          AddressId = ad.AddressId,

                          CustomerName = cus

                      };



        var result = qCus.SingleOrDefault();



        if (result != null) 

        {

            result.AddressDetail = db.Addresses.Where(a => a.CustomerId == result.CustomerName.CustomerId)

        }

        return result;

    }



    //[Key] <- You don't need to define a Key annotation, since this will only be a ViewModel (thus, not having persistence by EF).

    public string UserId { get; set; }

    public string AddressId { get; set; }

    public virtual CustomerNames CustomerName { get; set; }

    public virtual ICollection<Addresses> AddressDetail { get; set; }

}

Check if that help you in any way... (I'm still not sure if I got you right and as I said before, there are lots of things to improve here - but I'm trying to address your specific question and using your type of solution only, to make sure I'll not confuse you).