asp.net mvc 4 -连接来自多个上下文的LINQ查询

Question

我正试图为我想在视图中显示的数据创建一个ViewModel。问题是，显示的数据分布在两个数据库中，每个数据库中都有多个表。我读到你不能跨多个上下文中加入Linq查询，这是有道理的，我也读到我们不能在EF5中使用Code-First来使用存储过程....这导致我使用3个不同的Linq查询，并试图将它们组合成一个ViewModel…我只是不知道怎么去那里。

以下是我的Linq查询:

var csdContext = new CSDContext(CustomerCode);
var masterContext = new MasterContext();
//Only returns 1 row - which is what we want.
List<Site> sites = (from s in csdContext.Sites
                join sa in csdContext.SiteAddresses
                    on s.SiteID equals sa.SiteID
                join a in csdContext.Addresses
                    on sa.AddressID equals a.AddressID
                join spv in csdContext.SiteProductVersions
                    on s.SiteID equals spv.ProductVersionID
                where s.SiteID == id
                select s).ToList();
//List
List<States> states = (from s in masterContext.StatesTable
                    select s).ToList();

我的ViewModel看起来像这样:

public class SiteDetailsViewModel
{
    public string Address { get; set; }
    public string Address2 { get; set; }
    public string City { get; set; }
    public string StateCode { get; set; }
    public string ZipCode { get; set; }
    public string OfficePhone { get; set; }
    public string MobilePhone { get; set; }
    public string AlternativePhone { get; set; }
    public int ProductVersionID { get; set; }
}

基本上，我需要这些表中的以下数据:

地址
Address2
城市
ZipCode

csdContext - Sites
OfficePhone
手机
AlternativePhone

csdContext - SiteProductVersions
ProductVersionID

masterContext - States
StateCode

SQL中的表连接方式如下:

SELECT csd_a.Address, csd_a.Address2, csd_a.City, mstr_st.StateCode, csd_a.ZipCode, csd_s.OfficePhone, csd_s.MobilePhone, 
csd_s.AlternativePhone, csd_spv.ProductVersionID
FROM CSD.dbo.Sites AS csd_s 
INNER JOIN  CSD.dbo.SiteAddress AS csd_sa ON csd_sa.SiteID = csd_s.SiteID 
INNER JOIN  CSD.dbo.Address AS csd_a ON csd_a.AddressID = csd_sa.AddressID 
INNER JOIN CSD.dbo.SiteProductVersions AS csd_spv ON csd_s.SiteID = csd_spv.SiteID 
INNER JOIN MasterDB.dbo.States AS mstr_st ON mstr_st.StateID = csd_a.StateID 

我不知道如何合并这3个结果来创建SiteDetailsViewModel的ViewModel数据。有人能帮忙吗?

Answer 1

如果您实现了OP中所描述的查询，您可以使用:

var query = sites.Join(
            states,
            si => si.StateID,
            st => st.StateID,
           (si, st) => new SiteDetailsViewModel
                        {
                             Address = si.Address,
                             Address2 = si.Address2,
                             City = si.City,
                             StateCode = st.StateCode,
                             ZipCode = si.ZipCode,
                             OfficePhone = si.OfficePhone,
                             MobilePhone = si.MobilePhone,
                             AlternativePhone = si.AlternativePhone,
                             ProductVersionID = siProductVersionID
                        });

Answer 2

另一个可能的解决方案是在db# 2中创建db# 1中的表的视图。然后，您可以在EF中为db# 2建模/映射这些视图。当然，如果需要更新任何表，仍然需要db# 1的上下文。但这种解决方案的好处是，您可以在单个上下文中完成所有的读访问，并在服务器上而不是在内存中进行连接。

Answer 3

不如这样写:

from ...
...
where s.SiteID == id
select new SiteDetailsViewModel()
{
    Address = sa.Address,
    Address2 = as.Address2,
    ..
}