[RESOLVED] MVC 5 - Entiry Framework and querying multiple tables

[Ms.Longstocking]

Oh hi!

I am currently able to get information from my server from a single table like so:

My Model:

public class PersonalInfo
    {
        [Key]
        public string Id { get; set; }

        [Display(Name = "First Name")]
        public string FirstName { get; set; }

        [Display(Name = "Last Name")]
        public string LastName { get; set; }

        [Display(Name = "Country")]
        public string CountryId { get; set; }

        [Display(Name = "Region")]
        public string RegionId { get; set; }

        [Display(Name = "City")]
        public string City { get; set; }

        [Display(Name = "Address")]
        public string Address1 { get; set; }

        [Display(Name = "Address Line 2")]
        public string Address2 { get; set; }

        [Display(Name = "Postal Code")]
        public string PostalCode { get; set; }
    }

Data Context Class:

public class DataContext : DbContext
    {
        public DataContext() : base("Conn")
        {

        }

        public DbSet<PersonalInfo> PersonalInfoView { get; set; }
        public DbSet<Country> Countries { get; set; }
        public DbSet<Region> Regions { get; set; }

    }

My Controller:

// GET: PersonalInfo
        public ActionResult _PersonalInfoDisplay()
        {
            string user = User.Identity.GetUserId();

            var output = db.PersonalInfoView.Single(model => model.Id == user);

            return View(output);
        }

And finally, my view:

<div class="sectionInfo">
    <ul>
        <li>
            <strong class="lbl">@Html.DisplayNameFor(model => model.FirstName)</strong>
            <span class="txt">@Html.DisplayFor(model => model.FirstName)</span>
        </li>
        <li>
            <strong class="lbl">@Html.DisplayNameFor(model => model.LastName)</strong>
            <span class="txt">@Html.DisplayFor(model => model.LastName)</span>
        </li>
        <li>
            <strong class="lbl">@Html.DisplayNameFor(model => model.CountryId)</strong>
            <span class="txt">@Html.DisplayFor(model => model.CountryId)</span>
        </li>
        <li>
            <strong class="lbl">@Html.DisplayNameFor(model => model.RegionId)</strong>
            <span class="txt"> @Html.DisplayFor(model => model.RegionId)</span>
        </li>
        <li>
            <strong class="lbl">@Html.DisplayNameFor(model => model.City)</strong>
            <span class="txt">@Html.DisplayFor(model => model.City)</span>
        </li>
        <li>
            <strong class="lbl">@Html.DisplayNameFor(model => model.Address1)</strong>
            <span class="txt">@Html.DisplayFor(model => model.Address1)</span>
        </li>
        <li>
            <strong class="lbl">@Html.DisplayNameFor(model => model.Address2)</strong>
            <span class="txt">@Html.DisplayFor(model => model.Address2)</span>
        </li>
        <li>
            <strong class="lbl">@Html.DisplayNameFor(model => model.PostalCode)</strong>
            <span class="txt">@Html.DisplayFor(model => model.PostalCode)</span>
        </li>
    </ul>
</div>

Now, instead of simply listing CountryId and RegionId, I'd like to display actual country and region names.

My Models:

public class Country
    {
        [Key]
        public int CountryId { get; set; }
        public string CountryName { get; set; }
        public string CountryCode { get; set; }
    }

    public class Region
    {
        [Key]
        public int RegionId { get; set; }
        public string RegionName { get; set; }
        public string CountryId { get; set; }
    }

This is where I am stuck. If I were to write an SQL statement to achieve my desired goal, this would be it:

SELECT FirstName, LastName, Country.CountryName, Region.RegionName
FROM PersonalInfo
INNER JOIN Country
ON PersonalInfo.CountryId = Country.CountryId
INNER JOIN Region
ON PersonalInfo.RegionId = Region.RegionId
WHERE Id = '1234567890'

How can I achieve the same endgame using MVC?

Thanks in advance,
MzPippz

[KGComputers]

