MVC Multiple Databases in one single domain model

Question

I am writting an app based on Steve Sanderson's Pro Asp .Net MVC Framework, which uses a data modeling like described on Sports Store app on that book. My app works well, I use Castle project as IOC but I have two databases. The first one stores many kind of data and my clients information. The second one stores only medical data of each client. I need to create a class that returns data in clients table, located on database 1 and medical information of each client stored on database 2.

Below are my code:

My connectionstrings on web.config:

<add name="CNRConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=CNR;Persist Security Info=True;User ID=sa;Password=@#@@@!" providerName="System.Data.SqlClient"/>

<add name="CNRpeConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=pe;Persist Security Info=True;User ID=sa;Password=@#@@@!" providerName="System.Data.SqlClient"/>

My new class (just data, no table conection):

namespace DomainModel.Entities
{
    public class ImprimePeriodoPaciente
    {
        public int CodConsulta { get; set; }
        public DateTime DataConsulta { get; set; }
        public string Evolucao { get; set; }
        public int CodProfissional1 { get; set; }
        public int CodPaciente1 { get; set; }
        public string NomeProfissional { get; set; } //receives data from other db
        public string NomePaciente { get; set; } //receives data from other db
        public int CodProcedimento { get; set; }
        public bool TagConsulta { get; set; }
        public int? TagConsulta2 { get; set; } 
        public DateTime di { get; set; }
        public DateTime df { get; set; }
    }
}

This is my concrete and interface from domainmodel:

public  class SqlConsultasRepository:IConsultasRepository
    {
     private Table<Consulta> consultasTabela;
     private Table<TEvolucao> tEvolucaoTabela;
     private Table<Paciente> pacientesTabela;
     private Table<Funcionario> funcionariosTabela;

     public SqlConsultasRepository(string connectionString)
     {
         consultasTabela = (new DataContext(connectionString)).GetTable<Consulta>();
         tEvolucaoTabela = (new DataContext(connectionString)).GetTable<TEvolucao>();
         pacientesTabela = (new DataContext(connectionString)).GetTable<Paciente>();
         funcionariosTabela = (new DataContext(connectionString)).GetTable<Funcionario>();
     }


        public IQueryable<ImprimePeriodoPaciente> Prontuarios            { 
            get
            { 
                return
                    (
                       from c in consultasTabela
                       join p in pacientesTabela on c.CodPaciente1 equals p.CodigoPaciente //my doom
                       join f in funcionariosTabela on c.CodProfissional1 equals f.CodigoFuncionario //my doom
                       select new ImprimePeriodoPaciente
                       {
                           CodConsulta=c.CodConsulta,
                           DataConsulta=c.DataConsulta,
                           Evolucao=c.Evolucao,
                           NomeProfissional= f.NomeFuncionario, //my doom
                           NomePaciente=p.NomePaciente, //my doom
                           CodProfissional1=c.CodProfissional1,
                           CodPaciente1 = c.CodPaciente1
                       }
                   );
            }

        }

The code is marked with 'my doom' to show where the problem occurs.... I need to create a connectionString reference to the other database because this tables are not in current database specified by connectionString variable. I am getting error saying that the data are not from same datacontext (or shomething like that).

So, could you help?

Answer 1

Got my way through it based on this post:

http://dotnetprogrammingtipsbymuhil.blogspot.com.br/2009/12/query-contains-references-to-items.html

For those who get on same problem, follow above post steps.

Im my project I've created an interface to a concrete method that takes separated data from each database and put them on separated lists, as follows:

namespace DomainModel.Concrete
{
    public class SqlImprimePeriodoPacientesRepository : IImprimePeriodoPacientesRepository
    {
    private static string cConexao = "Data Source=.\\SQLEXPRESS;Initial Catalog=CNR;Persist Security Info=True;User ID=sa;Password=23092000";
    private static string cConexao2 = "Data Source=.\\SQLEXPRESS;Initial Catalog=PE;Persist Security Info=True;User ID=sa;Password=23092000";

    private IConsultasRepository cRepository = new SqlConsultasRepository(cConexao2);
    private IPacientesRepository pRepisotory = new SqlPacientesRepository(cConexao);
    private IFuncionariosRepository fRepository = new SqlFuncionariosRepository(cConexao);

    public IQueryable<ImprimePeriodoPaciente> ImprimePeriodoProntuarios//(int? codpac, DateTime? di, DateTime? df)
    {
        get
        {
            var consultas =
                (
                    from c in cRepository.Consultas
                    select new
                    {
                        c.cdf,
                        c.cdi,
                        c.CodConsulta,
                        c.CodPaciente1,
                        c.CodProcedimento,
                        c.CodProfissional1,
                        c.DataConsulta,
                        c.df,
                        c.di,
                        c.Evolucao,
                        c.TagConsulta,
                        c.TagConsulta2
                    }
                )
                .ToList();

            var pacientes =
                (
                    from p in pRepisotory.Pacientes
                    select new
                    {
                        p.CodigoPaciente,
                        p.NomePaciente
                    }
                )
                .ToList();

            var funcionarios =
                (
                    from f in fRepository.Funcionarios
                    select new
                    {
                        f.CodigoFuncionario,
                        f.NomeFuncionario
                    }
                )
                .ToList();

            var result =
                (
                    from c in consultas
                    join p in pacientes on c.CodPaciente1 equals p.CodigoPaciente
                    join f in funcionarios on c.CodProfissional1 equals f.CodigoFuncionario
                    select new ImprimePeriodoPaciente
                    {
                        CodConsulta = c.CodConsulta,
                        DataConsulta = c.DataConsulta,
                        Evolucao = c.Evolucao,
                        NomeProfissional = f.NomeFuncionario,
                        NomePaciente = p.NomePaciente,
                        CodProfissional1 = c.CodProfissional1,
                        CodPaciente1 = c.CodPaciente1
                    }
                ).ToList();


            return result.AsQueryable();
        }
    }
}
}

That method did the job on the Domain´s Model side, so the rest resides on dealing with the results.

Hope this help someone!