Creating new entities

An entity or a table or a collection is defined by a class. The class must have int Id property which if 0 indicates new entity and if not - the entity to be updated. Supported data types are: int, double, DateTime (and their nullables), byte[] and string. (Note bool is not supported, use int instead). For example, we’ll use:

public class SomeData
{
    public int Id { get; set; }
    public int PeriodId { get; set; }
    public int? ObjectId { get; set; }
    public double Value { get; set; }
    public double? Normalized { get; set; }
    public int? PersonId { get; set; }
    public DateTime? Date { get; set; }
    public string Name { get; set; }
}

Then to save new record we would (table will be created automatically if not there based on type's name):

using LinqDb;
...
var db = new Db("DATA");

var d = new SomeData()
{
    Id = 0,
    Normalized = 1.2,
    PeriodId = 5
};
db.Table<SomeData>().Save(d);
db.Dispose();

To save bulk of data more efficiently:

var list = new List<SomeData>();
...
db.Table<SomeData>().SaveBatch(list);

Querying

Select

Select everything from table:

List<SomeData> res = db.Table<SomeData>()
                       .SelectEntity();

To select only some columns:

var res = db.Table<SomeData>()
            .Select(f => new 
            { 
                Id = f.Id,
                Normalized = f.Normalized
            });

Note that only anonymous types are supported in this case. res is List<AnonymousType>

Where

Linqdb supports Where clause:

var res = db.Table<SomeData>()
            .Where(f => f.Normalized == 2.3 || f.Date > DateTime.Now && f.PersonId == 5)
            .Select(f => new 
            { 
                Id = f.Id,
                Normalized = f.Normalized
            });

Where supports these operators: && || == >= > < <= != . Also on the left hand side of operator there must be some property without any expression (i.e. f.PersonId % 2 == 0 won‘t work), on the right hand side - constant/variable/expression. Types on both sides must match exactly (i.e. int != int?) and casting is only possible on the right side.
Empty string and empty array is treated as null.

Between

To evaluate something like .Where(f => f.Normalized > 3 && f.Normalized < 10) Linqdb will iterate data twice: first it will find all values > 3, then all values < 10 and finally return it’s intersection. So in this case it’s faster to use .Between(f => f.Normalized, 3, 10, BetweenBoundaries.BothExclusive) which will only scan data once: from value 3 to 10.

var res = db.Table<SomeData>()
            .Between(f => f.Normalized, 3, 10, BetweenBoundaries.BothExclusive)
            .Select(f => new 
            { 
                Id = f.Id,
                Normalized = f.Normalized
            });

Intersect

Finds intersection of values in table with given set:

var res = db.Table<SomeData>()
            .Intersect(f => f.Normalized, new HashSet<double>() { 10, 20, 30 })
            .Select(f => new
            {
                Id = f.Id,
                Normalized = f.Normalized
            });

Order, skip, take

Ordering (by one column) is supported:

var res = db.Table<SomeData>()
            .OrderBy(f => f.Normalized)
            .Skip(10)
            .Take(10)
            .Select(f => new
            {
                Id = f.Id,
                Normalized = f.Normalized
            });

There are also overloads of Select and SelectEntity that take out argument total. This argument will be assigned the number of records that satisfied condition. (Handy with .Skip and .Take when the total number is also needed). Ordering by Id is the fastest.

Search

Linqdb supports simple full text search on string columns. For example,

var res = db.Table<SomeData>()
            .Search(f => f.Name, "some text")
            .Select(f => new
            {
                Id = f.Id,
                Name = f.Name
            });

will find rows where column Name contains both "some" and "text". If you don't need search functionality on string you can store them more efficiently as byte[] (no need to maintain search index).
.Search takes optional prameters start_step and steps which enable partial search. This is handy when you have lots of data and want to return some results as fast as possible. The search will only happen on slice of data, for example, start_step = 0, steps = 1 means only first 1000 documents will be searched, start_step = 10, steps = 5 - 5000 documents will be searched starting from document 10000. You can get total number of steps using LastStep function.

Or

By default when statements like .Where or .Search go together they imply logical "and" to the results. It is possible to have "or" on neighbouring statements like so:

var res = db.Table<SomeData>()
            .Search(f => f.Name, "some text").Or().Search(f => f.Name, "something else")
            .Where(f => f.Id > 100)
            .Select(f => new
            {
                Id = f.Id,
                Name = f.Name
            });

In this case only one of search need to satisfy to return the result. More than one .Or could be used.

Intermediate results

All the work happens in .Select or .SelectEntity statement (generally - in the last statement), so things like these are possible:

var tmp = db.Table<SomeData>()
             .Where(f => f.Normalized == 5);
if (person_id != null)
{
    tmp.Where(f => f.PersonId == person_id); //no need to assign to tmp
}
var res = tmp.SelectEntity();

Count

To obtain table count:

db.Table<SomeData>().Count();

Also works when conditions applied:

db.Table<SomeData>().Where(f => f.Id < 50).Count();

GetIds

GetIds() method allows to get list of id's satisfying conditions without having to select them.

Updating

As mentioned above Save(item); will update row with item’s Id using item’s properties, given that Id is not 0. If it is 0, new item will be created and new Id will be assigned to the object’s Id property.

To update column of multiple rows you would need to construct Dictionary<int, T> where T is column’s type and:

var dic = new Dictionary<int, int?>();
dic[2] = 8;
dic[3] = 11;
db.Table<SomeData>().Update(f => f.PeriodId, dic);

This will update column PeriodId of rows with Ids 2 and 3 with respective values 8 and 11.

Deleting

To delete row(s) you would need to construct HashSet<int> of ids to be deleted and:

db.Table<SomeData>().Delete(new HashSet<int>() { 2, 3 });

Atomic increment

To increment values atomically. i.e. thread-safe, use .AtomicIncrement:

var new_item = new Counter() { Name = "unique_name", Value = 1 };
db.Table<Counter>().Where(f => f.Name == "unique_name").AtomicIncrement(f => f.Value, 1, new_item, null);

Transactions

Linqdb supports ACID transactions:

using (var transaction = new LinqdbTransaction())
{
    var d = new SomeData()
    {
        Id = 1,
        Normalized = 1.2,
        PeriodId = 5
    };
    db.Table<SomeData>(transaction).Save(d); //note that .Table takes transaction as a parameter
    var d2 = new BinaryData()
    {
        Id = 1,
        Data = new List<byte>() { 1, 2, 3 }.ToArray()
    };
    db.Table<BinaryData>(transaction).Save(d2);
    transaction.Commit(); //all writes happen here, if it fails - nothing gets modified (all or nothing)
}

if .Commit is not called, nothing is modified. Transaction must be created used and destroyed in the same thread. After .Save (or .SaveBatch) inside a transaction new items (with Id == 0) are assigned new ids, so that they can be used before doing commit. Transaction is supported on these data-modifying commands: .Save, .SaveBatch, .Update, .Delete.

Indexes

Linqdb supports in-memory indexes on columns of type int, DateTime and double. In-memory indexes speed up reading operations on such columns. Also indexes are required for .GroupBy statement (see below). To build an index on property use .CreatePropertyMemoryIndex, to remove - .RemovePropertyMemoryIndex Indexes slow down data-modifying operations, so use them only when you have to. If indexes (or string properties) are present it is especially efficient to use batch operations, i.e. .SaveBatch and to make batches larger.

GroupBy

Suppose you want to group by column PeriodId and aggregate column Value. For that in-memory index must be created:

db.Table<SomeData>().CreateGroupByMemoryIndex(f => f.PeriodId, f => f.Value);

Then you could do something like:

var res = db.Table<SomeData>()
            .GroupBy(f => f.PeriodId)
            .Select(f => new { f.Key, Sum = f.Sum(z => z.Value), Avg = f.Average(z => z.Value) });

These aggregation functions are supported: Count, CountDistinct, Sum, Max, Min, Average. Key selects group-by property's value.


Init \ dispose

Before using Linqdb one would need to create it:

var db = new Db("DATA"); //embedded version
var db = new Db("host_ip:port"); //server's client version 

The argument is path to the database or ip to the server.
The db needs to be created once at the application startup.
Before exiting application it is a good practice to dispose the db:

db.Dispose();

Replication

To make a copy of a database programatically:

db.Replicate("PATH_TO_COPY");

If directory exists - it will be removed before copying. Database can still be read/written while replication is in progress.

Limitations \ caveats

Server installation

Server folder contains Server.exe which starts listening on port specified in config.txt
Server has configuration file config.txt which is self-explanatory.