Using Dapper and LINQ I am able to get the data from two different databases. See the code below.
//Get Person details from Database 1 and populate the Model.
string sql1 = "Select * from dbo.person p left join dbo.address a on p.id = a.id";
var person = connection.Query<Person>(sql1);
//Get Employee details from Database 2 and populate the Model.
string sql2 = "Select * from dbo.Employee";//I want to AVOID this, as it will fetch all the records.
var employee = connection.Query<Employee>(sql2);
//Use LINQ to get the desired result
var res = from p in person
join e in employee
on p.emp_id equals e.emp_id into prsnemp
from pe in prsnemp.DefaultIfEmpty()
select new
{
Emp_Name = p.emp_name,
Emp_Salary = pe?.emp_salary ?? string.Empty
};
I want to avoid getting all the rows from Employee table and only get what is really necessary.
One solution is to create a synonym and put it in Database 1, so I could basically have something like the following
string finalSql = "Select p.emp_name, e.emp_salary from dbo.person p
LEFT JOIN dbo.address a on p.id = a.id
LEFT JOIN dbo.employee e on e.id = p.id";
var result = connection.QueryAsync<EmployeePerson>(finalSql);
but I do not want to have synonyms either.
Thoughts?
Update - 1 I looked into the provided post and its answer I do not think it is going to work in my case, for two reasons.
- We will eventually move to the cloud. I wouldn't want to devise a solution that cross-references multiple databases and then runs into issues as others have asked in many posts, e.g. here.
- I also wouldn't want to add another task to our architect's plate to figure out how to make the cross-database query work, if I could find another way.
As of now I have a way forward to fetch the data from another database by passing a list of IDs, but this only works when INNER JOIN is in play, I do not know how to achieve the same for LEFT JOIN, unless of course I first get all the data from another database's table and then use LINQ's DefaultIfEmpty() method.
