0

I'm new to MVC & LINQ but we are using them in a new project and I need some help. We have an MVC 4 app that we are creating and I need to get the details of a particular site but the information is in 2 different databases. DB1 is where most of the info reside while DB2 has only 2 pieces that I need. In short, the DB1 has tables Sites, SiteAddress, Address, and ProductVersions. DB2 as States, Products, Versions, and ProductVersions. In SQL, I can use the following to get what I need:

SELECT db1_s.SiteName, db1_s.PhoneNumber, db1_a.Address, db1_a.Address2, db1_a.City, db2_st.StateName, db1_a.ZipCode, db2_v.VersionNumber, db1_pv.ProductVersionID
FROM DB1.dbo.Sites db1_s
INNER JOIN DB1.dbo.SiteAddress db1_sa ON db1_s.SiteID = db1_sa.SiteID
INNER JOIN DB1.dbo.Address db1_a ON db1_sa.AddressID = db1_a.AddressID
INNER JOIN DB1.dbo.SiteProductVersions db1_spv ON db1_s.SiteID = db1_spv.SiteID
INNER JOIN DB1.dbo.ProductVersions db1_pv ON db1_spv.ProductVersionID = db1_pv.ProductVersionID
INNER JOIN DB2.dbo.States db2_st ON db1_a.StateID = db2_st.StateID
INNER JOIN DB2.dbo.ProductVersions db2_pv ON db1_spv.ProductVersionID = db2_pv.ProductVersionID
INNER JOIN DB2.dbo.Versions db2_v ON db2_pv.VersionID = db2_v.VersionID
WHERE db1_s.SiteID = 35

I did some research and found there's really no way to use LINQ to join tables across different databases, instead to use Lists and combine them, which is where I'm having difficulty.

I created a Model for the view like so:

public class SiteDetailsViewModel
{
    public string SiteName { get; set; }
    public string Address { get; set; }
    public string Address2 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string ZipCode { get; set; }

    public string PhoneNumber { get; set; }

    public int ProductVersionId { get; set; }
    public List<Version> Versions { get; set; }
}

The view only contains labels to display the data, with the exception of Versions. I'd like that to be a dropdown list of available versions (which is why I created List).

Can anyone give me some guidance as to the best way to convert this to LINQ and use it as a data model for my view?

Robert
  • 1,696
  • 3
  • 36
  • 70
  • 1
    I think the big part of your question is answer [here](http://stackoverflow.com/questions/352949/linq-across-multiple-databases) – Son_of_Sam Jan 11 '13 at 21:04
  • What are you using for the LINQ provider? Linq to Sql ... Entity Framework? You can use multiple database with either. Linq to Sql will allow you to map to the three part table name. EF isn't so simple... you will need to use sql server synonyms. – Tom Brothers Jan 11 '13 at 21:11

0 Answers0