Geek asks and answers

Issue with DateTime setting with linked servers and Entity Framework First base code

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

Instead of creating a new DbContext for db2, we created a Linked Server and created a synonym for this table in db1. I have configured mappings for this in my first db1 code context. this works and I can get the 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 is as follows:

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 delete the dates, the query runs fine.

server1 is SQL 2008
server2 is SQL 2005

, , - , , Entity Framework.

+5
2

, Entity Framework 6 :

  • Server1 (= "" ): MS SQL Server 2008 R2
  • 2 (= "" ): MS SQL Server ( 2005)

"" , :

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

:

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

, POCO:

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

, StartTime EndTime, , :

OLE DB "SQLNCLI10" "Server2".

:

DateTime .NET , datetime2 SQL Server. EF datetime datetime2 SQL Server, , DateTime .NET, datetime2.

MSA SQL Server 2008 R2 2005 :

DateTime # → EF → DateTime2 SQL, "" SQL Server 2008 R2. "" , DateTime2.

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

.

+4

, ExecuteStoreQuery.

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();

, .

0

More articles:


All Articles