If your using Entity Framework, you need to perform multiple joins similar to the code below.

var Records = (from personInfo in db.PersonalInfo
                 join countryInfo in db.Country on personInfo.CountryId equals countryInfo.CountryId
                 join regionInfo in db.Region on regionInfo.RegionId equals personInfo.RegionId
                 where personInfo.Id == "1234567890"
                 select new {
                     FirstName = FirstName,
                     LastName = LastName,
                     CountryName = countryInfo.CountryName,
                     RegionName = regionInfo.RegionName
                 });

Note: The code above isn't working, it's just a sample logic. But that will serve as an idea on how to perform joins in EF.

Reference: Join Operators

- kgc

[Ms.Longstocking]

I managed to create an errorless query....

var output = (from PersonalInfo in db.PersonalInfoView
                          join Country in db.Countries on PersonalInfo.CountryId equals Country.CountryId
                          join Region in db.Regions on PersonalInfo.RegionId equals Region.RegionId
                          where (PersonalInfo.Id == user)
                          select new
                          {
                              FirstName = PersonalInfo.FirstName,
                              LastName = PersonalInfo.LastName,
                              CountryName = Country.CountryName,
                              RegionName = Region.RegionName
                          });

.... but how about assigning the results to my model for rendering in my view?

As accomplished in my old code:

var output = db.PersonalInfoView.Single(model => model.Id == user);

Doing this old school seems SO much easier. This way of doing things seems over-engineered. And not by a little.

[Ms.Longstocking]

Ok, I got the statement to work. Upon tracing the code, the output is correct.
However, when my page part gets rendered, I receive this error:

The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery`1[<>f__AnonymousType9`8[System.String,System.String,System.String,System.String,System.String,System.String,System.String,Sy stem.String]]', but this dictionary requires a model item of type 'MyProject.Models.PersonalInfo'.

[Ms.Longstocking]

This is the closest I've come to resolving this issue:

// GET: PersonalInfo
        public ActionResult _PersonalInfoDisplay()
        {
            string user = User.Identity.GetUserId();


            var output = (from PersonalInfo in db.PersonalInfoView.Where(model => model.Id == user)
                          join Country in db.Countries on PersonalInfo.CountryId equals Country.CountryId
                          join Region in db.Regions on PersonalInfo.RegionId equals Region.RegionId
                          select new PersonalInfo()
                          {
                              Id = PersonalInfo.Id,
                              FirstName = PersonalInfo.FirstName,
                              LastName = PersonalInfo.LastName,
                              CountryId = Country.CountryName,
                              RegionId = Region.RegionName,
                              City = PersonalInfo.City,
                              Address1 = PersonalInfo.Address1,
                              Address2 = PersonalInfo.Address2,
                              PostalCode = PersonalInfo.PostalCode
                          });

            //      NOTE: When output is set to the line below, it works fine. All I want to do is to JOIN a table!!!
            //      var output = db.PersonalInfoView.Single(model => model.Id == user);

            return View(output);
        }

