DateTime parameter issue with Linked Servers and Entity Framework Code First

Viewed 1953

I have two databases on two servers. My app primarily uses db1 on server1. However, there is one table that I will only read from in db2 on server2.

Rather than created a new DbContext to db2, we created a Linked Server and set up a synonym for this table in db1. I set up mappings for this in my db1 Code First Context. this appears to be working and I can retrieve data.

However, if I use any dates in my predicate, I get the following error:

A failure occurred while giving parameter information to OLE DB provider "SQLNCLI10" for linked server "server2".

My mapping looks like this:

ToTable("synonym");

Property(t => t.Id).HasColumnName("ID");
Property(t => t.Company).HasColumnName("Company");
Property(t => t.StartDate).HasColumnName("StartDate");
Property(t => t.EndDate).HasColumnName("EndDate");
Property(t => t.LastUpdatedDate).HasColumnName("LastUpdatedDate");
Property(t => t.LastUpdatedBy).HasColumnName("LastUpdatedBy");

I am trying to run the following query:

_context.Set<Synonym>()
    .Any(s => s.Company == company
            && s.StartDate <= date
            && (s.EndDate >= date || s.EndDate == null));

If I remove the dates, the query runs fine.

server1 is SQL 2008
server2 is SQL 2005

I found this thread that suggests there is some sort of issue with dates, but I cannot figure out how to apply it to Entity Framework.

3 Answers

PI OLEDB Enterprise Linked Server View and SQL Stored Proc parameter were both set as DateTime. We switched the stored proc to DateTime2 and this fixed the error.

Related