Error inserting row to the sql server from linqtosql with the existance of a trigger

Question

I created a trigger for the table below in the sql server,

Buildings: ID decimal(24, 0) PK, Name varchar(255)

The trigger is

CREATE TRIGGER [dbo].[TRG_BLD]
   ON  [dbo].[Building] 
   INSTEAD OF INSERT
AS 
BEGIN
    INSERT INTO Building (Name)
    SELECT Name
    FROM inserted
END

All it does, only inserting the row into the table (it does more, but I'm trying to simplify my case).

When I'm inserting a row from the sql server, everything is fine, but when I'm inserting through LinqToSql,

Building b = new Building();
b.Name = "building A";
DC.Buildings.InsertOnSubmit(b);
DC.SubmitChanges();

an exception occurs on 'SubmitChanges' saying :

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.Linq.dll

Additional information: The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type.

You are not supplying a value for the primary key. If it does not have to be a decimal use an IDENTITY field. Or you are going to have to come up with a method of generating unique values for it — Simon Wilson, Dec 22 '15 at 13:31
@SimonWilson I changed the type of the ID to int, though it was already auto-incremented Identity, but I'm still getting the following error: " The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type." — Ahmad Hammoud, Dec 22 '15 at 13:49
@AhmadHammoud you need to change your Entity to Int32 as well. and add `Property(x => x.ID).HasColumnName("ID").IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);` to your entity configuration — vantian, Dec 22 '15 at 15:35

Devart · Answer 1 · 2015-12-22T14:36:35.660

0

IF OBJECT_ID('dbo.Building', 'U') IS NOT NULL
    DROP TABLE dbo.Building
GO
CREATE TABLE dbo.Building
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(255)
)
GO
CREATE UNIQUE NONCLUSTERED INDEX ix ON dbo.Building (Name) WITH(IGNORE_DUP_KEY=ON)
GO

CREATE TRIGGER dbo.TRG_BLD
   ON  dbo.Building
   INSTEAD OF INSERT
AS 
BEGIN

    SET NOCOUNT ON;

    INSERT INTO dbo.Building (Name)
    SELECT Name
    FROM INSERTED

END
GO

INSERT INTO dbo.Building (Name)
VALUES ('a'), ('a'), ('b'), ('c'), (NULL), (NULL)
GO

SELECT *
FROM dbo.Building

output -

ID          Name
----------- ------------
1           a
3           b
4           c
5           NULL

edited Dec 22 '15 at 14:36

answered Dec 22 '15 at 13:58

Devart

119,203
23
166
186

I just tried it, but I'm still getting this error.. "An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.Linq.dll Additional information: The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type." – Ahmad Hammoud Dec 22 '15 at 14:21
please note that `ID` is `INT`. change your code and check again – Devart Dec 22 '15 at 14:26
I have exactly the same error too, and the primary key column in my table is of type INT, couldn't find a solution for it yet – Saleem Dec 22 '15 at 14:29
@Devart I ran the same code you provided me, and tested it, but still the same exception, I even tried it on another PC, a different project with a different database, the same.. maybe is it something with the LinqToSql? – Ahmad Hammoud Dec 22 '15 at 14:30
@Devart my problem is not from sql-server, everything is working fine to me when I insert from the sql management studio, My problem is when I'm using LinqToSql to insert. – Ahmad Hammoud Dec 22 '15 at 14:47
@Devart Would you mind explaining what your code should fix? I don't see much of a difference. – Gert Arnold Dec 22 '15 at 14:50
just added a unique index which avoids duplication in `dbo.Building` – Devart Dec 22 '15 at 14:51
1

The issue is that the trigger hides `SCOPE_IDENTITY()` from linq-to-sql. So after the insert, linq-to-sql receives null from the database and tries to assign that value to a decimal. – Gert Arnold Dec 22 '15 at 14:56
@GertArnold So there's no solution for my problem? – Ahmad Hammoud Dec 22 '15 at 15:03
You could consider moving to Entity Framework in which it's peanuts to use sprocs for CUD actions, while the basic architecture closely resembles linq-to-sql. – Gert Arnold Dec 22 '15 at 15:09

Error inserting row to the sql server from linqtosql with the existance of a trigger

1 Answers1