The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery`1[MyProject.Models.PersonalInfo]', but this dictionary requires a model item of type 'MyProject.Models.PersonalInfo'.

[Ms.Longstocking]

I found a solution.
The trouble is, I am too green to know if it's a viable answer. On the surface, at least to me, it is not an intuitive solution.

string user = User.Identity.GetUserId();

// My results delivered as a model
var output = db.PersonalInfoView.Single(model => model.Id == user);

// My results tweaked based on the results of 2 other models (country and region)
var country = db.Countries.Single(e => e.CountryId == output.CountryId);
var region = db.Regions.Single(e => e.RegionId == output.RegionId);
var countryName = country.CountryName;
var regionName = region.RegionName;
output.CountryId = countryName;
output.RegionId = regionName;
          
return View(output);

I derive the final output using 3 separate models instead of incorporating everything into one query.
1) Personalinfo
2) Country
3) Region

Is what I'm doing kosher relative to my earlier approach?

[KGComputers]

If your solution work, that's great. It's just my preference to use LINQ query based syntax as it's more of SQL form.

If your still interested in solving that issue and purse the query based syntax approach, here's how I would do it using ViewModel pattern.
I'll create a ViewModel class that holds results from the query which will be passed to the view. And in the View, i'll add reference to my ViewModel.

View Model class

public class PersonalInfoViewModel
    {
        public string Id { get; set; }
 
        public string FirstName { get; set; }
 
        public string LastName { get; set; }
 
        public string CountryId { get; set; }
 
        public string RegionId { get; set; }
 
        public string City { get; set; }
 
        public string Address1 { get; set; }
 
        public string Address2 { get; set; }
 
        public string PostalCode { get; set; }
    }

Pass query result to ViewModel instance

var output = (from PersonalInfo in db.PersonalInfoView.Where(model => model.Id == user)
                          join Country in db.Countries on PersonalInfo.CountryId equals Country.CountryId
                          join Region in db.Regions on PersonalInfo.RegionId equals Region.RegionId
                          select new PersonalInfoViewModel()
                          {
                              Id = PersonalInfo.Id,
                              FirstName = PersonalInfo.FirstName,
                              LastName = PersonalInfo.LastName,
                              CountryId = Country.CountryName,
                              RegionId = Region.RegionName,
                              City = PersonalInfo.City,
                              Address1 = PersonalInfo.Address1,
                              Address2 = PersonalInfo.Address2,
                              PostalCode = PersonalInfo.PostalCode
                          });

In my view, add reference to ViewModel

@model MyNamespace.Models.PersonalInfoViewModel
<div class="sectionInfo">
....
</div>

Reference: The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery', but this dictionary requires a model item of type B

Doing this old school seems SO much easier. This way of doing things seems over-engineered. And not by a little.

Yep, couldn't agree more. But that's the way new things work, if you keep yourself at par with current technologies..

- kgc

[Ms.Longstocking]

Hi kgc,

Thanks for taking the time to write me back.
I implemented your code suggestion (which sported just some variable name changes) and received the same error message:

The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery`1[MyProject.Models.PersonalInfoViewModel]', but this dictionary requires a model item of type 'MyProject.Models.PersonalInfoViewModel'.

The data that I am sending to the server is not being cast properly....

Over 120 views of this thread now, and yet no one can offer a way to correct what should be a simple issue.
Entity Framework is horribly over-engineered and complicated, at least from the perspective of an junior/intermediate back-end developer.
After completing my current section, I'll be abandoning the EF approach and going back to the time-honoured notion of writing my own connection methods and SQL queries.
Decent programmers don't need to worry about database integrity if they know what they are doing and take measures to protect their data.

Using EF, tasks have taken days instead of hours. No more.

Thanks again for your time.
MzPippz

[FunkyDexter]

You can define Foreign Key Relationships in you class. See the "Create The Office Assignment" section here for an example. (This allows you to reference Parent/Lookup data which is, I think, what you're after here - he covers Child/List data earlier in same page).

Once you've established the relationships you can access the related data in a few different ways, depending on exactly how you want to use it. See here for how to do that.

Entity Framework is horribly over-engineered and complicated

Speaking as someone who came from the DB world long before anyone was even considering ORM systems I'm 50/50 on that. Entity Framework, particularly with the Code First approach, has the huge advantage of allowing me to work 100% abstracted from the actual DB. It ties my code changes to my database changes and makes roll outs and configuration management a lot easier which is a huge boon. On the other hand, like any other technology, it comes with it's own learning curve, foibles and annoyances.

I would recommend running through that whole tutorial though. It really helps get your head round the basics.

Decent programmers don't need to worry about database integrity

I'll pretend you didn't say that.

[Ms.Longstocking]

Decent programmers don't need to worry about database integrity

I'll pretend you didn't say that.

LOL!!

2005 join date? Back in the days of Mendhak and jmcilliney!!

Yeah, the learning curve, at least for me has been brutal. Thanks for your input. Seems as though I need to learn patience.

-MzPippz