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.

It seems you have linked the server using OLE DB? can you link is using native SQL Server mode? — usr, Aug 20 '12 at 21:27
Try `!s.EndDate.HasValue || s.EndDate.Value >= date` (in this order) — Gert Arnold, Aug 20 '12 at 21:31
Great question. We too use SQLNCLI which I believe is based on OLE DB. I don't see how you could change it from SQLNCLI to something else. — SQLMason, Aug 21 '12 at 21:04
When I view the properties on our linked servers, the top radio called "SQL Server" is selected. Yours sounds like the bottom one ("OLE DB Provider for SQL Server"). How else would you explain the error message referencing OLE DB? — usr, Aug 21 '12 at 21:52

score 5 · Answer 1 · edited May 23 '17 at 12:14

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.

score 0 · Answer 2 · answered Aug 22 '12 at 18:35

0

I had the same issue and I ended up using the ExecuteStoreQuery method.

string formatter = "{0:yyyy/MM/dd}";

string start = String.Format(formatter, startDate);
string end = String.Format(formatter, endDate);

string sampleStatement = "SELECT * FROM Synonym WHERE StartDate >= '" + start + "' and EndDate <='" + end + "'";

var result = _context.ExecuteStoreQuery<Synonym>(sampleStatement, null).ToList();

I hope this approach help.

answered Aug 22 '12 at 18:35

MadJjack

1

1

This code uses string concatenation instead of parameters. Using parameters with ExecuteStoreQuery is a far better solution and sooo easy. – usr Aug 23 '12 at 09:41
Yes, I agree with that. That was a quick trivial example to demonstrate the ExecuteStoreMethod. That was my quick work around to solve the parameter linked server issue. But, thanks for the tip :) – MadJjack Aug 23 '12 at 13:34

score 0 · Answer 3 · answered Sep 09 '20 at 19:34

0

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.

answered Sep 09 '20 at 19:34

Patrick Wright

1

Are you answering the question or providing an update. Please clarify to avoid confusion. – jmm312 Sep 10 '20 at 11:21

DateTime parameter issue with Linked Servers and Entity Framework Code First

3 Answers3