Creating a database with linq2db

Question

I am writing a small program to test out linq2db. What I want to do is create the database if it does not already exist. Most(all?) of the linq2db documentation assumes an existing database. I check if the database exists and if not create an empty database using CreateCommand as below. The Intellisense for this says this method is "For internal use only". Is it OK to use CreateCommand or is there a better way?

namespace SqliteTest
{
    public partial class ImageDatabaseDB : LinqToDB.Data.DataConnection
    {
        public ITable<testTable> testTables { get { return this.GetTable<testTable>(); } }

        public void InitMappingSchema()
        {
        }
        public ImageDatabaseDB()
        {
            InitDataContext();
            InitMappingSchema();
        }
        public ImageDatabaseDB(string configuration) : base(configuration)
        {
            InitDataContext();
            InitMappingSchema();
        } 
        partial void InitDataContext();
    }
    [Table("testTable")]
    public partial class testTable
    {
        [PrimaryKey, Identity] public int id;
        [Column, NotNull] public string Name;
        [Column, Nullable] public string Description;
    }
    public static partial class TableExtensions
    {
        public static testTable Find(this ITable<testTable> table, long id)
        {
            return table.FirstOrDefault(t =>
                t.id == id);
        }
    }
class Program
{
    static void Main(string[] args)
    {
        const string dbLocation = @".\TestDatabase\TestDatabase.sqlite";
        if (!File.Exists(dbLocation))
        {
            using (var db = new TestDatabaseDB())
            {
                db.CreateCommand(); 
                // create tables...
            }
        }

    }

Why are you using `db.CreateCommand();` vs `var command = db.Command;`? https://github.com/linq2db/linq2db/blob/master/Source/LinqToDB/Data/DataConnection.cs/#L1004 — mjwills, Aug 25 '18 at 11:50

score 2 · Answer 1 · answered Aug 25 '18 at 12:23

2

Linq2db offers Database First approach only, creating DB schema from entity mappings (Code First approach) is not supported out-of-the-box.

However, Linq2db is able to generate CREATE TABLE DDL statements from mappings by IDataContext.CreateTable<T>. See also this discussion.

answered Aug 25 '18 at 12:23

Adam Simon

2,762
16
22

OK, I now understand that linq2db only supports DB first so will use an existing database. However, the CreateCommand does actually create an empty SQLite database and allows me to create tables in it. – Pete Freeman Aug 25 '18 at 14:26
It appears that the CreateCommand does not actually create an empty SQLite database since when I remove db.CreateCommand(), the database still gets created along with the tables in it. – Pete Freeman Aug 25 '18 at 15:06
Of course, you have the chance to create your DB from scratch by code executing raw SQL commands (*IDataContext.CreateTable* helps you with this). But you shouldn't mess with *CreateCommand* or other low-level ADO.NET concepts. You're better off using [the API](https://github.com/linq2db/linq2db/blob/v2.2.0/Source/LinqToDB/Data/DataConnectionExtensions.cs#L902) provided by Linq2db to execute raw SQL. – Adam Simon Aug 25 '18 at 15:09

score 2 · Answer 2 · answered Nov 14 '18 at 12:03

2

To create sqlite database you should use LinqToDB.DataProvider.SQLite.SQLiteTools.CreateDatabase(...) method

answered Nov 14 '18 at 12:03

Creating a database with linq2db

2 Answers2