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*. — freedomn-m, Sep 28 '15 at 11:15
Forget the view - it's falling over before it gets to the view. — freedomn-m, Sep 28 '15 at 11:15

score 2 · Accepted Answer · edited May 23 '17 at 12:24

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 '17 at 12:24

Community

1
1

answered Sep 28 '15 at 11:10

CodeCaster

147,647
23
218
272

This has the following error in intellisense Cannot convert lambda expression to type 'string' becuase it is not a delegate type' – Phill Sanders Sep 28 '15 at 11:28
1

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. – CodeCaster Sep 28 '15 at 11:29
Thanks, however PostName is blank in the list view – Phill Sanders Sep 28 '15 at 11:34
That's a different issue. Are you sure this owner has a PostID set? – CodeCaster Sep 28 '15 at 11:35
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 – Phill Sanders Sep 28 '15 at 11:42
Looking at this Ive made a complete hash of the question and failed to mention the Post class is within another db context, so the answers Ive implemented have added anothe rpost table in the Owners db. I'll amend the question and see if I can get it right this time – Phill Sanders Sep 28 '15 at 11:54

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

1 Answers1