CODEWITHSUNDEEP
c#.netentity-frameworklinq
Phill Sanders
Phill Sanders

Reputation: 487

Query two databases using LINQ

I have the following in my controller get method

private PeopleContext Peopledb = new PeopleContext();
private IARContext db = new IARContext();

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

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

I'm getting this error when I try and load the page:

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

I know the issue is that LINQ cant query two different contexts but having tried several solutions on here I cant seem to fix the issue and sucesfully query the Peopledb.Posts table to find the related PostName to display for each instance in the db.Owners table.

Upvotes: 1

Views: 138

Answers (3)

AD.Net
AD.Net

Reputation: 13399

You can try this:

var owners = (from s in db.Owners
                     where s.Dormant == false
                     orderby s.Post.PostName
                     select s).ToList();

This will execute the code in one context and have the List<Owner> in memory for the other context.

Also take a look for the execution of the .Select part, does it execute a separate query for each owner? If so you should optimize it, you can get the posts beforehand using the ids and then build your viewmodel.

Upvotes: 2

D Stanley
D Stanley

Reputation: 152521

Just transition from Linq-to-Entities to Linq-to-Objects using AsEnumerable():

    var viewModel = owners.AsEnumerable()
                          .Select(t => new OwnerListViewModel
    ...

Upvotes: 0

Kirill Bestemyanov
Kirill Bestemyanov

Reputation: 11964

Entity Framework context can work only with single database. If you want to get data from another database in this context, you can create proxy View, that will reflect this data in db of your dbcontext.

Upvotes: 0

Related Questions