Reference one model from another model in another database asp.net mvc

Question

I have the following method in my controller:

    public ActionResult OwnerList()
    {                              
        var owners = (from s in db.Owners                       
                     orderby s.Post.PostName
                     select s).ToList();

        var viewModel = owners.Select(t => new OwnerListViewModel
        {
             Created = t.Created,
             PostName = t.Post.PostName,
             Dormant = t.Dormant,
             OwnerId = t.OwnerId,
        });
        return PartialView("_OwnerList", viewModel);
    }

When running this I get the following error from the owners variable:

{"Invalid object name 'dbo.Post'."}

My models are these

In the Owners database

        public class Owner
{
    public int OwnerId { get; set; }

    [Column(TypeName = "int")]
    public int PostId { get; set; }

    [Column(TypeName = "bit")]
    public bool Dormant { get; set; }

    [Column(TypeName = "datetime2")]
    public DateTime Created { get; set; }

    public virtual ICollection<Asset> Assets { get; set; }

    public virtual Post Post { get; set; }
}

In the People database

public class Post
{
    public int PostId { get; set; }

    [StringLength(50)]
    [Column(TypeName = "nvarchar")]
    public string PostName { get; set; }

    [Column(TypeName = "bit")]
    public bool Dormant { get; set; }

    [StringLength(350)]
    [Column(TypeName = "nvarchar")]
    public string Description { get; set; }

    public virtual ICollection<Contract> Contracts { get; set; }

    public virtual ICollection<Owner> Owners { get; set; }
}

The view is:

@model IEnumerable<IAR.ViewModels.OwnerListViewModel>



<table class="table">

@foreach (var group in Model
            .OrderBy(x => x.Dormant)
            .GroupBy(x => x.Dormant))
{
    <tr class="group-header">
        @if (group.Key == true)
        {
            <th colspan="12"><h3>Dormant:- @group.Count()</h3></th>
        }
        else
        {
            <th colspan="12"><h3>Active:- @group.Count()</h3></th>
        }

    </tr>
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.PostName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Created)
        </th>
        <th></th>
    </tr>

    foreach (var item in group
                )
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.PostName) @Html.HiddenFor(modelItem => item.OwnerId)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Created)
            </td>
            <td>
                @if (group.Key == true)
                {
                    @Html.ActionLink("Make Active", "Dormant", new { ownerId = item.OwnerId })
                }
                else
                {
                    @Html.ActionLink("Make Dormant", "Dormant", new { ownerId = item.OwnerId })
                }
            </td>
        </tr>
    }
}

</table>

I'm sure this is something simple but what am I not doing correctly to allow it to reference Post from Owner?

Exactly how do you populate db.Owners ? Your references ("allow it to reference Post from Owner") are correct, but Post is not populated. — fdomn-m
– fdomn-m, Commented Sep 28, 2015 at 11:15
Forget the view - it's falling over before it gets to the view. — fdomn-m
– fdomn-m, Commented Sep 28, 2015 at 11:15

Community · Accepted Answer · 2017-05-23 12:24:37Z

2

Given s.Post is null, you have Entity Framework's lazy loading disabled. Either enable lazy loading, or explicitly load the navigation property:

from s in db.Owners.Include(o => o.Post)
orderby s.Post.PostName
...

As for your edit, given Owner.Post is in a different database, that's an entirely different question. Search the web for that, see for example Cross database querying in EF and Joining tables from two databases using entity framework for some hints. Either link them at the database level (using linked server or synonym and views) , or fix them up in your code (loop over the Owners and look up their Posts).

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Sep 28, 2015 at 11:10

CodeCaster

153k24 gold badges237 silver badges287 bronze badges

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

6 Comments

Phill Sanders Over a year ago

This has the following error in intellisense Cannot convert lambda expression to type 'string' becuase it is not a delegate type'

CodeCaster Over a year ago

And if you researched that error you'd find out that you need a using System.Data.Entity; directive on top of your file to import that extension method.

Phill Sanders Over a year ago

Thanks, however PostName is blank in the list view

CodeCaster Over a year ago

That's a different issue. Are you sure this owner has a PostID set?

Phill Sanders Over a year ago

Having looked at the Owner Table the save method is adding an autogenerated PostId number not the actual PostId that was selected, though i cant work out why

|

Collectives™ on Stack Overflow

Reference one model from another model in another database asp.net mvc

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related