Friday, February 8, 2013

LinkedServerDatabase Query with CodeFirst Entity Framework

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

No comments:

Post a Comment