代码优先实体框架可以在同一个框上与SQL Server数据库进行跨数据库查询吗？ [英] Can code-first Entity Framework do cross database queries with SQL Server DBs on the same box?

问题描述

我知道有关实体框架在同一个服务器上发布的跨数据库查询有很多问题发布到计算器。大多数情况下，答案似乎是不，而这个链接来自返回2008年的引用。然而，实体框架正在随着CTP5而改变，我想知道答案是否仍然是一样的 - 你不能做，或者你可以手动编辑edmx文件，或者你有使用观点。单独的这个功能是我仍然绑定到Linq到SQL的原因，因为我们在同一台服务器上有多个SQL Server 2008数据库，需要在它们之间进行查询。用数百个 select * 视图污染我们的数据库不是一个选项，并且通过代码优先开发，我没有一个edmx文件进行编辑。我正在玩酒吧数据库，看看我能不能去某个地方，但我被困住了。任何建议？

  using System; 
使用System.Collections.Generic; 
使用System.Diagnostics; 
使用System.Linq; 
使用System.ComponentModel.DataAnnotations; 
使用System.Data.Entity; 
使用System.Data.Entity.ModelConfiguration; 
 
命名空间DbSchema {
 public class Employee {
 [Key] 
 public string ID {get;组; } 
 public string FirstName {get;组; } 
 public string LastName {get;组; } 
 public short JobID {get;组; } 
 public Job Job {get;组; 
} 
 
 public class Job {
 [Key] 
 public short ID {get;组; } 
 public string描述{get;组; } 
} 
 
 public class PubsRepository：DbContext {
 public DbSet< Employee>员工{get;组; } 
 public DbSet< Job>工作{get;组; } 
 
 protected override void OnModelCreating（ModelBuilder modelBuilder）{
 // employee 
 var eeMap = modelBuilder.Entity< Employee>（）; 
 eeMap.ToTable（employee，dbo）; //<  - 我如何引用另一个数据库？ 
 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（）; //<  - 在引用另一个数据库时出现错误！ 
} 
} 
} 
  

解决方案

我认为答案依然没有，但是有一些方法。

为什么它不是，EF使用DBContext的原因，以及一个上下文有一个连接字符串，一个连接字符串到数据库。

这里有两种方法：

• 对每个数据库使用两个不同的上下文，这意味着将数据提供给客户端并将其合并到客户端。


• 使用链接表数据库，通过视图拉取数据，以便EF将其视为来自单个数据库。

在代码中，您似乎是使用2个dbcontexts

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!
        }
    }
}

解决方案

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

这篇关于代码优先实体框架可以在同一个框上与SQL Server数据库进行跨数据库查询吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！