Gurux ORM

See Gurux for an overview.
Join the Gurux Community or follow @Gurux for project updates.

Gurux ORM is an open-source Object Relational Mapping (ORM) component for C#, and part of the Gurux Device Framework.
Its purpose is to provide a fast and simple-to-use ORM layer for serializing database data into C# objects — while supporting multiple database engines.

---

✨ Features

• Unified ORM interface for multiple databases
• Automatic table creation and relation handling
• Debug-mode SQL visualization (hover over arguments)
• Lambda expression-based query support
• Built-in attributes for database mapping
• Supports complex relations: 1:1, 1:N, and N:N

---

🗃️ Supported Databases

• MySQL
• MariaDB
• Microsoft SQL Server
• Oracle
• SQLite

---

⚙️ Key Attributes

Attribute	Description
DataMember	Marks a field as stored in the database.
AutoIncrement	Automatically increases on insert; updated back to object.
ForeignKey	Defines foreign key relations.
Relation	Declares table-to-table relationships.
Alias	Specifies custom SQL alias for a field or table.
UseEnumStringValue	Save enums as strings (default: integer).
UseEpochTimeFormat	Store dates as Unix timestamps.
StringLength(int max)	Validates and limits string length in the DB schema.
IsRequired	Field must have a value (NOT NULL).

---

🔗 Relations

Gurux ORM supports:

• 1:1 — One-to-One
• 1:N — One-to-Many
• N:N — Many-to-Many

---

🏗️ Defining C# Classes

All ORM classes must implement IUnique<T>, ensuring a unique ID for each object.
This makes lookups faster and relations easier to manage.

---

Models

Country ↔ Company (1:N)

GXCountry maintains a list of its companies and cascades deletes.

[DataContract]
class GXCountry : IUnique<int>
{
    [DataMember(Name = "ID"), AutoIncrement]
    public int Id { get; set; }

    [DataMember(Name = "CountryName"), IsRequired, StringLength(100)]
    public string Name { get; set; }

    [DataMember]
    [ForeignKey(OnDelete = ForeignKeyDelete.Cascade)]
    public List<GXCompany> Companies { get; set; }
}

[DataContract]
class GXCompany : IUnique<long>
{
    [AutoIncrement, DataMember]
    public long Id { get; set; }

    [DataMember, IsRequired, StringLength(150)]
    public string Name { get; set; }

    // Navigation to Country; FK column is auto-managed (e.g., CountryID)
    [DataMember(Name = "CountryID"), ForeignKey]
    public GXCountry Country { get; set; }
}

Usage Example:

GXCountry finland = new GXCountry { Name = "Finland" };
GXCompany gurux = new GXCompany { Name = "Gurux Ltd", Country = finland };
GXCompany sample = new GXCompany { Name = "Sample Co", Country = finland };
finland.Companies = new List<GXCompany> { gurux, sample };

// Create Country and all related tables in one go (including Company)
Connection.CreateTable<GXCountry>(true, false);

Connection.Insert(GXInsertArgs.Insert(finland));

---

Company ↔ Users (1:N)

GXUser holds a direct reference to GXCompany (no CompanyId).

[DataContract]
class GXCompany : IUnique<long>
{
    [AutoIncrement, DataMember]
    public long Id { get; set; }

    [DataMember, IsRequired, StringLength(150)]
    public string Name { get; set; }

    [DataMember, ForeignKey(OnDelete = ForeignKeyDelete.Cascade)]
    public GXUser[] Users { get; set; }
}

[DataContract]
class GXUser : IUnique<int>
{
    [AutoIncrement, DataMember]
    public int Id { get; set; }

    [DataMember, IsRequired, StringLength(120)]
    public string Name { get; set; }

    [DataMember, ForeignKey(OnDelete = ForeignKeyDelete.Cascade)]
    public GXCompany Company { get; set; }
}

Usage Example:

GXCompany company = new GXCompany { Name = "Gurux Ltd" };
GXUser u1 = new GXUser { Name = "Alice", Company = company };
GXUser u2 = new GXUser { Name = "Bob", Company = company };
company.Users = new[] { u1, u2 };

