Can code-first Entity Framework do cross database queries with SQL Server DBs on the same box?

ef-code-first entity-framework sql-server

Question

I know there have been a lot of questions about Entity Framework doing cross database queries on the same server posted to stackoverflow. Mostly the answer seems to be 'no', and this link from way back in 2008 is referenced. However, Entity Framework is changing all the time and with CTP5 out, I'm wondering if the answer is still the same - that you can't do it, or you can do it if you manually edit the edmx file, or you have to use views. This feature alone is the reason I'm still tied to Linq-to-SQL, as we have multiple SQL Server 2008 databases on the same server and need to query across them. Polluting our databases with hundreds of select * views is not an option, and with code-first development I don't have an edmx file to edit. I was playing with the pubs database to see if I could get somewhere, but I'm stuck. Any suggestions?

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration;

namespace DbSchema {
    public class Employee {
        [Key]
        public string ID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public short JobID { get; set; }
        public Job Job { get; set; }
    }

    public class Job {
        [Key]
        public short ID { get; set; }
        public string Description { get; set; }
    }

    public class PubsRepository : DbContext {
        public DbSet<Employee> Employee { get; set; }
        public DbSet<Job> Job { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder) {
            // employee
            var eeMap = modelBuilder.Entity<Employee>();
            eeMap.ToTable("employee", "dbo"); // <-- how do I reference another database?
            eeMap.Property(e => e.ID).HasColumnName("emp_id");
            eeMap.Property(e => e.FirstName).HasColumnName("fname");
            eeMap.Property(e => e.LastName).HasColumnName("lname");
            eeMap.Property(e => e.JobID).HasColumnName("job_id");

            // job
            var jobMap = modelBuilder.Entity<Job>();
            jobMap.Property(j => j.ID).HasColumnName("job_id");
            jobMap.Property(j => j.Description).HasColumnName("job_desc");
        }

        public List<Employee> GetManagers() {
            var qry = this.Employee.Where(x => x.Job.Description.Contains("manager"));
            Debug.WriteLine(qry.ToString());
            return qry.ToList(); // <-- error here when referencing another database!
        }
    }
}
1
13
5/23/2017 11:54:24 AM

Accepted Answer

I think that the answer is still no, but there are ways around it.

The reason why it is no, it that EF uses a DBContext, and a context has a connection string, and a connection string goes to a database.

Here are 2 ways around it:

  • use 2 different contexts one against each database, this will mean bringing data to the client and merging it on the client.
  • use linked tables on the database, pulling data through views, so that EF sees it as coming from a single database.

In your code it looks like you are using 2 dbcontexts

12
3/15/2011 3:48:57 PM

Popular Answer

There are two ways to do it.

One is, of course, to create a view in one of the databases which does the cross database query, then access the veiw from your model as you would any other view.

The other was it to create the same cross database query view within the model itself by creating a DefiningQuery. This is most similar to how you would do it with SQLClient. In SQLClient, you'd create the view in T-SQL as the text of a SQLCommand, then execute the command to create a data reader or data table. Here you use the same T-SQL to create a DefiningQuery, then link it up with an Entity that you create manually. It's a bit of work, but it does exactly what you'd want it to.

Here's a link on using DefiningQuerys: http://msdn.microsoft.com/en-us/library/cc982038.aspx.

If you happen to have the book "Programming Entity Framework" by Lerman from O'Reilly, there a good example in chapter 16.

So you have to jump through a few hoops to do what you used to do directly with SQLClient, BUT you get the modeled Entity.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow