Clever Geek Handbook

Single Row Table in Entity Framework 6

I couldn't find a solution to this problem, but the title clearly shows what I want.

Is it possible to create a table with one row (I only need to store a boolean in the table)? And how can I customize this constraint using the Fluent API?

+3


source to share


2 answers


you can make one of the columns the primary and also only allow one value. Unfortunately the current version of the api currently supports the default

public class StatusIdentifer
{
  [DefaultValue("1")]
  [Key]
  public int id {get; set}; //set can be removed here?
  public bool status {get:set;} //your boolean value
}

the trick is not to expose any set methods for id.



at the database level, you can still break the paradigm. The answer here talks about how to create a check constraint

public void InitializeDatabase(MyRepository context) {
            if (!context.Database.Exists() || !context.Database.ModelMatchesDatabase()) {
                context.Database.DeleteIfExists();
                context.Database.Create();

                context.ObjectContext.ExecuteStoreCommand("CREATE UNIQUE CONSTRAINT...");
                context.ObjectContext.ExecuteStoreCommand("CREATE INDEX...");
                context.ObjectContext.ExecuteStoreCommand("ETC...");
            }
        }
0


source


With a bit of research, I came up with this solution. I created an initializer for the database (because ExecuteSqlCommand cannot be called in the OnModelCreating method).

class UserDbInitializer : CreateDatabaseIfNotExists<UserDbContext>
{
    protected override void Seed(UserDbContext context)
    {
        context.Database.ExecuteSqlCommand(
            "CREATE TABLE __Lock(" +
            "Id char(1) NOT NULL DEFAULT 'X'," +
            "Lock bit NOT NULL," +
            "CONSTRAINT PK_LOCK PRIMARY KEY (Id)," +
            "CONSTRAINT CK_LOCK_Locked CHECK (Id='X'))"
        );

        context.Database.ExecuteSqlCommand(
            "INSERT INTO __Lock VALUES('X', 0)"
        );

        base.Seed(context);
    }
}

And in UserDbContext the following property:



public bool Locked
{
    get
    {
        return Database.SqlQuery<bool>("SELECT Lock FROM __Lock").SingleAsync().Result;
    }
    set
    {
        if (value)
            Database.ExecuteSqlCommand("UPDATE __Lock SET Lock = 1");
        else
            Database.ExecuteSqlCommand("UPDATE __Lock SET Lock = 0");
    }
}

Hope this helps others :)

0


source






More articles:


All Articles