// Create Company and all related tables (Users) in one go
Connection.CreateTable<GXCompany>(true, false);

Connection.Insert(GXInsertArgs.Insert(company));

---

Users ↔ UserGroups (N:N)

[DataContract]
class GXUser : IUnique<int>
{
    [AutoIncrement, DataMember]
    public int Id { get; set; }

    [DataMember, IsRequired, StringLength(120)]
    public string Name { get; set; }

    [DataMember, ForeignKey(typeof(GXUserGroup), typeof(GXUserToUserGroup))]
    public GXUserGroup[] Groups { get; set; }

    [DataMember, ForeignKey(OnDelete = ForeignKeyDelete.Cascade)]
    public GXCompany Company { get; set; }
}

[DataContract]
class GXUserGroup : IUnique<int>
{
    [AutoIncrement, DataMember]
    public int Id { get; set; }

    [DataMember, IsRequired, StringLength(120)]
    public string Name { get; set; }

    [DataMember, ForeignKey(typeof(GXUser), typeof(GXUserToUserGroup))]
    public GXUser[] Users { get; set; }
}

[DataContract]
class GXUserToUserGroup
{
    [DataMember, ForeignKey(typeof(GXUser), OnDelete = ForeignKeyDelete.Cascade)]
    public int UserId { get; set; }

    [DataMember, ForeignKey(typeof(GXUserGroup), OnDelete = ForeignKeyDelete.Cascade)]
    public int GroupId { get; set; }
}

Usage Example:

GXUserGroup adminGroup = new GXUserGroup { Name = "Admin" };
GXUserGroup devGroup = new GXUserGroup { Name = "Developers" };

GXCompany gurux = new GXCompany { Name = "Gurux Ltd" };
GXUser user = new GXUser
{
    Name = "Charlie",
    Company = gurux,
    Groups = new[] { adminGroup, devGroup }
};

// Create UserGroup and all related tables (bridge + users + company) in one go
Connection.CreateTable<GXUserGroup>(true, false);

Connection.Insert(GXInsertArgs.Insert(user));

---

⚡ Quick Start

A full example using CreateTable<T>(true, false) to generate all related tables with a single call,
and using GXDeleteArgs.Delete(obj) when you have the instance (preferred over DeleteById).

using Gurux.ORM;
using System.Collections.Generic;
using System.Data.SQLite;

class Program
{
    static void Main()
    {
        // 1. Connect to SQLite (you can also use MySQL, SQL Server, etc.)
        var sqlite = new SQLiteConnection("Data Source=:memory:");
        var Connection = new GXDbConnection(sqlite, null);

        // 2. Create all related tables starting from GXCountry
        Connection.CreateTable<GXCountry>(true, false);

        // 3. Insert data
        GXCountry finland = new GXCountry { Name = "Finland" };
        GXCompany gurux = new GXCompany { Name = "Gurux Ltd", Country = finland };
        GXCompany sample = new GXCompany { Name = "Sample Co", Country = finland };
        finland.Companies = new List<GXCompany> { gurux, sample };
        Connection.Insert(GXInsertArgs.Insert(finland));

        GXUser john = new GXUser { Name = "John", Company = gurux };
        GXUser jane = new GXUser { Name = "Jane", Company = gurux };
        Connection.Insert(GXInsertArgs.Insert(john));
        Connection.Insert(GXInsertArgs.Insert(jane));

        GXUserGroup devs = new GXUserGroup { Name = "Developers" };
        john.Groups = new[] { devs };
        Connection.Insert(GXInsertArgs.Insert(john));

        // 4. Query data
        GXSelectArgs selectUsers = GXSelectArgs.SelectAll<GXUser>();
        List<GXUser> users = Connection.Select<GXUser>(selectUsers);

        // 5. Update data
        john.Name = "John Updated";
        Connection.Update(GXUpdateArgs.Update(john, q => q.Name));

        // 6. Delete data (preferred when you have the instance)
        Connection.Delete(GXDeleteArgs.Delete(john));
    }
}

