CODEWITHSUNDEEP
linq-to-sql
Josh Kropf
Josh Kropf

Reputation: 21

Primary key value from SQL function

I have written a user defined function in SQL to generate random integers to be used as a primary key:

CREATE VIEW [dbo].[GetNewID]
AS
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS new_id;

CREATE FUNCTION [dbo].[RandomID] ()
RETURNS INT
AS
BEGIN
    DECLARE @new_id AS INT
    SET @new_id = (SELECT new_id FROM GetNewID)

    IF (SELECT COUNT(*) FROM Foo WHERE FooID = @new_id) != 0
        SELECT @new_id = [dbo].[RandomID]()

    RETURN @new_id
END;

With the following table:

CREATE TABLE [dbo].[Foo] (
    [FooID] [int] PRIMARY KEY DEFAULT([dbo].[RandomID]()),
    [val] [varchar](32)
);

Using SQL to insert records works as expected.

However when attempt to create a new record with LINQ I get the following error:

Cannot insert the value Null into column id, column does not allow nulls

In my LINQ to SQL model FooID has the following column attributes:

[ColumnAttribute(Storage="_FooID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL", IsPrimaryKey=true, IsDbGenerated=true)]

If I check the SQL generated by LINQ the problem is pretty clear:

INSERT INTO [dbo].[Foo]([val])
VALUES (@p0)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input VarChar (Size = 8000; Prec = 0; Scale = 0) [Testing]

LINQ is trying to get the last value from an identity insert which will return null.

So I suppose the question comes down to; can I have an auto-generated primary key property in LINQ that does NOT use an identity column in the underlying SQL? Is there another approach I could take that would yield the same result?

Note: I would like to avoid changing application code. Ideally this should Just Work when changing my existing tables from using a linear increment primary key to a randomly generated one.

Upvotes: 2

Views: 685

Answers (1)

istepaniuk
istepaniuk

Reputation: 4271

In the DBML designer, set the Auto Generated Value property to true for that column. That should do the trick.

Check this post, and this thread in the MSDN forums

Upvotes: 1

Related Questions