CODEWITHSUNDEEP
c#sql-serverlinqentity-framework-core
Fatemeh Ramezani
Fatemeh Ramezani

Reputation: 103

How to find the equivalent Linq command for translated SQL Query

I have a table with Guid as a primary key and multiple nvarchar(max) columns:

CREATE TABLE [dbo].[Tables](
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Description] [nvarchar](max) NULL,
CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED 
([Id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And

public partial class Table : INamedEntity, IEntityKey<Guid>
{
    public Guid Id { get; set; }

    private Table() { }

    public string Name { get; set; }

    public string Description { get; set; }
}

which is translated to this query in SQL Server:

    SELECT   [Id], DATALENGTH([Description]), [Description] , DATALENGTH([Name]), [Name] 
    FROM     [dbo].[Table]
    WHERE    [Id] < @UpperBound
    ORDER BY [Id] ASC

I have the same problem with another table with 

public class Document : IEntityKey<Guid>
{
    public Document() { }

    public Guid Id { get; set; }

    [StringLength(500)]
    public string Name { get; set; }

    public byte[] Data { get; set; }

    [StringLength(10)]
    public string Extension { get; set; }
}

And 

(@UpperBound uniqueidentifier)
SELECT   [Id],  CASE WHEN DATALENGTH([Data]) >= 4116480 THEN DATALENGTH(0x2142494E5F46494C45213A + CAST (CAST (newid() AS VARCHAR (100)) AS VARBINARY (100))) ELSE DATALENGTH([Data]) END, CASE WHEN DATALENGTH([Data]) >= 4116480 THEN 0x2142494E5F46494C45213A + CAST (CAST (newid() AS VARCHAR (100)) AS VARBINARY (100)) ELSE [Data] END, [Name],[Extension]  FROM [dbo].[Documents]  
    WHERE    [Id] < @UpperBound 
    ORDER BY [Id] ASC

As you can see Data column is weird in the translated query. These queries are slow and I should tune them but I can not find the their origins in C# code. Therefore I am looking for an equivalent translated Linq command. Would you please guide me about:

  1. What is the equivalent Linq?
  2. Why is nvarchar(max) translated to two columns DATALENGTH([Description]), [Description]?
  3. Why is byte[] translated to CASE WHEN DATALENGTH([Data]) >= 4116480 THEN DATALENGTH(0x2142494E5F46494C45213A + CAST (CAST (newid() AS VARCHAR (100)) AS VARBINARY (100))) ELSE DATALENGTH([Data]) END, CASE WHEN DATALENGTH([Data]) >= 4116480 THEN 0x2142494E5F46494C45213A + CAST (CAST (newid() AS VARCHAR (100)) AS VARBINARY (100)) ELSE [Data] END?

Upvotes: 0

Views: 143

Answers (1)

John Tseng
John Tseng

Reputation: 6352

In our environment, we also saw a similar strange and slow query with the same weird constant 0x2142494E5F46494C45213A. It was caused by a Data-tier Application Package (DACPAC) export. Even better was that canceling the job didn't kill the query, but killing SSMS stopped it.

Upvotes: 1

Related Questions