EF core can't create Table, error in sql syntax

Question

im new to Entity framework and i dont understand why it is giving me an error in sql syntax , i thought the whole purpose of using EFCore is to handle the sql syntax and abstract all the sql queries away

here is my model :

  class Block 
    {
        public BlockHeader Header {get ;}
        public List<Transaction> Transactions {get;}

        public Block(List<Transaction> transactions, BlockHeader header)
        {
            Transactions= transactions;
            Header=header;           
        }    
        public Block(){
            
        }
    }
    public class BlockHeader
    {
        public byte[] Hash {get ;} 
        public byte[]  PreviousHash { get; }
        public DateTime timestamp { get; }
        public int version{ get; } 
        public byte[] MerkleRoot{ get; }
        public int SequenceNumber {get ; private set;}

        public BlockHeader (byte[] previoushash,int sequencenumber,List<Transaction> transactions)
        {
            timestamp = DateTime.Now;
            PreviousHash =  previoushash;
            version = 1;
            SequenceNumber = sequencenumber;
            MerkleRoot = ComputeMerkleRoot(transactions);
            Hash = ComputeBlockHash();

        }

and here is my database Context class

        class BlockchainDB : DbContext
        {
            public BlockchainDB()
            {
                
            }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseMySQL("server=localhost;database = Blockchain ;user = root ; password = password");
            }
            protected override void OnModelCreating(ModelBuilder builder){
                builder.Entity<Block>(e => e.HasNoKey());
            }   
        }

when i add a migration it adds it successfully but when i update the database using this command

dotnet ef database update

it outputs this error :

Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `Block` (

);
MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3

@Bizhan EntityFramework does this , not me , i cant even find where the error is located — ezio
– ezio, Commented Jul 24, 2020 at 10:45
It's trying to create a table without any properties, this seems very incorrect. you don't have setters for your navigation properties. if you try to encapsulate it, use a protected empty constructor, and private setters. I'm pretty sure the error can be found inside some of these pointers. The erros is located in the generated SQL in CREATE TABLE Block` ( -- this method won't work if empty. );` — Bjarke Handsdal
– Bjarke Handsdal, Commented Jul 24, 2020 at 10:49

Bjarke Handsdal · Accepted Answer · 2020-07-24 11:00:45Z

1

EFCore entities needs setters, otherwise the reference will not be set.

I'd suggest you would write it something like this to ensure encapsulation

public class Block 
{
  protected Block()
  {
    // empty constructor needed for EF Core
  }

  // Navigation properties 
  public virtual BlockHeader BlockHeader {get; private set;}

  // For collections 
  private readonly List<ChildEntity> _children = new List<ChildEntity>();
  public virtual IReadOnlyList<ChildEntity> Children => _children.ToList();

  // Encapsulated Business logic constructor 
  public Block(BlockHeader header, List<ChildEntity> children)
  {
    _children = children;
    BlockHeader = header;
  }
}

Alternatively you can use the fluent api modelbuilder configuration through entity core framework and explicitly map the relations.

answered Jul 24, 2020 at 11:00

Bjarke Handsdal

2381 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ezio Over a year ago

this solved it, the error disappeared , thank you sir ,now im trying to configure the relationship of blockheader in my Context class , i have new errors but i think i can find out whats the problem, thank you sir

Bjarke Handsdal Over a year ago

Glad I could help. :) best of luck with the relation, if it's 1-1 relation between block and blockheader I would suggest using the fluent api, otherwise you'd have to annotate the dependant child class with the [ForeignKey] attribute.

ezio Over a year ago

im doing it with fluent api , thank you soo much sir

Collectives™ on Stack Overflow

EF core can't create Table, error in sql syntax

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related