Using checksum index from linq to sql query

Question

I have a table with an index on a nvarchar(MAX)-column by using CHECKSUM() as a computed column. SQL query can use this index by using a WHERE-clause with both the text value and its checksum.

How can a linq-to-sql query use this index?

I looked at sqlfunctions.checksum of linq object and How do I calculate a checksum on all columns in a row using LINQ and Entity Framework? and their answers, i.e SqlFunctions.Checksum("important") for a checksum of a single value.
But SqlFunctions.Checksum("important") throws the exception:

This function can only be invoked from LINQ to Entities

(this behaviour is exactly as is documented in the documentation for Checksum(string)).

In sqlserver the table can be defined via:

CREATE TABLE [Tags](
    [TagId] [uniqueidentifier] NOT NULL PRIMARY KEY,
    [Tag] [nvarchar](max) NOT NULL,
    [TagHash] AS (checksum([Tag])) PERSISTED
 )

and the index via:

 CREATE INDEX [IX_Tags] ON [Tags] (
     [TagHash] ASC
 )

The following SQL query might use the index¹:

SELECT * FROM [Tags]
WHERE [TagHash] = checksum('Important') AND
    [Tag] = 'Important'

This query expression attempt in linqpad c# throws the NotSupportedException:

from tag in Tags
where SqlFunctions.Checksum("important") == tag.Hash &&
    "important" == tag.Tag
select tag

How can the above query be constructed in link-to-sql without using link-to-entities only constructs like SqlFunctions.Checksum(string)?

¹⁾ _{It depends on the query optimizer.}

sgmoore · Accepted Answer · 2017-07-21 12:34:37Z

1

If you can modify your database and change your Linq2Sql model then you can do this by creating your own sql function that wraps CheckSum. For example

Create FUNCTION dbo.MyCheckSum (@input NVarChar(max)) RETURNS int
AS
BEGIN   
   Declare @output int
   select @output = CheckSum(@input)
   return @output
End

You can then use this like.

from tag in Tags
where MyChecksum("important") == tag.Hash && "important" == tag.Tag
select tag

If you can't modify your database or model, you could have a workaround that works for your example, at the expense of another database hit, eg

int checkSum this dc.ExecuteQuery<int>("select Checksum('important')").Single(); 

from tag in Tags
where checkSum == tag.Hash &&   "important" == tag.Tag
select tag

answered Jul 21, 2017 at 12:34

sgmoore

16.1k6 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kasper van den Berg · Accepted Answer · 2017-07-25 11:30:20Z

0

Instead of writing a generic wrapper around CHECKSUM (as sgmoore suggests).

You can write an accessor function to access the table via the checksum index:

CREATE FUNCTION TagsByName(
     @key nvarchar(max))
RETURNS TABLE
AS
RETURN SELECT
    [TagId],
    [Tag],
    [TagHash]
FROM Tags
WHERE CHECKSUM(@key) = [TagHash] AND
    @key = [Tag];

Compared to sgmoore's solution this has the advantage that all link-to-sql- and SQL-queries can access the table via the TVF and are insulated from using the index and the checksums. The downside is that every checksum index construct requires its own TVF, whereas in sgmoore's solution a single TVF suffices.

answered Jul 25, 2017 at 11:30

Kasper van den Berg

9,7064 gold badges52 silver badges75 bronze badges

Collectives™ on Stack Overflow

Using checksum index from linq to sql query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related