Lookup data from second database from key in first database asp.net mvc

Question

I have the following error when running my get method for a list in my controller:

The specified LINQ expression contains references to queries that are associated with different contexts.

Having debugged the controller it is the orderby statement that produces the error

The Method is:

    public ActionResult OwnerList()
    {                              
        var owners = (from s in db.Owners                       
                     orderby Peopledb.Posts.FirstOrDefault(x => x.PostId == s.PostId).PostName
                     select s).ToList();

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

The Class for Owner in the first database is, dbcontext = IARContext:

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 People.Models.Post Post { get; set; }
}

The Class for Post in the second database is, dbcontext = PeopleContext:

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; }
}

I am trying to look up PostName from Post in the People db when displaying a list of Owners from the IAR db

Answer 1

for me the message is quite clear: you are mixing two contexts. That is not possible. This would nearly mean creating an hadhoc dblinq.

The solutions are :

• a global context
• a separation by code (if the context can't be globalized):

for me you should have

public ActionResult OwnerList()
{                              
    var owners = (from s in db.Owners                                   
                 //can't order from here without a dbling/global context
                 select s);
    //may be a where is missing here ?

    List<DAOSomeName> viewModel = owners.Select(t => new DAOSomeName
    {
         Created = t.Created,             
         Dormant = t.Dormant,
         OwnerId = t.OwnerId,
    });// .ToList(); the materialization is done by the following foreach

    //until here, no run to the db, no data transfered.

    foreach (DAOSomeName m in viewModel ) {
        m.PostName = Peopledb.Posts.Where(x => x.PostId == t.PostId).
            Select(x => x.PostName).FirstOrDefault();
        //this way you also handle the null case pointed by Trevor
    }

    //please note that this way, yout view model is not anymore linked
    //to the context, except if one property is a navigation property

    return PartialView("_OwnerList", viewModel.OrderBy(x => x.PostName));
}

public class DAOSomeName {
    public DateTime Created {get; set;}
    //Dormant, OwnerId, PostName...
}  

Answer 2

By amending my controller to this:

    public ActionResult OwnerList()
    {
        var posts = new List<People.Models.Post>(Peopledb.Posts);
        var owners = new List<Owner>(db.Owners);
        var ownerposts = (from c in posts
                         join d in owners on c.PostId equals d.PostId
                         orderby c.PostName
                         select new OwnerPost { OwnerId = d.OwnerId, PostName = c.PostName, Created = d.Created, Dormant = d.Dormant }).ToList();

        var viewModel = ownerposts.Select(t => new OwnerListViewModel
        {
            Created = t.Created,
            PostName = t.PostName,
            Dormant = t.Dormant,
            OwnerId = t.OwnerId,
        });

        return PartialView("_OwnerList", viewModel);
    }

and adding a OwnerPost Class:

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

    public string PostName { get; set; }

    public bool Dormant { get; set; }

    public DateTime Created { get; set; }
}

I solved the issue