EF6 generated SQL is not valid SQL server side

.net entity-framework-6 sql-server

Question

This one sounds crazy - but fact is following is what I observe: I have a apsx mvc application connected to a SQL server using EF6. From time to time I I get exception on the underlaying generated SQL. Like:

Incorrect syntax near the keyword 'AS'" and "Incorrect syntax near '[Extend1]'

(And variations of this)

In my best opinion that should not happen from a framework like EF.

Luckily I have introduced a SQL interceptor (like https://docs.microsoft.com/en-us/ef/ef6/fundamentals/logging-and-interception) given me that chance to see the SQL prior to execution. My first step was to examine the generated SQL - and that looks sane. In an other step I attached a profiler on the SQL server! And guess what - the SQL that the intercepted logs does not match the SQL recorded in the profiler. (sound fubar, but it is what is observer) Conclusion is so far: from time to time single characters in the SQL changes (In the observed cases it is commas that becomes formfeed.)

Does anyone on the internet have a clue what could be the background for this?

UPDATE 1: (Requested information)

=== EF Code ===

public Customer GetById(string id)
{
    return DbContext.Customers.Include("Address").FirstOrDefault(a => a.Id == id);
}

=== From the Interceptor ===

2019-11-06 09:07:14.471 +01:00 [Interceptors.SqlLogInterceptor] [44] [<<username>>] [Debug] SELECT 
     [Limit1].[AddressTypeId] AS [AddressTypeId], 
    [Limit1].[Id] AS [Id], 
    [Limit1].[AddressId] AS [AddressId], 
    [Limit1].[ShowPrebooking] AS [ShowPrebooking], 
    [Limit1].[Id1] AS [Id1], 
    [Limit1].[Name1] AS [Name1], 
    [Limit1].[Name2] AS [Name2], 
    [Limit1].[Address1] AS [Address1], 
    [Limit1].[Address2] AS [Address2], 
    [Limit1].[HouseNumber] AS [HouseNumber], 
    [Limit1].[PostCode] AS [PostCode], 
    [Limit1].[City] AS [City], 
    [Limit1].[State] AS [State], 
    [Limit1].[CountryCode] AS [CountryCode], 
    [Limit1].[ContactName] AS [ContactName], 
    [Limit1].[Email] AS [Email], 
    [Limit1].[Phone] AS [Phone], 
    [Limit1].[Mobile] AS [Mobile], 
    [Limit1].[CreatedByUserId] AS [CreatedByUserId], 
    [Limit1].[CreatedDateTime] AS [CreatedDateTime], 
    [Limit1].[UpdatedByUserId] AS [UpdatedByUserId], 
    [Limit1].[UpdatedDateTime] AS [UpdatedDateTime], 
    [Limit1].[DeliveryDescription] AS [DeliveryDescription], 
    [Limit1].[OpeningHoursTo] AS [OpeningHoursTo], 
    [Limit1].[OpeningHoursFrom] AS [OpeningHoursFrom]
    FROM ( SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[AddressId] AS [AddressId], 
        [Extent1].[ShowPrebooking] AS [ShowPrebooking], 
        [Extent2].[Id] AS [Id1], 
        [Extent2].[Name1] AS [Name1], 
        [Extent2].[Name2] AS [Name2], 
        [Extent2].[Address1] AS [Address1], 
        [Extent2].[Address2] AS [Address2], 
        [Extent2].[HouseNumber] AS [HouseNumber], 
        [Extent2].[PostCode] AS [PostCode], 
        [Extent2].[City] AS [City], 
        [Extent2].[State] AS [State], 
        [Extent2].[CountryCode] AS [CountryCode], 
        [Extent2].[ContactName] AS [ContactName], 
        [Extent2].[Email] AS [Email], 
        [Extent2].[Phone] AS [Phone], 
        [Extent2].[Mobile] AS [Mobile], 
        [Extent2].[AddressTypeId] AS [AddressTypeId], 
        [Extent2].[CreatedByUserId] AS [CreatedByUserId], 
        [Extent2].[CreatedDateTime] AS [CreatedDateTime], 
        [Extent2].[UpdatedByUserId] AS [UpdatedByUserId], 
        [Extent2].[UpdatedDateTime] AS [UpdatedDateTime], 
        [Extent2].[DeliveryDescription] AS [DeliveryDescription], 
        [Extent2].[OpeningHoursTo] AS [OpeningHoursTo], 
        [Extent2].[OpeningHoursFrom] AS [OpeningHoursFrom]
        FROM  [dbo].[Customers] AS [Extent1]
        INNER JOIN [dbo].[Addresses] AS [Extent2] ON [Extent1].[AddressId] = [Extent2].[Id]
        WHERE [Extent1].[Id] = @p__linq__0
    )  AS [Limit1]
2019-11-06 09:07:14.471 +01:00 [Yourleman.Ef.Interceptors.SqlLogInterceptor] [44] [<<username>>] [Debug] -- Failed in 1 ms with error: Incorrect syntax near 'Limit1'.
Incorrect syntax near the keyword 'AS'.

=== Trace from profiler ===

exec sp_executesql N'SELECT 
    [Limit1].[AddressTypeId] AS [AddressTypeId], 
    [Limit1].[Id] AS [Id], 
    [Limit1].[AddressId] AS [AddressId], 
    [Limit1].[ShowPrebooking] AS [ShowPrebooking], 
    [Limit1].[Id1] AS [Id1], 
    [Limit1].[Name1] AS [Name1], 
    [Limit1].[Name2] AS [Name2], 
    [Limit1].[Address1] AS [Address1], 
    [Limit1].[Address2] AS [Address2], 
    [Limit1].[HouseNumber] AS [HouseNumber], 
    [Limit1].[PostCode] AS [PostCode], 
    [Limit1].[City] AS [City], 
    [Limit1].[State] AS [State], 
    [Limit1].[CountryCode] AS [CountryCode], 
    [Limit1].[ContactName] AS [ContactName], 
    [Limit1].[Email] AS [Email], 
    [Limit1].[Phone] AS [Phone], 
    [Limit1].[Mobile] AS [Mobile], 
    [Limit1].[CreatedByUserId] AS [CreatedByUserId], 
    [Limit1].[CreatedDateTime] AS [CreatedDateTime], 
    [Limit1].[UpdatedByUserId] AS [UpdatedByUserId], 
    [Limit1].[UpdatedDateTime] AS [UpdatedDateTime], 
    [Limit1].[DeliveryDescription] AS [DeliveryDescription], 
    [Limit1].[OpeningHoursTo] AS [OpeningHoursTo] 
    [Limit1].[OpeningHoursFrom] AS [OpeningHoursFrom]
    FROM ( SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[AddressId] AS [AddressId], 
        [Extent1].[ShowPrebooking] AS [ShowPrebooking], 
        [Extent2].[Id] AS [Id1], 
        [Extent2].[Name1] AS [Name1], 
        [Extent2].[Name2] AS [Name2], 
        [Extent2].[Address1] AS [Address1], 
        [Extent2].[Address2] AS [Address2], 
        [Extent2].[HouseNumber] AS [HouseNumber], 
        [Extent2].[PostCode] AS [PostCode], 
        [Extent2].[City] AS [City], 
        [Extent2].[State] AS [State], 
        [Extent2].[CountryCode] AS [CountryCode], 
        [Extent2].[ContactName] AS [ContactName], 
        [Extent2].[Email] AS [Email], 
        [Extent2].[Phone] AS [Phone], 
        [Extent2].[Mobile] AS [Mobile], 
        [Extent2].[AddressTypeId] AS [AddressTypeId], 
        [Extent2].[CreatedByUserId] AS [CreatedByUserId], 
        [Extent2].[CreatedDateTime] AS [CreatedDateTime], 
        [Extent2].[UpdatedByUserId] AS [UpdatedByUserId], 
        [Extent2].[UpdatedDateTime] AS [UpdatedDateTime], 
        [Extent2].[DeliveryDescription] AS [DeliveryDescription], 
        [Extent2].[OpeningHoursTo] AS [OpeningHoursTo], 
        [Extent2].[OpeningHoursFrom] AS [OpeningHoursFrom]
        FROM  [dbo].[Customers] AS [Extent1]
        INNER JOIN [dbo].[Addresses] AS [Extent2] ON [Extent1].[AddressId] = [Extent2].[Id]
        WHERE [Extent1].[Id] = @p__linq__0
    )  AS [Limit1]',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'2341234'

Examine the bytes showed that after the [OpeningHoursTo] there is a Form Feed character (\u000C)

1
3
11/7/2019 7:52:45 AM

Popular Answer

Lo and behold, on the 3rd of February, the error returned out of nowhere. All traffic out of our production server seemed to be affected and our customers would experience random errors.

Using log files and Wireshark on the server hosting the website we could rule out that particular server, since the data in the TCP packages was correct. We could also rule out the database server and another application server, since requests to both servers would fail in the same way.

In the end, Infrastructure was able to isolate the issue to a defective port on a switch in the network. After disabling this port, the issues was resolved instantly.

1
2/17/2020 9:32:51 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow