So you have a Model or
Poco (or ViewModel) and you want to populate it via EF code first. However, the Entity Framework refuses to let you run a simple query against the connection when it involves a table that's linked.
Here's the Poco Model of the linked db
[Table("Customer", Schema="LinkedDatabaseName].dbo")]
public class Customer
{
[Key]
[Column("Customer_Id")]
public int CustomerId { get; set; }
[Column("Company_Name")]
[Display(Name="CustomerName")]
public string CompanyName { get; set; }
public string CustomerAlias { get; set; }
}
Here's the Context Method that consumes it:
public IEnumerable<LandingViewModel> GetTrackingFiles()
{
var q = from tf in TrackedFiles
join lfs in FileStatuses on tf.FileStatusId equals lfs.FileStatusId into lfsg
from fs in lfsg.DefaultIfEmpty()
join lc in Customers on tf.CustomerId equals lc.CustomerId into lcg
from c in lcg.DefaultIfEmpty()
select
new
{
tf.FileGuid,
tf.FileName,
tf.FileId,
tf.CustomerId,
tf.ExpectedTrxCount,
tf.ActualTrxCount,
tf.ExpectedTrxPaidAmount,
tf.ActualTrxPaidAmount,
tf.ExpectedClaimCount,
tf.ActualClaimCount,
tf.FileRecieved,
tf.FileCompleted,
fs.FileStatusName,
tf.FilePath,
c.CompanyName,
c.CustomerAlias
};
var sql = q.ToString();
var fixedSql = sql.Replace("[LinkedDatabaseName]].dbo]", "[LinkedDatabaseName].[dbo]");
Trace.WriteLine(fixedSql); // look at the result in the trace output
var result=this.Database.SqlQuery<LandingViewModel>(fixedSql);
return result;
}
