Linq to SQL MVC issue with FK

Question

right having a bit of a hard mental blog with some Linq to SQL and MVC.

Getting the basic MVC and L2SQL going ok.

1. FK relationships. -> when using MVC and we have a fk relationship in the model, and the view is based on a strongly typed object - how do you get the data from the related table?

So for example

User (Table)
UserId
UserName

---

Call (Table)
    CallId
    UserId
    CountryId

---

Country(Table)
CountryID
CountryName

SO I want to get only the calls for a user with a specific country?

The view - based on Call Object as this is the "Details" view -

how do i get a UserName and CountryName and still maintain a view based on Call?

It would seem that I still have to create an Object CallForUserByCountry - but this gets messy on save as the CallForUserByCountry object also needs to implement how to create Call User and Country.

the linq query

var calls = from c in db.Call
    where c.CountryID == id

        select new CallForUserByCountry or new something// this is bit that suggests a new object.

Hopefully I missing something ...

Answer 1

You could use the TempData bag and save the Username and CountryName there.

in the controller: TempData["CountryName"] = countryName;

Answer 2

If I'm understanding this correctly you are trying to get calls from a specific user with a specific country. If that's the case, then as long as you have the relationships mapped in the Linq2SQL designer, then you should be able to access the user and country from the call as properties. So it would be something like:

var userName = call.User.UserName;

As I said, this is contingent on the relationships being mapped in the designer, otherwise Linq2SQL will have no indication that the two tables are related.

You could also have a separate query to just get the calls you need based on User and Country:

var calls = from c in db.Call
            where c.User.UserID = userID
            && c.Country.CountryID == countryID
            select c;

Answer 3

If the Call data class has associations to User and Call data classes you can access Call's User and Country properties directly in your View. Also you can load data from related User and Country tables immediately (instead of lazy loading by default):

In Controller:

DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Call>(x => x.User);
options.LoadWith<Call>(x => x.Country);
db.LoadOptions = options;

var calls = from c in db.Call
    where c.Country.CountryName == countryName && c.User.UserName == userName
        select c;

In View:

<%= Model.User.UserName %>
<%= Model.Country.CountryName %>

Answer 4

Your question can be taken two ways.

1. You want to display a list of calls using the current Call Details View. - This would need a new view. Unless the details view is a ViewUserControl. Then you could use PartialRender.

2. You want to add extra data to the current Details View - Add the extra data to the ViewData.

For number 2 you could do the following.

i prefer using joins for this, and I like working with objects detached from my DataContext. So normally i don't have the extra IQueryable in my objects.

I would still use the CallForUserByCountry object. Makes things verbose.

var calls = from call in db.Calls
            join country in db.Countries on call.CountryId equals country.CountryID
            join user in db.Users on call.UserId equals user.UserId
            where user.UserName == userName && country.CountryName == countryName
            select new CallForUserByCountry
            {
               UserName = user.UserName,
               CountryName = country.CountryName,
               Call = call
            };

View["CallsForUserByCountry"] = calls.ToList();

In the view.

<% foreach(var callForUserByCountry in (IList<CallForUserByCountry>)ViewData["CallsForUserByCountry"]) { %>
  .. Do stuff here .. I like using PartialRendering 
  <% Html.PartialRender("CallForUserByCountryDetail", callForUserByCountry); %>

<%}