CODEWITHSUNDEEP
c#.netsql-serverentity-frameworklinq
TGod-Ajayi
TGod-Ajayi

Reputation: 61

TinyInt datatype in SQL Server does not save 0 integer when using LINQ (C#)

I have a simple entity like this:

public partial class TestEntity
{   public int Id { get; set; } // primary key
    public string Relation { get; set; }
    public byte? Age { get; set; }
    public byte Alive { get; set; }
}

When I call DB.add(SampleEntity) add then DB.SaveChanges() what happens is this on the alive property, I pass 0 it changes to 1 when saving now the data type on the DB is tiny int which is supposed to allow 0 - 255 for some reason, it is not accepting 0.

Upvotes: 0

Views: 481

Answers (1)

Harald Coppoolse
Harald Coppoolse

Reputation: 30454

Short answer

You forgot to give TestEntity a primary key.

Explanation

TestEntity doesn't have a primary key. Because of the lack of it, Entity Framework assumes that property Alive is the one that should contain the primary key. When saving it will automatically generate a value for the primary key.

You can verify this, by adding a second TestEntity. What happens with the value of Alive?

When defining identifiers for your entity framework, consider to follow the conventions: use plural nouns for collections, singular nouns for elements from the collection.

So if you have a database with Products, Customers, Orders, etc, you'll have classes Product, Customer, Order, and tables Products, Customers, Orders. Every Customer has zero or more Orders, so a Customer has a property Orders (plural). Every Order is the Order of exactly one Customer, so Order has a property Customer (singular).

By following the conventions, you don't need to tell entity framework a lot. However, if you already have an existing database, and your tables and columns in the tables have different names than the conventions suggest, you need to tell entity framework.

Another advice: your TestEntity represents a row in your table of TestEntities. The non-virtual properties of TestEntity represent the columns of the table. The virtual properties represent the relations between tables (one-to-many, many-to-many, ...)

Therefore make TestEntity a simple straightforward POCO: a class with only get/set. Don't add methods, or properties that do more than just get/set

Back to your question

public class Test
{
    public int Id {get; set;}            // Primary key

    public string Relation { get; set; }
    public byte? Age { get; set; }
    public byte Alive { get; set; }

By the way, are you sure that Alive is not a Boolean? I can imaging that you are Alive, or not Alive, but what does Alive = 4 mean? Consider to use identifiers that properly describe what it means. Future readers will praise you for it.

Virtual properties indicate the relations with other tables. For example, if every Test has zero or more TestResults (one-to-many relation), add the following property:

    public virtual ICollection<TestResult> TestResults {get; set;}

This is an ICollection<TestResult>, not a List<TestResult>. After all, what would TestResults[4] mean? Interface ICollection contains enough properties to Add / Remove / Change the TestResults of this Test.

If this Test is a Test of a Student, then there is a one-to-many relation with Students: every Student has zero or more Tests, every Test is the Test of exactly one Student, namely the Student that the foreign key refers to.

Add the following properties:

public int StudentId {get; set;}      // a real column in the table: non-virtual
public Student Student {get; set;}    // represents a relation: virtual

Of course, in class Student you need to add:

public virtual ICollection<Test> Tests {get; set;}



class MyDbContext : DbContext
{
    public DbSet<Test> Tests {get; set;}
    public DbSet<TestResult> TestResults {get; set;}
    public DbSet<Student> Students {get; set;}
}

Because I followed the conventions, entity framework will be able to detect the tables, the columns in the tables, the functions of the columns (primary / foreign key) and the relations between the tables. You will only need attributes or fluent API if you decide to deviate from the conventions. But that is outside of your question.

Upvotes: 2

Related Questions