---

🔍 Query Examples

Join query for N:N (Users ↔ Groups)

Fetch users together with their groups using explicit joins over the bridge table:

GXSelectArgs arg = GXSelectArgs.Select<GXUser>(s => "*");
arg.Columns.Add<GXUserGroup>();
arg.Joins.AddInnerJoin<GXUser, GXUserToUserGroup>(j => j.Id, j => j.UserId);
arg.Joins.AddInnerJoin<GXUserToUserGroup, GXUserGroup>(j => j.GroupId, j => j.Id);

var list = Connection.Select<GXUser>(arg);

Filtering & sorting for N:N

Only users in the "Developers" group, ordered by user name:

GXSelectArgs arg = GXSelectArgs.Select<GXUser>(s => "*");
arg.Columns.Add<GXUserGroup>();
arg.Joins.AddInnerJoin<GXUser, GXUserToUserGroup>(j => j.Id, j => j.UserId);
arg.Joins.AddInnerJoin<GXUserToUserGroup, GXUserGroup>(j => j.GroupId, j => j.Id);

// Filter: group name equals "Developers"
arg.Where.And<GXUserGroup>(g => g.Name == "Developers");

// Sort: users by Name (ascending)
arg.OrderBy.Add<GXUser>(u => u.Name, true);

var developers = Connection.Select<GXUser>(arg);

Alternative (portable) approach using a subquery:

// Subquery: find group IDs with name 'Developers'
var groupsSub = GXSelectArgs.Select<GXUserGroup>(g => g.Id, g => g.Name == "Developers");

// Users whose Groups contain one of the subquery results
var usersInDevs = GXSelectArgs.Select<GXUser>(
    s => "*",
    u => GXSql.In(u => u.Groups, groupsSub)
);

// Optional sort
usersInDevs.OrderBy.Add<GXUser>(u => u.Name, true);

var developers2 = Connection.Select<GXUser>(usersInDevs);

---

Separate blocks for filtering and sorting

Filtering only

GXSelectArgs arg = GXSelectArgs.Select<GXUser>(s => "*");
arg.Where.And<GXUser>(c => c.Name.StartsWith("Cha"));

var filtered = Connection.Select<GXUser>(arg);

Sorting only

GXSelectArgs arg = GXSelectArgs.Select<GXUser>(s => "*");
arg.OrderBy.Add<GXUser>(c => c.Name);

var sorted = Connection.Select<GXUser>(arg);

You can also combine:

GXSelectArgs arg = GXSelectArgs.Select<GXUser>(s => "*");
arg.Where.And<GXUser>(c => c.Name.StartsWith("Cha"));
arg.OrderBy.Add<GXUser>(c => c.Name);
var result = Connection.Select<GXUser>(arg);

---

Selecting Related Data (1:N Join Example)

GXSelectArgs arg = GXSelectArgs.SelectAll<GXCountry>();
arg.Columns.Add<GXCompany>();
arg.Joins.AddInnerJoin<GXCountry, GXCompany>(c => c.Id, c => c.Country);
List<GXCountry> countries = Connection.Select<GXCountry>(arg);

---

🔁 Updating Data

user.Name = "UpdatedName";
Connection.Update(GXUpdateArgs.Update(user, q => q.Name));

---

❌ Deleting Data

// Preferred when you have the instance:
Connection.Delete(GXDeleteArgs.Delete(user));

// If only ID is known:
Connection.Delete(GXDeleteArgs.DeleteById<GXUser>(5));

---

🧠 Subqueries

GXSelectArgs sub = GXSelectArgs.Select<GXUser>(q => q.Id, q => q.Id > 100);
GXSelectArgs arg = GXSelectArgs.Select<GXUserGroup>(null, q => q.Id > GXSql.In(q => q.Users, sub));

---

📚 Further Resources

For examples and tests, check:
Gurux.Service_Simple_UnitTests directory

Need help?
Ask your questions on the Gurux Forum.

---