CODEWITHSUNDEEP
c#sqllinqjoin
afom
afom

Reputation: 61

linq-sql join two tables and select columns

I'm new to sql-linq and I'm trying to join two tables using their common id which is motherid. I have done that but when I try to convert the returned value into list it throws an exception saying "The query contains references to items defined on a different data context." Here is the code.

               var todaySecondVisitProfile = (from a in _maternalvisitvaluedb.Value
               join b in _maternalcarevaluedb.Value on a.MotherId equals b.MotherID
               where (DateTime)a.SecondVisit.Date == DateTime.Now.Date
               select new
                          {
                            FirstName = b.FirstName,
                            LastName = b.LastName,
                            PhoneNo = b.PhoneNo
                           }).ToList();

If I can't convert the result into list how can I access my result? tnx for the help.

Upvotes: 1

Views: 3295

Answers (1)

codebased
codebased

Reputation: 7073

I think you are trying to do a Linq on two different databases. If so then you should so something like this:

var firstQuery = (from s in _maternalvisitvaluedb.Value select s).ToList();
var secondQuery = (from t in _maternalcarevaluedb.Value select t).ToList();

var result = (from s in firstQuery
join k in secondQuery
on s.MotherId equals k.MotherId
 where (DateTime)s.SecondVisit.Date == DateTime.Now.Date 
 select s).ToList();

Upvotes: 3

Related Questions