How to efficiently join tables using Dapper ORM from Two different databases

by

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?

Leave a Comment