Create record. Linq to SQL

  • Thread starter Thread starter shapper
  • Start date Start date
S

shapper

Hello,

I am creating a User with Linq To SQL.
Each user is associated with a District and to one or more Levels in
Many to Many relationship through UserLevel.

I tried the following:

_user.District = new Entities.District { Id = user.District.Id };

IEnumerable<Entities.UserLevel> levels = user.Levels.Select(l => new
Entities.UserLevel { LevelId = l.Id });
_user.UserLevels.AddRange(levels);

I get the error:
Cannot insert the value NULL into column 'Name', table
'DB.dbo.Districts'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I think I will have the same problem with Levels.

Both District and Level has two properties: Id (PK) and Name.
So basically I think I need to create some kind of Join or get the
District and Levels with the given Id's before I add them to the user.

Anyway, how can I do this?

Thank You,
Miguel
 
shapper said:
Hello,

I am creating a User with Linq To SQL.
Each user is associated with a District and to one or more Levels in
Many to Many relationship through UserLevel.

I tried the following:

_user.District = new Entities.District { Id = user.District.Id };

IEnumerable<Entities.UserLevel> levels = user.Levels.Select(l => new
Entities.UserLevel { LevelId = l.Id });
_user.UserLevels.AddRange(levels);

I get the error:
Cannot insert the value NULL into column 'Name', table
'DB.dbo.Districts'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I think I will have the same problem with Levels.

Both District and Level has two properties: Id (PK) and Name.
So basically I think I need to create some kind of Join or get the
District and Levels with the given Id's before I add them to the user.

Anyway, how can I do this?

Thank You,
Miguel
Click to expand...

It means that the schema for the field on the table is set to Not Allow Null
Values.

That also means that each field in an entity that maps to a table field that
does Not Allow Null Values must have data in the entity field/property,

You must address the fields/properties of the entity to ensure that non null
values are used for fields/properties of the entity that map to database
table fields that do Not Allow Null Values.

Maybe you do it with a New Shape or something. Or you go back to the
database schema for the table and set the fields to accept null values.

It's like this. If you use SQL Server Manager, created a SQL Insert
statement for a 3 field table, the 3rd field of the table had a definition
of Not Allow Null Values, you populate the first two fields of the Insert
statement, never did anything with field 3 it's not even addressed in the
Insert statement, the value for field 3 is going to be Null, and it's going
to blow on the insert.

That's kind of what is happening to you.

The properties of the Entity has null values in it and fields of the SQL
table do not allow null values.


__________ Information from ESET NOD32 Antivirus, version of virus signature database 4465 (20090928) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
 
_user.District = new Entities.District { Id = user.District.Id };

IEnumerable<Entities.UserLevel> levels = user.Levels.Select(l => new
Entities.UserLevel { LevelId = l.Id });
_user.UserLevels.AddRange(levels);
Click to expand...

One other thing to help you see the problem, I suggest that you put a
break-point at _user.UserLevels.AddRange(levels) and use a QuickWatch in
debug mode on 'levels' and look at the objects in 'levels' to see what is
in them at the time you try to do the AddRange().


__________ Information from ESET NOD32 Antivirus, version of virus signature database 4465 (20090928) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
 
One other thing to help you see the problem, I suggest that you put a
break-point at _user.UserLevels.AddRange(levels) and use a QuickWatch in
debug mode on 'levels' and look at the objects  in 'levels' to see whatis
in them at the time you try to do the AddRange().

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4465 (20090928) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
Click to expand...

Hi Arnold,

I think I found a better solution which allows do keep the nulls on
the table and at the same time not adding new queries:

Entities.District district = new Entities.District { Id =
user.District.Id };
_context.Districts.Attach(district);
_user.District = district;

IEnumerable<Entities.UserLevel> levels = user.Levels.Select(l =>
new Entities.UserLevel { LevelId = l.Id });
_context.UserLevels.AttachAll(levels);
_user.UserLevels.AddRange(levels);

I attach the entities before I add them to the user.
On submit changes everything works ... as far as my tests went.

What do you think?

Thanks,
Miguel
 
Back
Top