DateTime parameter issue with Linked Servers and Entity Framework Code First

Question

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.

Answer 1

We ran into the same problem using Entity Framework 6 with the following constellation:

• Server1 (="ours"): MS SQL Server 2008 R2
  
• linked Server2 (="theirs"): MS SQL Server (unknown version presumably 2005)

We had a view on "our" server which looked something like this:

CREATE VIEW [ourSchema].[SomeEntity]
AS
SELECT 
    [Id]
    ,...
    ,[StartTime]
    ,[EndTime]
FROM [SERVER2].[someDb].[theirSchema].[someTable]

The table on the linked server looked like the following:

CREATE TABLE [schema2].[table]
(
    [Id] [int] NOT NULL,
    ,...
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NULL
) ON PRIMARY

The model and the configuration created via reverse POCO creation:

public partial class SomeEntity
{
    public int Id { get; set; }
    public ...
    public DateTime StartTime { get; set; }
    public DateTime? EndTime { get; set; }
}
public partial class SomeEntityConfiguration : EntityTypeConfiguration<TestBedData>
{
    public SomeEntityConfiguration(string schema = "ourSchema")`
    {
        ToTable(schema + ".SomeEntity");
        HasKey(someLambdaExpression);
        Property(x => x.Id).HasColumnName("Id").IsRequired();
        Property(x => ...);
        Property(x => x.StartTime).HasColumnName("StartTime").IsRequired();
        Property(x => x.EndTime).HasColumnName("EndTime").IsOptional();
        InitializePartial();
    }
    partial void InitializePartial();
}

Any query involving the StartTime or EndTime properties resulted in the exception described in the initial post:

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

After having spent several hours understanding the underlying problem I stumbled across the following:

The DateTime type in .NET has the same range and precision as datetime2 in SQL Server. When EF inserts or updates a datetime or datetime2 column in SQL Server it converts the model property to the type that can hold the whole range of DateTime in .NET, that's datetime2. Link

Comparing the supported data types of the different MS SQL Server versions of 2008 R2 and 2005 finally revealed the problem:

DateTime in C# -> transformation via EF -> DateTime2 in SQL because we were communicating with "our" view on SQL Server 2008 R2. But the query was forwarded to "their" linked server which did not support the DateTime2 data type.

CREATE VIEW [ourSchema].[SomeEntity]
AS
SELECT 
    [Id]
    ,...
    ,CAST([StartTime] AS datetime2)
    ,CAST([EndTime] AS datetime2)
FROM [SERVER2].[someDb].[theirSchema].[someTable]

did the trick.